1、定义触发器
CREATE TRIGGER <触发器名>
ON <表名> FOR|AFTER|INSTEAD OF <触发事件>
AS
<触发动作体>
注:
- AFTER表示只有执行了指定的操作(DELETE,UPDATE,INSERT)之后,触发器才被激活,执行触发器中的sql语句;
- FOR表示为AFTER触发器,并且该触发器只能在表上创建;
- 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中。