SQL Server 触发器（ trigger ) ------- 用例详解（ 你需要举一反三的触发器实用方法都在这了 ）

trigger

第一部分

1. 概述

① 触发器的特点

1. 触发器不能被直接调用执行，它只能由事件触发而自动执行。
2. 触发器是自动执行的，当用户对表中数据作了某些操作之后立即被触发。
3. 触发器可通过数据库中的相关表实现级联更改，实现多个表之间数据的一致性和完整性。
4. 触发器可以实现比用 CHECK 约束定义的约束更为复杂的约束。与 CHECK 约束不同，触发器可以引用其它表中的列。
5. 触发器也可以评估数据修改前后的表状态，并根据其差异采取对策。

② 触发器的作用

实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性。

③ 触发器的分类

1. DDL

主要包括 create alter drop

2. DML

主要包括 insert update delete

3. 登录触发器

登录触发器将在登录的身份验证阶段完成之后且用户会话实际建立之前激发。如果身份验证失败，将不激发登录触发器。
登录触发器可从任何数据库创建，在服务器级注册，并驻留在 master 数据库中。可以使用登录触发器来审核和控制服务器会话。

④ DML 触发器的分类

• AFTER 触发器

AFTER触发器又称为后触发器，该类触发器是在触发操作（INSERT、UPDATE或 DELETE）后和处理完任何约束后激发。
此类触发器只能定义在表上，不能创建在视图上。可以为每个触发操作（INSERT，UPDATE或DELETE）创建多个AFTER触发器。

• INSTEAD OF 触发器

INSTEAD OF触发器又称为替代触发器，该类触发器代替触发动作进行激发，并在处理约束之前激发。
该类触发器既可定义在表上，也可定义在视图上。对于每个触发操作（UPDATE、DELETE 和 INSERT），每个表或视图只能定义一个 INSTEAD OF 触发器。

• CLR 触发器

CLR 触发器可以是 AFTER 触发器或 INSTEAD OF 触发器。CLR 触发器还可以是 DDL 触发器。CLR 触发器将执行在托管代码中编写的方法，而不用执行 Transact-SQL 存储过程。

第二部分 实现

1. 触发器的创建

① insert 触发器的创建

在 STUMS 数据库的教师表上创建一个名为 js_insert_trigger 的触发器，当执行 INSERT 操作时，该触发器被触发，提示 “ 禁止插入记录！” 。

CREATE TRIGGER js_insert_trigger ON 教师
FOR INSERT 
AS   
BEGIN
PRINT('禁止插入记录！')
ROLLBACK TRANSACTION
END
GO

在 STUMS 数据库的专业表上创建一个名为 zy_insert_trigger 的触发器，当执行 INSERT 操作时，该触发器被触发，提示 “ 禁止插入记录！” 。

代码如下：

USE STUMS
GO
CREATE TRIGGER zy_insert_trigger ON 专业
INSTEAD OF INSERT
AS
PRINT('禁止插入记录！')
GO

• 当用户向上面两张表中插入记录时，触发器被激发，插入操作将告失败！

② delete 触发器的创建

在 STUMS 数据库的教师表上创建一个名为 js_delete_trigger 的触发器，当执行 DELETE 操作时，该触发器被触发，提示 “ 禁止删除记录！” 。
代码如下：

USE STUMS
GO
CREATE TRIGGER js_delete_trigger ON 教师
FOR DELETE
AS
BEGIN
PRINT('禁止删除记录！')
ROLLBACK TRANSACTION
END
GO

③ update 触发器的创建

在 STUMS 数据库的 “ 教师 ” 表上创建一个名为 js_update_trigger 的 DML 触发器，用以检查是否修改了 “ 教师 ” 表中姓名列的数据，若作了修改，该触发器被触发，提示 “ 不允许修改！” 。

代码如下：

