SqlServer：触发器

目录

• 触发器
• 触发器的分类
• 与触发器相关的临时表
• SQL 语句
• 创建样例
  • 样例一
  • 样例二
  • 样例三
  • 样例四
  • 样例五
  • 样例六
• 样例七
• 参考资料

触发器#

触发器（Trigger)用户对某一个表进行 INSERT、UPDATE 和 DELECT 操作时，被用户的行为触发执行的一段程序。触发器可用来检查用户对表的操作是否合乎整个应用系统的需求，维持表内数据的完整性和正确性。触发器是表的对象，是由系统自动触发执行的，不要也不能运用命令来执行，和存储过程一样是由一组 SQL 语句写成的程序。
触发器的功能和表内所设置的约束有些重叠，如果列约束的功能能够达到应用程序的要求，则无须设计触发器，但是，作为约束的补充或者在维护表时，使用触发器会更加方便。使用触发器具有以下三个作用：

1. 在增加、删除、修改记录前检查数据的完整性；
2. 当触发器检查通不通过时，回滚以确保数制的合法性。
3. 对表进行维护操作时利用触发器增加其功能。

触发器的分类#

根据触发器中的代码还是 INSERT、UPDATE 和 DELECT 操作哪个先执行，可以将触发器分为 AFTER 和 INSTEAD OF 触发器。

1. AFTER 触发器：先执行表的 INSERT、UPDATE 和 DELECT 操作，然后才执行触发器的语句。AFTER 触发器只有执行 INSERT、UPDATE 和 DELETE 中某一操作之后才被触发且只能针对表进行定义，可以针对一个表的同一操作定义多个触发器。
2. 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 触发器。建立触发器的命令格式是:

Copy Highlighter-hljs
CREATE TRIGGER 触发器名
ON 表名
[WITH ENCRYPTLON]
FOR | AFTER | INSTEAD OF
[INSERT][,][UPDATE][,][DELETE]
AS
[IF 触发条件]
BEGIN
    SQL语句
END

创建样例#

假设 Student 表中有如下一些数据：

样例一#

在 Student 表上创建触发器 t1，在用户插入、修改和删除记录时，都会自动显示表中的内容。

Copy Highlighter-hljs
CREATE TRIGGER t1 
ON Student
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SELECT * FROM Student
END

样例二#

建立一个触发器 t2，当向 Student 表中插入数据时，如果姓名不重复则插入。如果出现姓名重复的情况，则提示错误（raiserror('姓名重复，不能插入',16,1)）并回滚该事务。

Copy Highlighter-hljs
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

此时若插入重复的名字，会报错。

Copy Highlighter-hljs
INSERT INTO Student(Sno, Sname, Ssex) VALUES(001, '李君帅', '男')

样例三#

创建修改触发器 t3，该触发器防止用户修改表 Student 的学号。

Copy Highlighter-hljs
CREATE TRIGGER t3
ON Student
AFTER UPDATE
AS
IF UPDATE(Sno)
BEGIN
    raiserror('不能修改学号', 16, 2)
    rollback
End

样例四#

建立一个触发器 t4，将 Student 表中所有修改时改前的记录及修改日期保存到 ss 表中，作为历史记录。

Copy Highlighter-hljs
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 表中该生的选课记录。

Copy Highlighter-hljs
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（学号，姓名，课程名，成绩）。

Copy Highlighter-hljs
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 通过视图完成添加成绩。

Copy Highlighter-hljs
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 数据保持一致)。

Copy Highlighter-hljs
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 数据库技术实用教程》，胡伏湘、肖玉朝 主编，清华大学出版社