触发器对表进行插入、更新、删除的时候会自动执行的特殊存储过程。触发器一般用在check约束更加复杂的约束上面。触发器和普通的存储过程的区别是:触发器是当对某一个表进行操作。诸如:update、insert、delete这些操作的时候,系统会自动调用执行该表上对应的触发器。SQL Server 2005中触发器可以分为两类:DML触发器和DDL触发器,其中DDL触发器它们会影响多种数据定义语言语句而激发,这些语句有create、alter、drop语句。
DML触发器分为:
1、 after触发器(之后触发)
a、 insert触发器
b、 update触发器
c、 delete触发器
2、 instead of 触发器 (之前触发)
其中after触发器要求只有执行某一操作insert、update、delete之后触发器才被触发,且只能定义在表上。而instead of触发器表示并不执行其定义的操作(insert、update、delete)而仅是执行触发器本身。既可以在表上定义instead of触发器,也可以在视图上定义。
触发器有两个特殊的表:插入表(instered表)和删除表(deleted表)。这两张是逻辑表也是虚表。有系统在内存中创建者两张表,不会存储在数据库中。而且两张表的都是只读的,只能读取数据而不能修改数据。这两张表的结果总是与被改触发器应用的表的结构相同。当触发器完成工作后,这两张表就会被删除。Inserted表的数据是插入或是修改后的数据,而deleted表的数据是更新前的或是删除的数据。
对表的操作 Inserted逻辑表 Deleted逻辑表
增加记录(insert) 存放增加的记录 无
删除记录(delete) 无 存放被删除的记录
修改记录(update) 存放更新后的记录 存放更新前的记录
Update数据的时候就是先删除表记录,然后增加一条记录。这样在inserted和deleted表就都有update后的数据记录了。注意的是:触发器本身就是一个事务,所以在触发器里面可以对修改数据进行一些特殊的检查。如果不满足可以利用事务回滚,撤销操作。
-----简单的
create trigger change_shiw on [user]
after insert
as
select * from [user]
--判断数据库对象是否存在
if (exists (select * from sys.objects where name = 'stopwangwu'))
print '存在';
go
--禁止王五发帖 更新的触发器
alter trigger stopwangwu on [bbs]
for insert
as
begin
if(select b.bbsower from bbs b,inserted i where b.bbsid=i.bbsid)=3
select * from inserted
print '王五禁ID了'
rollback
end
---test
go
select b.bbsower from bbs b,[user] i where b.bbsower=i.userid and b.bbsower=3
insert into bbs values(8,'ssss',3,'sssssss')
-----T-SQL
declare @name varchar(20),@result varchar(100);
set @name='张飒';
set @result='hello'+@name;
select @result;
--查询列子
declare @name varchar(20);
set @name='李四';
select * from [user] where username=@name;
----
declare @name varchar(20),@uid int;
set @uid=3;
select @name=username from [user] where userid=@uid;
select @name
---全局变量
全局变量
select @@identity;--最后一次自增的值
select identity(int, 1, 1) as id into tab from [user];--将user表的属性,以/1自增形式创建一个tab
select * from tab;
select @@rowcount;--影响行数
select @@cursor_rows;--返回连接上打开的游标的当前限定行的数目
select @@error;--T-SQL的错误号
select @@procid;
--配置函数
set datefirst 7;--设置每周的第一天,表示周日
select @@datefirst as '星期的第一天', datepart(dw, getDate()) AS '今天是星期';
select @@dbts;--返回当前数据库唯一时间戳
set language 'Italian';
select @@langId as 'Language ID';--返回语言id
select @@language as 'Language Name';--返回当前语言名称
select @@lock_timeout;--返回当前会话的当前锁定超时设置(毫秒)
select @@max_connections;--返回SQL Server 实例允许同时进行的最大用户连接数
select @@MAX_PRECISION AS 'Max Precision';--返回decimal 和numeric 数据类型所用的精度级别
select @@SERVERNAME;--SQL Server 的本地服务器的名称
select @@SERVICENAME;--服务名
select @@SPID;--当前会话进程id
select @@textSize;
select @@version;--当前数据库版本信息
--系统统计函数
select @@CONNECTIONS;--连接数
select @@PACK_RECEIVED;
select @@CPU_BUSY;
select @@PACK_SENT;
select @@TIMETICKS;
select @@IDLE;
select @@TOTAL_ERRORS;
select @@IO_BUSY;
select @@TOTAL_READ;--读取磁盘次数
select @@PACKET_ERRORS;--发生的网络数据包错误数
select @@TOTAL_WRITE;--sqlserver执行的磁盘写入次数
----输出函数 print在输出值不少字符串的情况下,
--需要用convert转换成字符串才能正常输出,而且字符串的长度在超过8000的字符以后,
--后面的将不会显示
select 1+1;
print 1+1;
print @@language;
--循环
declare @i int
set @i=1;
while (@i<=100)
begin
print @i;
set @i=@i+1;
end
declare @i int,@sum int;
set @i=1;
set @sum=0;
while (@i<=100)
begin
set @sum=@sum+@i;
set @i=@i+1;
if(@i>50)
--break; --1275
continue;--5050
end
print @sum;
--case
select * ,
case userid
when 1 then '可乐'
when 2 then '糖浆'
else 'Sb'
end as '外号'
from [user]
select * ,
'大赢家'=
case
when username like '%张%' then '中奖了'
else 'other'
end
from [user]
--定时
waitfor delay '00:00:03';--定时三秒后执行
print '定时三秒后执行';
--update 触发 修改一条数据 就复制老数据
if (exists (select * from sys.objects where name = 'tri_inseuser'))
print '存在,重新创建';
drop trigger tri_inseuser;
go
create trigger tri_inseuser on [user] for update
as
declare @uid int,@uname varchar(100);
select @uid=i.userid,@uname=i.username from [user] u,deleted i where u.userid=i.userid;--老数据
insert into [user](username) values (@uname);
--test
update [user] set username='卡k卡' where userid=6;
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
--delete删除类型触发器
if (object_id('tgr_user_delete', 'TR') is not null)
print '存在,将重新创建';
drop trigger tgr_classes_delete
go
create trigger tgr_classes_delete
on [user]
for delete --删除触发
as
print '备份数据中……';
if (object_id('classesBackup', 'U') is not null)
--存在classesBackup,直接插入数据
insert into classesBackup select userid, username from deleted;
else
--不存在classesBackup创建再插入
select * into classesBackup from deleted;
print '备份数据成功!';
go
--
--不显示影响行数
--set nocount on;
delete [user] where userid = 5;
--查询数据
select * from [user];
select * from classesBackup;
---
if(OBJECT_ID('tri_delbbsid','tr') is not null)
drop trigger tri_delbbsid;
go
---raiserror
create trigger tri_delbbsid
on bbs with encryption --加密触发器
for update
as
if update(bbsid)
begin
raisError('系统提示:ID不能修改!', 16, 11);
rollback tran;
end
--test
update bbs set bbsid=19 where bbsid=5;
--instead 触发器 先删除帖子 再删除 用户 备份用户信息 不备份帖子信息
if(OBJECT_ID('tri_userinstead','tr') is not null)
drop trigger tri_userinstead;
go
create trigger tri_userinstead
on [user]
instead of delete
as
declare @userid int, @username varchar(50),@bbsid int,@bbscount int,@index int;
select @userid=u.userid,@username=u.username from [user] u,deleted d where u.userid=d.userid;
set @bbscount =( select count(*) from bbs where bbsower = @userid);
set @index = 0;
print '找到'+@username+'的帖子'+convert(varchar,@bbscount)+'条';
while(@index<@bbscount)
begin
select @bbsid=bbsid from bbs where @userid=bbsower;
print '正在删除'+@username+'的帖子:'+convert(varchar,@bbsid);
waitfor delay '00:00:01' ---进度条效果
set @index=@index+1;
end
----test
delete [user] where userid=1;
select * from [user];
--- 启用、禁用触发器
--禁用触发器
disable trigger tri_userinstead on [user];
--启用触发器
enable trigger tri_userinstead on [user];
---# 查询创建的触发器信息
--查询已存在的触发器
select * from sys.triggers;
select * from sys.objects where type = 'TR';
--查看触发器触发事件
select te.* from sys.trigger_events te join sys.triggers t
on t.object_id = te.object_id
where t.parent_class = 0 and t.name = 'tgr_valid_data';
--查看创建触发器语句
exec sp_helptext 'tgr_message';
---test 记录日志
if (object_id('log4user', 'U') is not null)
drop table log4user
go
create table log4user (
logStream int identity(1,1) primary key,
action varchar(20),
createDate datetime default getDate() ---默认 就是当前的时间
)
if(OBJECT_ID('tri_log4user','tr') is not null)
drop trigger tri_log4user;
go
create trigger tri_log4user
on [user]
for insert,update,delete
as
if ((exists (select 1 from inserted)) and (exists (select 1 from deleted)))
begin
insert into log4user(action) values('updated');
end
else if (exists (select 1 from inserted) and not exists (select 1 from deleted))
begin
insert into log4user(action) values('insert');
end
else
begin
insert into log4user(action) values('delete');
end
select * from log4user;
----test
insert into [user] values('麦克斯韦');
update [user] set username = '李凯' where username='麦克斯韦';
---分页SQl
---① 利用top 和not in
select top 2 * from [user] where userid not in (select top(1*2) userid from [user])
----②利用MAX
select top 2 * from [user] where userid >(select max(t.userid) from (select top(1*2) userid from [user] order by userid)t);
---③利用ROWNUMBER 从0-4条记录
SELECT *
FROM
(
SELECT *,ROW_NUMBER() OVER (ORDER BY userid) AS RowNo
FROM [user]
) AS A
WHERE RowNo >= 0 and RowNo < = 4;
----存储过程
create table bankMoney (
id int identity(1,1) primary key,
userid varchar(20),
sex varchar(20),
Money int
)
insert into bankMoney values('lisi','男',30);
----test
if(OBJECT_ID('sp_query_bankMoney','P') is not null)
drop procedure sp_query_bankMoney;
create procedure sp_query_bankMoney
as
select * from bankMoney
exec sp_query_bankMoney
drop procedure insert_bank;
Create procedure insert_bank @param2 varchar(20),@param3 varchar(20),@param4 int,@param5 int output
with encryption ---------加密
as
insert into bankMoney (userID,sex,Money)
Values(@param2,@param3, @param4)
select @param5=sum(Money) from bankMoney where userID='Zhangsan'
declare @total_price int
exec insert_bank 'Zhangsan','男',100,@total_price output
print '总余额为'+convert(varchar,@total_price)
----游标
go
declare @userid int ,@username varchar(20);
declare c_showuser cursor for select * from [user]
open c_showuser
fetch next from c_showuser into @userid,@username;
while(@@FETCH_STATUS=0)
begin
print convert(varchar,@userid)
print @username;
fetch next from c_showuser into @userid,@username;
end
close c_showuser
deallocate c_showuser
----存储过程 显示全部数据
if(OBJECT_ID('pro_showuser','P') is not null)
drop procedure pro_showuser;
go
create procedure pro_showuser
as
declare @userid int ,@username varchar(20);
declare c_showuser cursor for select * from [user]
open c_showuser
fetch next from c_showuser into @userid,@username;
while(@@FETCH_STATUS=0)
begin
print convert(varchar,@userid)
print @username;
fetch next from c_showuser into @userid,@username;
end
close c_showuser
deallocate c_showuser
exec pro_showuser
---带传入参数的存储 过程
WITH RECOMPILE ---不缓存
create procedure pro_inp (@startid int,@endid int)
as
select * from [user] where userid between @startid and @endid;
go
exec pro_inp 1,3
--带输出参数的
if (object_id('proc_putu', 'P') is not null)
drop proc proc_putu
go
create proc proc_putu(
@id int, --默认输入参数
@bbstopic varchar(20) out, --输出参数
@bbscontent varchar(20) output--输入输出参数
)
as
select @bbstopic = bbstopic, @bbscontent = bbsccontent from bbs where bbsid = @id and bbsccontent = @bbscontent;
go
--
declare @id int,
@name varchar(20),
@temp varchar(20);
set @id = 1;
set @temp = 'lallalalal';
exec proc_putu @id, @name out, @temp output;
select @name, @temp;
print @name + '#' + @temp;
if (object_id('pro_out4user', 'P') is not null)
drop proc pro_out4user
go
create proc pro_out4user(@userid int ,@username varchar(20) output)
as
select @userid=userid,@username=username from [user] where userid=@userid and username=@username;
select userid,username from [user] where userid=@userid and username=@username;
go
declare @id int,@name varchar(20);
set @id=11;
set @name='麦克斯韦'
exec pro_out4user @id, @name output;
分享到:
相关推荐
1.1. 简介 SQLServer2008与Oracle11g 同时为现代企业两个重量级的数据库,如何实现他们之 间的互连互通 1.2. 环境信息 SQLServer :2008R2 Oracle:11gR2 ...《OracleDB11g初探》《SQLServer2008初探》
SQL Server 2005 Service Broker 初探
SQL SERVER 2000初探.pdf
初探ADO.NET对SQL Server数据库的数据访问技术
MS SQL SERVER安全之初探.pdf
SQL Server数据移动方法初探.pdf
Sybase SQL Server性能优化技术初探.pdf
结合VB进行SQL SERVER教学初探.pdf
SQL SERVER课程案例驱动教学初探.pdf
SQL Server 2005 Mobile同步技术初探.pdf
SQL Server数据库崩溃时恢复策略初探.pdf
高职《SQL Server数据库》课程教学模式初探.pdf
ASP SQL Server环境下 小学网络课件开发初探.pdf
SQL SERVER扩展存储过程实现机制及应用方法初探.pdf
SQL Server 2000认证与权限机制及其安全策略初探.pdf
《SQL Server数据库》“实例教学”与“任务驱动”相结合教学模式初探.pdf
网上的SQL优化的文章实在是很多,说实在的,我也曾经到处找这样的文章,什么不要使用IN了,什么OR了,什么AND了,很多很多,还有很多人拿出仅几S甚至几MS的时间差的例子来证明着什么(有点可笑),让许多人不知道其是...
1. 简介 1.1. 简介 1.2. 环境信息 1.3. 相关文档 2. 软件准备
利用PHP和mysql搭建了一个简易网站,包括网页登录、注册和主页,目的为练习SQL漏洞注入,故网页设计较为简单,仅实现基本前端表单和PHP数据交互功能。
SQL事件探查器初探SQL事件探查器初探