SqlServer：触发器
触发器
触发器（Trigger)用户对某一个表进行 INSERT、UPDATE 和 DELECT 操作时，被用户的行为触发执行的一段程序。触发器可用来检查用户对表的操作是否合乎整个应用系统的需求，维持表内数据的完整性和正确性。触发器是表的对象，是由系统自动触发执行的，不要也不能运用命令来执行，和存储过程一样是由一组 SQL 语句写成的程序。
触发器的功能和表内所设置的约束有些重叠，如果列约束的功能能够达到应用程序的要求，则无须设计触发器，但是，作为约束的补充或者在维护表时，使用触发器会更加方便。使用触发器具有以下三个作用：
- 在增加、删除、修改记录前检查数据的完整性；
- 当触发器检查通不通过时，回滚以确保数制的合法性。
- 对表进行维护操作时利用触发器增加其功能。
触发器的分类
根据触发器中的代码还是 INSERT、UPDATE 和 DELECT 操作哪个先执行，可以将触发器分为 AFTER 和 INSTEAD OF 触发器。
- AFTER 触发器：先执行表的 INSERT、UPDATE 和 DELECT 操作，然后才执行触发器的语句。AFTER 触发器只有执行 INSERT、UPDATE 和 DELETE 中某一操作之后才被触发且只能针对表进行定义，可以针对一个表的同一操作定义多个触发器。
- INSTEAD OF 触发器先执行触发器，没有问题后才对表记录进行 INSERT、UPDATE 和 DELECT 操作。对表进行 INSERT、UPDATE 或 DELETE 操作时，系统不是直接对表执行这些操作，而是把操作内容交给触发器。触发器检查进行的操作是否正确，若正确才进行相应的操作。
两种触发器的不同点在于，AFTER 触发器只能在修改语句执行之后才能执行。如果触发器出现异常或更新操作不满足触发器中定义的规则，要进行回滚，同一个操作可以定义多个AFTER触发器。INSTEAD OF 触发器可以对表或视图生成，每个操作只能定义一个 INSTEAD OF 触发器，操作出错无须撤销事务。
与触发器相关的临时表
触发器除了与数据表相关外，系统维护两个临时表辅助触发器的工作。只有数据表定义了触发器并执行维护操作时，这两个表才能起作用。用户不可以修改这些临时表的内容，但可以查询显示，当数据表的维护操作完成后这两个表消失。
| 临时表 | 功能 |
|---|---|
| INSERTED 表 | 存放由 INSERT、UPDATE 语句的执行而要加到该触发器作用的表中去的所有新记录 |
| DELETED 表 | 存放由 DELETE、UPDATE 语句的执行而要从被该触发器作用的表中删除的记录 |
执行 INSERT 命令插入记录时只用到 INSERTED 表，执行 DELETE 命令删除记录时只用到 DELETED 表，当执行 UPDATE 操作时 2 张表都要用到。由于上述 3 个操作都只能影响一条记录，所以这 2 张临时表中只会存在 1 条记录。
SQL 语句
建立触发器要确定触发器对应的表，触发 INSERT、UPDATE 或 DELETE 中哪个操作，采用 AFTER(FOR) 触发器还是 INSTEAD OF 触发器。建立触发器的命令格式是:
CREATE TRIGGER 触发器名
ON 表名
[WITH ENCRYPTLON]
FOR | AFTER | INSTEAD OF
[INSERT][,][UPDATE][,][DELETE]
AS
[IF 触发条件]
BEGIN
SQL语句
END
创建样例
假设 Student 表中有如下一些数据：
样例一
在 Student 表上创建触发器 t1，在用户插入、修改和删除记录时，都会自动显示表中的内容。
CREATE TRIGGER t1
ON Student
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
SELECT * FROM Student
END
样例二
建立一个触发器 t2，当向 Student 表中插入数据时，如果姓名不重复则插入。如果出现姓名重复的情况，则提示错误（raiserror('姓名重复，不能插入',16,1)）并回滚该事务。
CREATE TRIGGER t2
ON Student
INSTEAD OF INSERT
AS
BEGIN
DECLARE @name char(10)
SELECT @name = inserted.Sname FROM inserted
IF exists(SELECT Sname FROM Student WHERE Sname = @name)
BEGIN
raiserror('姓名重复，不能插入',16,1)
rollback
END
ELSE
BEGIN
INSERT INTO Student SELECT inserted.* FROM inserted
END
END
此时若插入重复的名字，会报错。
INSERT INTO Student(Sno, Sname, Ssex) VALUES(001, '李君帅', '男')
样例三
创建修改触发器 t3，该触发器防止用户修改表 Student 的学号。
CREATE TRIGGER t3
ON Student
AFTER UPDATE
AS
IF UPDATE(Sno)
BEGIN
raiserror('不能修改学号', 16, 2)
rollback
End
样例四
建立一个触发器 t4，将 Student 表中所有修改时改前的记录及修改日期保存到 ss 表中，作为历史记录。
CREATE TRIGGER t4
ON Student
AFTER UPDATE
AS
INSERT INTO ss(editdate, sno, sname, ssex, sbirthday, sclass)
SELECT GETDATE(), deleted.* FROM deleted
样例五
创建触发器 t5，当删除 student 表中的某个学生记录时，同时删除 Score 表中该生的选课记录。
CREATE TRIGGER t6
ON Student
AFTER DELETE
AS
DECLARE @num char(5)
SELECT @num = deleted.sno FROM deleted
DELECT FROM Score WHERE Score.Sno = @num
样例六
创建视图 V_cj（学号，姓名，课程名，成绩）。
CREATE VIEW V_cj AS
SELECT S.Sno, Sname, Cname, Degree
FROM Student S
JOIN Score SC ON S.Sno = SC.Sno
JOIN Course C ON SC.Cno = C.Cno
由于对视图插入数据需要对每个表分别插入，此时可以创建触发器 T_V 通过视图完成添加成绩。
CREATE TRIGGER T_V
ON V_cj
INSTEAD OF INSERT
AS
INSERT Score
SELECT Sno, Cno, Degree
FROM inserted I
JOIN Course C ON I.Cname = C.Cname
样例七
创建表 scoreB(xh,kch,cj) xh,kch 为主键，为 score (SNO,CNO,DEGREE) 的备份，用触发器完成对 score 的任何修改自动反映到 scoreB 中(scoreB 和 score 数据保持一致)。
CREATE TRIGGER T_bak
ON ScoreB
AFTER INSERT, UPDATE, DELETE
AS
set nocount on
DELETE ScoreB WHERE deleted.sno = ScoreB.sno AND deleted.cno = ScoreB.cno
INSERT INTO ScoreB SELECT * FROM inserted
参考资料
《SqlServer 2014 数据库技术实用教程》，胡伏湘、肖玉朝 主编，清华大学出版社