USE STUMS
GO
CREATE TRIGGER js_update_trigger ON 教师
FOR UPDATE
AS
BEGIN
IF UPDATE(姓名)     /*检测是否修改了姓名列数据*/
PRINT('不允许修改！')
ROLLBACK TRANSACTION
END
GO

2. 多表级联插入触发器

在 STUMS 数据库的学生基本信息表上创建一个名为 xs_insert_trigger 的触发器，当在学生基本信息表中插入记录时，将该记录中的学号自动插入 Student 表。

代码如下：

USE STUMS
GO
CREATE TRIGGER xs_insert_trigger ON 学生基本信息
FOR INSERT
AS
DECLARE @XH CHAR(9)  /*定义局部变量*/
SELECT @XH = 学号 FROM INSERTED  /*从INSERTED表中取出学号赋给变量@XH */
INSERT Student(学号)
VALUES(@XH)   /*将变量@XH的值插入到选课表*/
GO

在 STUMS 数据库的选课表上创建一个名称为 xk_insert_trigger 触发器，当向选课表中插入记录时，检查该记录的学号在学生基本信息表中是否存在，如果不存在，则不允许插入。

代码如下：

USE STUMS
GO
CREATE TRIGGER xk_insert_trigger ON 选课
FOR INSERT
AS
/* 定义局部变量 */
DECLARE @XH CHAR(9)
/* 根据 inserted 表中的学号，查询 “ 学生基本信息 ” 表中对应的学号并赋给变量 @XH */
SELECT @XH = 学生基本信息.学号
FROM 学生基本信息, inserted
WHERE 学生基本信息.学号 = inserted.学号
/* 根据 @XH 变量的值，作出相应的处理 */
IF @XH <> ''
PRINT('记录插入成功')
ELSE
BEGIN
PRINT('学号不存在，不能插入记录，插入将终止！')
ROLLBACK TRANSACTION
END
GO

3. 多表级联删除触发器

在 STUMS 数据库的学生基本信息表上创建一个名称为 xs_delete_trigger 触发器，当删除学生基本信息表中的记录时，同步删除该学号在选课表中的所有记录，并显示提示信息 “ 选课表中相应记录也被删除！” 。

代码如下：

USE STUMS
GO
CREATE TRIGGER xs_delete_trigger ON 学生基本信息
FOR DELETE
AS
BEGIN
DELETE 选课 WHERE 学号 IN (SELECT 学号 FROM DELETED)
PRINT('选课表中相应记录也被删除!')
END

在STUMS数据库的系部表上创建一个名称为 xibu_delete_trigger 触发器，当删除系部表中的记录时，如果学生基本信息表中引用了此记录的系部代码，则提示 “ 用户不能删除！”，否则提示 “ 记录已删除！”。

代码如下：

USE STUMS
GO
CREATE TRIGGER xibu_delete_trigger ON 系部
FOR DELETE
AS
IF (SELECT COUNT(*) 
FROM 学生基本信息  
INNER JOIN DELETED 
ON 学生基本信息.系部代码 = DELETED.系部代码
) > 0
BEGIN
PRINT('该系部代码被引用,用户不能删除！')
ROLLBACK TRANSACTION
END
ELSE
PRINT('记录已删除!')
GO

4. 多表级联修改触发器

在 STUMS 数据库的系部表上创建一个名称为 xibu_update_trigger1 触发器，当修改系部表中的系部代码时，如果学生基本信息表中引用了该系部代码，则提示 “ 用户不能修改！”，否则提示 “ 记录已修改！” 。

代码如下：

CREATE TRIGGER xibu_update_trigger1 ON 系部
FOR UPDATE
AS
IF UPDATE(系部代码)
BEGIN
DECLARE @XBDM CHAR(2)
SELECT @XBDM = DELETED.系部代码 FROM DELETED
IF EXISTS (SELECT 系部代码 FROM 学生基本信息
WHERE 系部代码 = @XBDM)
BEGIN
PRINT('该系部代码被引用,用户不能修改！')
ROLLBACK TRANSACTION
END
ELSE
PRINT('记录已修改!')
END
GO

