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

触发器

触发器（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 中哪个操作，采用 AFTERFOR) 触发器还是 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 char10)
    SELECT @name = inserted.Sname FROM inserted
    IF existsSELECT 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 StudentSno, Sname, Ssex) VALUES001, '李君帅', '男')

样例三

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

CREATE TRIGGER t3
ON Student
AFTER UPDATE
AS
IF UPDATESno)
BEGIN
    raiserror'不能修改学号', 16, 2)
    rollback
End

样例四

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

CREATE TRIGGER t4
ON Student
AFTER UPDATE
AS
INSERT INTO sseditdate, sno, sname, ssex, sbirthday, sclass)
SELECT GETDATE), deleted.* FROM deleted

样例五

创建触发器 t5，当删除 student 表中的某个学生记录时，同时删除 Score 表中该生的选课记录。

CREATE TRIGGER t6
ON Student
AFTER DELETE
AS
DECLARE @num char5)
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

样例七

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