初探sqlserver触发器 博客分类: T-SQL编程 sqlserver触发器新人
程序员文章站
2024-03-11 15:13:07
...
触发器对表进行插入、更新、删除的时候会自动执行的特殊存储过程。触发器一般用在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后的数据记录了。注意的是:触发器本身就是一个事务,所以在触发器里面可以对修改数据进行一些特殊的检查。如果不满足可以利用事务回滚,撤销操作。
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;