例如，当用户修改系部表中的系部代码时，就激发 xibu_update_trigger1 触发器，在学生基本信息表中没有引用系部表中的 “ 02 ” 系部代码， 记录就被修改 ；在学生基本信息表中引用了系部表中的 “ 02 ” 系部代码，就禁止修改。

在 STUMS 数据库的系部表上创建一个名称为 xibu_update_trigger2 触发器，当修改系部表中的系部代码时，如果学生基本信息表中引用了该系部代码，则作同样地修改，并提示 “ 记录已修改！”。

代码如下：

CREATE TRIGGER xibu_update_trigger2 ON 系部
FOR UPDATE
AS
IF UPDATE(系部代码)
BEGIN
DECLARE @XBDM1 CHAR(2),@XBDM2 CHAR(2)
SELECT @XBDM1 = DELETED.系部代码, @XBDM2 = INSERTED.系部代码
FROM DELETED, INSERTED
UPDATE 学生基本信息
SET 系部代码 = @XBDM2
WHERE 系部代码 = @XBDM1
PRINT('记录已修改!')
END

例如，当用户将系部表中 “ 07 ” 系部代码改为 “ 12 ” 时，激发了 xibu_update_trigger2 触发器，学生基本信息表中有若干条记录引用了 “ 07 ” 系部代码，记录都作了同样地修改。

5. DDL 触发器

为 STUMS 数据库创建一个名为 STUMS_DDL_TRG 触发器，当在 STUMS 数据库中创建、修改或删除表时，显示警告信息 “ 禁止在当前数据库中操作数据表！”，并取消这些 DDL 操作。

代码如下：

USE STUMS
GO
CREATE TRIGGER STUMS_DDL_TRG ON DATABASE
FOR CREATE_TABLE,ALTER_TABLE, DROP_TABLE /*指定事件类型*/
AS
BEGIN
RAISERROR('禁止在当前数据库中操作数据表！',16,1)  /*错误提示信息*/
ROLLBACK TRANSACTION   /*取消DDL操作*/
END
GO

6. 使用系统存储过程查看触发器

① 可使用 sp_help 查看触发器的一般信息；

EXEC sp_help xibu_delete_trigger

② 可使用 sp_depends 查看触发器的相关性；

EXEC sp_depends xibu_delete_trigger

③ 可使用 sp_helptext 查看触发器的定义信息；

EXEC sp_helptext xibu_delete_trigger

④ 可使用 sp_helptrigger 查看指定表上存在的触发器类型。

EXEC sp_helptrigger 系部

7. 修改触发器

① 改名

利用 sp_rename 系统存储过程 将 “ xibu_delete_trigger ” 触发器改名为 “ xibu_delete_DMLTRG ”。

代码如下：

EXEC sp_rename xibu_delete_trigger, xibu_delete_DMLTRG
GO

② 改质

修改 STUMS 数据库教师表上的 js_delete_trigger 触发器，使得用户执行删除、插入、修改操作时，该触发器被触发，自动给出提示报警信息，并撤销此次操作。

代码如下：

USE STUMS
GO
ALTER TRIGGER js_delete_trigger ON 教师
FOR DELETE,INSERT,UPDATE
AS
BEGIN
PRINT('你不能删除、插入、修改记录！')
ROLLBACK TANSACTION
END
GO

8. 禁用启用触发器

DDL

/* 禁用 DDL 触发器 */
DISABLE TRIGGER STUMS_DDL_TRG ON DATABASE
GO
/* 启用 DDL 触发器 */
ENABLE TRIGGER STUMS_DDL_TRG ON DATABASE
GO

DML

-- 禁用 DML 触发器
ALTER TABLE 教师 DISABLE TRIGGER js_delete_trigger
GO
-- 启用 DML 触发器
ALTER TABLE 教师 ENABLE TRIGGER js_delete_trigger
GO

9. 删除触发器

DROP TRIGGER js_delete_trigger