SQL Server中触发器的简单使用示例

1、定义触发器

CREATE  TRIGGER  <触发器名>

ON  <表名>  FOR|AFTER|INSTEAD OF  <触发事件>

AS

<触发动作体>

注：

1. AFTER表示只有执行了指定的操作(DELETE,UPDATE,INSERT)之后，触发器才被激活，执行触发器中的sql语句；
2. FOR表示为AFTER触发器，并且该触发器只能在表上创建；
3. INSTEAD OF指定触发器类型为INSTEAD OF，通常用于使得不能直接更新的视图进行更新。

2、关于触发器

各种类型用到的测试表包括DELETED表 及INSERTED表。当执行DELETE触发器时会将触发器对应的表中的记录移到DELETED表，可以从DELETED表得到删除的记录；当执行INSERT触发器会将触发器对应的表中的增加新行，同时也会向INSERTED表中增加相同的行；如果执行UPDATED触发器会将“旧”行复制到DELETED表中，然后将新行同时插入到触发器对应 表及INSERTED表中。

 

3、INSERT触发器

【例一】

create trigger insert_trigger

on student for insert

as

begin

update teachers set total = total + 1

end

说明：向表student插入记录时候便会使得表teachers表中的total字段增一.

 

4、DELETE触发器

【例1】

as

begin

declare @rowCount int

select @rowCount=@@ROWCOUNT /*@@ROWCOUNT为全局变量，记录受影响的行数*/

if @rowCount>1

begin

rollback transaction

raiserror('当前删除的记录数为%d,一次仅允许删除一行数据',16,1,@rowCount)

end

end

 

delete from student where name like '李%'

错误信息为：

5、UPDATE触发器

创建一个表记录更新操作的日志信息，

create table logInfo(

oper_time datetime,

old_value int,

new_value int

);

 

create trigger S_update

on Student for update

as

begin

if(COLUMNS_UPDATED()&3)>0   /*第1列或第2列*/

begin

rollback transaction

raiserror('姓名及学号不允许修改',16,1)

return

end

if(COLUMNS_UPDATED() &4=4)

begin

print '修改了性别'

return

end

 

declare @old_value int,@new_value int

select @old_value=age from DELETED

select @new_value=age from INSERTED

insert into logInfo values(GETDATE(),@old_value,@new_value)

end

 

 

 

执行语句update student set age=29 where name='张琴'

可以看到logInfo表中多了一行记录，

注：COLUMNS_UPDATED()方法获得更新的列，例如如果返回3标识更新了第1列和第2列，将更新的列按照二进制形式标识，更新标记为1。符号“&”为按位与运算，COLUMNS_UPDATED() & 3>0表示更新了第1列或者第2列(3的二进制形式最低二位全为1，其它位为0)，COLUMNS_UPDATED() & 5=5表示更新了第1列及第3列。

 

6、INSTEAD OF触发器

INSTEAD OF触发器使得不可更新的视图变得可以进行更新操作。例如有三张表S1，S2，S3，结构都相同，如下：

现在建立一个视图，查询这三张表中的所有数据。表S1存储张三的记录，表S2存储李四的记录，表S3存储王武的记录。视图建立如下，

create view S_all

as

(

select * from S1

union all

select * from S2

union all

select * from S3

)

 

创建触发器

create trigger trigger_s

on S_all instead of insert

as

begin

declare @name nvarchar(10)

select @name=用户 from INSERTED

if @name='张三'

insert into S1 select * from INSERTED

else if @name='李四'

insert into S2 select * from INSERTED

else if @name='王武'

insert into S3 select * from INSERTED

else

print '输入姓名错误'

end

 

向视图S_all插入记录

insert into S_all values('张三','笔记本','是')

可以看到该记录插入到了表S1中。