SQL Server教程 - T-SQL-触发器（TRIGGER）

更新记录
转载请注明出处：
2022年8月1日 发布。
2022年7月2日 从笔记迁移到博客。

触发器说明（TRIGGER）(触发器是什么)

触发器是一种与表操作有关的数据库对象，当触发器所在表上出现指定事件时，将调用该对象即表的操作事件触发表上的触发器的执行。多条SQL语句的组合，类似存储过程，特殊的存储过程。特定事件条件下自动触发，不可以手动调用。触发器是一个独立执行单元,发生错误将回滚。只有INSERT、UPDATE、DELETE操作可以绑定触发器。

触发器作用

触发器的主要作用就是其能够实现由主键和外键所不能保证的复杂的引用完整性和数据的一致性，它能够对数据库中的相关表进行级联修改，能提供比CHECK约束更复杂的数据完整性，并自定义错误信息。触发器的主要作用有下几个方面：

强制数据库间的引用完整性。
级联修改数据库中所有相关的数据表，自动触发其他与之相关的操作。
跟踪变化，撤销或回滚违法操作，防止非法修改数据。
返回自定义的错误信息，约束无法返回信息，而触发器可以。
触发器可以调用更多的存储过程。

触发器优点

（1)触发器是自动的。当对数据表中的数据做了任何修改（比如手工输入或者应用程序采取的操作）之后触发器会立即被激活。
（2)触发器可以通过数据库中的相关数据表进行层叠更改。
（3)触发器可以强制一些限制。这些限制比用CHECK约束所定义的更复杂。与CHECK约束不同的是，触发器可以引用其他数据表中的列（即数据表中的字段）。

使用触发器需要注意的问题是什么

在使用触发器的时候需要注意的是，对相同的数据表、相同的事件只能创建一个触发器，比如对表 account创建了一个AFTER INSERT触发器，那么如果对表 account 再次创建一个 AFTER INSERT触发器，SQL Server将会报错，此时，只可以在表 account上创建 AFTER INSERT 或者INSTEAD OF UPDATE类型的触发器。灵活地运用触发器将为操作省去很多麻烦。

不再使用的触发器记得及时删除。

触发器与存储过程的区别

触发器与存储过程的主要区别在于触发器的运行方式，存储过程需要用户、应用程序或者触发器来显式地调用并执行，而触发器是当特定事件（INSERT、UPDATE、DELETE)出现的时候，自动执行。

触发器分类

数据操作触发器(DML)：INSERT、UPDATE、DELETE触发器。
数据定义触发器(DDL)
登录触发器：与SQL Server实例建立用户会话时将引发此事件。
注意：登录触发器将在登录的身份验证阶段完成之后且用户会话实际建立之前激发。

定义DML触发器-使用T-SQL

定义DML触发器

CREATE TRIGGER 触发器名
ON 表|视图
[WITH 选项]
FOR AFTER | INSTEAD OF
[INSERT | DELETE | UPDATE]
AS
BEGIN
-- SQL语句
END

说明：如果是INSTEAD OF类型，则触发器完成之后再将检查约束
说明：如果是AFTER类型，则触发器执行之前进行检查约束
注意：触发事件可以是INSERT/DELETE/UPDATE 或者其混合
注意：不建议在触发器中使用游标，因为可能会降低性能
注意：触发器带有事务功能，如果触发器执行失败，将会被回滚

实例：插入数据的同时插入数据到另一个表

CREATE TRIGGER [PandaTrigger]
ON [PandaTable]
FOR INSERT
AS
BEGIN
    INSERT INTO [PandaTable2](Name) VALUES('Dog');
END

实例：禁止插入数据到表中

CREATE TRIGGER Insert forbidden
ON stu Sum
AFTER INSERT
AS
BEGIN
	RAISERROR('不允许直接向该表插入记录，操作被禁止'，1,1)
	ROLLBACK TRANSACTION
END

实例：删除数据后获得被删除的数据

CREATE TRIGGER Delete Student
ON stu info
AFTER DELETE
AS
BEGIN
	SELECT s id AS 已删除的学生编号，s_name,s_score,s_sex,s_age
	FROM DELETED
END

实例：更新表后执行操作

CREATE TRIGGER
Update Student
ON stu_info
	AFTER UPDATE
AS
BEGIN
	DECLARE @stuCount INT;
	SELECT @stuCount = COUNT(*) FROM stu info;
	UPDATE stu Sum SET number = @stuCount;
	SELECT s_id AS 更新前学生编号，s name AS 更新前学生姓名
	FROM DELETED
	SELECT s_id AS 更新后学生编号， s name AS 更新后学生姓名
	FROM INSERTED
END

定义DML触发器-使用SSMS定义DML触发器-使用SSMS

定义DDL触发器-使用T-SQL

CREATE TRIGGER 触发器名
ON [SERVER | DATABASE]
AFTER event_type
AS
-- SQL语句

event_type可以是：CREATE_TABLE/ALTER_TABLE/DROP_TABLE等DDL语句

实例：拒绝用户对数据操作

CREATE TRIGGER DenyDelete test
ON DATABASE
FOR DROP TABLE,ALTER TABLE
AS
BEGIN
	PRINT '用户没有权限执行删除操作！'
	ROLLBACK TRANSACTION
END

实例：拒绝用户对数据库中数据表的删除和修改操作

CREATE TRIGGER DenyCreate_Al1Server
ON ALL SERVER
FOR CREATE DATABASE,ALTER DATABASE
AS
BEGIN
	PRINT '用户没有权限创建或修改服务器上的数据库！'
	ROLLBACK TRANSACTION
END

定义触发器-使用SSMS

修改触发器-使用T-SQL

ALTER TRIGGER 触发器名
ON 表|视图
AFTER
[INSERT][DELETE][UPDATE]
AS
-- SQL语句

修改触发器-使用SSMS

重命名触发器-使用T-SQL

EXECUTE sp_rename 触发器旧名 触发器新名;

禁用触发器-使用T-SQL

DISABLE TRIGGER 触发器名 | ALL
ON 表名;

或

ALTER TABLE 表名
DISABLE TRIGGER 触发器名;

禁用触发器-使用SSMS

启用触发器-使用T-SQL

ENABLE TRIGGER 触发器名 | ALL ON 表名;

或

ALTER TABLE 表名
ENABLE TRIGGER 触发器名;

启用触发器-使用SSMS

查看触发器-使用T-SQL

EXECUTE sp_helptext 触发器名;

查看触发器-使用SSMS

删除触发器-使用T-SQL

DROP TRIGGER 触发器名;

删除触发器-使用SSMS