触发器 - 简书

一.什么是触发器

触发器是一个在修改指定表值的数据时执行的存储过程，不同的是执行存储过程要使用EXEC语句来调用，而触发器的执行不需要使用EXEC调用，通过触发器可以保证不同表中的逻辑相关数据的引用完整性或一致性。
主要优点：
（1）触发器是自动的。
（2）触发器可以通过数据库中的相关表进行层叠更改。
（3）触发器可以强制限制。与CHECK约束不同的是，触发可以引用其他表中的列。

二.触发器的作用

触发器的主要作用就是其能够实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性，它能够对数据库中的相关表进行行级联修改。
触发器的主要作用有以下几个方面:
（1）强制数据库间的引用完整性
（2）级联修改数据库中所有相关的表，自动触发其他与之相关的操作。
（3）跟踪变化，撤销或回滚违法操作，防止非法修改数据。
（4）返回自定义的错误信息，约束无法返回信息，而触发器可以。
（5）触发器可以调用更多的存储过程。

三.触发器的分类

触发器有两种类型：数据操作语言触发器（DML）和数据定义语言触发器（DDL）。

数据操作语言触发器
- 通过数据库中的相关表实现级联更改。
- 防止恶意或错误的INSERT、UPDATE和DELETE操作，并强制执行比CHECK约束定义的限制更复杂的其他限制。
- 评估数据修改前后表的状态，并根据该差异采取措施，针对每个DML触发器定义了两个特殊表：DELETED表和INSERTED表。DELETED表存放DELETE或UPDATE语句中删除的行，INSERTED表存放INSERT和UPDATE中插入的行。
  2.数据定义语言触发器
  DDL触发器是当服务器或数据库中发生数据定义语言事件时被激活调用，使用DDL触发器可以防止对数据库框架进行的某些更改或记录数据库框架中的更改或事件。

四.创建DML触发器

--语法
CREATE TRIGGER trigger_name
ON {table | view}
{FOR | AFTER | INSTEAD OF} {[DELETE] [,] [INSERT] [,] [UPDATE]}
AS
SQL_STATEMENT

--插入触发器，往employee表插入数据时，检查emp表有没有employee刚插入的数据，如果没有就往emp表插入相同的数据，非则不插入
CREATE TRIGGER Insert_Employee
ON employee AFTER INSERT
AS
BEGIN
    IF NOT EXISTS(SELECT * FROM inserted i INNER JOIN emp e ON i.e_no=e.e_no)
    INSERT INTO emp SELECT * FROM inserted 
    ELSE
    PRINT 'emp已存在该数据'
END

--删除employee表中的数据，如果删除的数据的dept_no在employee表中已经不存在了，自动删除关联表dept中d_no对应dept_no值的数据
CREATE TRIGGER Delete_Employee
ON employee AFTER DELETE
AS
BEGIN
    IF EXISTS(
    SELECT * FROM employee WHERE dept_no=(SELECT dept_no FROM deleted))
    PRINT '该dept_no值还存在与employee表中'
    ELSE
    DELETE FROM dept WHERE d_no=(SELECT dept_no FROM deleted) 
END

3.替代触发器
执行触发AFTER触发器的SQL代码后，先建立临时的INSERTED和DELETED表，然后执行SQL代码中对数据的操作，最后才激活触发器中的代码。而替代触发器，在执行INSTEAD OF触发器的代码时，先建立临时的INSERTED和DELETED表，然后直接触发INSTEAD OF触发器，而拒绝执行用户输入的DML语句。
基于多个基本表的视图必须使用INSTEAD OF触发器来对多个表中的数据进行插入、更新和删除操作。

--INSTEAD OF不执行employee插入数据的操作，而是直接执行替代触发器里的代码。
--插入employee表中的数据没有插入，但触发器里插入emp表中的数据插入进去了。
CREATE TRIGGER Insert_Employee
ON employee INSTEAD OF INSERT
AS
BEGIN
    IF NOT EXISTS(SELECT * FROM inserted i INNER JOIN emp e ON i.e_no=e.e_no)
    INSERT INTO emp SELECT * FROM inserted 
    ELSE
    PRINT 'emp已存在该数据'
END

五.嵌套触发器

如果触发器在执行操作时调用了另一个触发器，而这个触发器又接着调用了下一个触发器，那么就形成了嵌套触发器。
使用触发器需要考虑以下注意事项：
（1）默认情况下，嵌套触发器配置选项是开启的。
（2）在同一个触发器事务中，一个嵌套触发器不能被触发两次。
（3）由于触发器是一个事务，如果在一系列嵌套触发器的任意层发生错误，则整个事务都将被取消，而所有数据将会回滚。

六.递归触发器

触发器的递归是指一个触发器从其内部在一次激活该触发器。
递归触发器包括两种：直接递归和间接递归。
直接递归：触发器被触发并执行一个操作，而该操作又使同一个触发器再次被触发。
间接递归：触发器被触发并执行一个操作，而该操作又使另一个表中的某个触发器被触发，第二个触发器使原始表得到更新，从而再次触发第一个触发器。

七.创建DDL触发器

--DATABASE:当前数据库，设置后当前数据库下的表不能进行删除和修改操作
CREATE TRIGGER DenyDelete_teacher
ON DATABASE
FOR DROP_TABLE,ALTER_TABLE
AS
BEGIN
PRINT '用户没有权限执行删除操作！'
ROLLBACK TRAN
END

八.管理触发器

1.查看触发器

sp_helptext Insert_Employee

2.修改触发器

--修改触发器和创建触发器语句相似，把创建触发器的CREATE语句改为ALTER语句就
--可以
ALTER TRIGGER Insert_Employee
ON employee AFTER INSERT
AS
BEGIN
    IF EXISTS(SELECT * FROM inserted i INNER JOIN emp e ON i.e_no=e.e_no)
    PRINT 'emp已存在该数据'
    ELSE
    INSERT INTO emp SELECT * FROM inserted
END

3.删除触发器

--DROP TRIGGER 触发器名称
DROP TRIGGER Insert_Employee

4.启用和禁用触发器
触发器创建之后便启用了，如果暂时不需要使用某个触发器，可以将其禁用。触发器被禁用后并没有删除，它仍然作为对象存储在当前数据库中。
（1）禁用触发器

ALTER TABLE employee
DISABLE TRIGGER Insert_Employee

（2）启用触发器

ALTER TABLE employee
ENABLE TRIGGER Insert_Employee