SQL-触发器(trigger)的详解以及代码演示
一、触发器的概念
触发器是一种特殊的存储过程，但是触发器不存在输入和输出参数，所以不能被显式的去调用，而是与特定的表相关联，当表中的数据发生变化时，触发器被激活并执行其定义的SQL代码。触发器可以是行级触发器（针对每一行数据变化）或语句级触发器（针对整个SQL语句的变化），行级触发器允许对每一行单独处理，而语句级触发器则在整个操作完成后执行一次。
触发器的功能
- 强化约束：触发器能够实现比CHECK语句更为复杂的约束。它可以很方便地引用其他表的列，进行逻辑上的检查。
- 跟踪变化：触发器可以检测数据库内的操作，从而禁止数据库中未经许可的更新和变化，以确保输入表中的数据的有效性。
- 级联运行：触发器可以检测数据库内的操作，并自动地级联影响整个数据库的不同表的各项内容。
- 调用存储过程：为了响应数据库更新，可以调用一个或多个触发器。
二、触发器种类及分类
触发器主要分为DML触发器和DDL触发器两种。
-
DML触发器：用户通过数据操作语句DML（对表或视图的INSERT、DELETE、UPDATE）编辑数据，则执行DML触发器。DML触发器可以在记录变更前（INSTEAD OF）或记录变更后（AFTER）被触发。
- AFTER触发器：在记录已经被修改完，事务已提交后被触发执行。主要用于记录变更后的处理或检查。(比如在订单系统中，当订单金额超过某个阈值时，需要进行额外的校验，如检查客户的信用额度。就可以在订单表被插入或更新时，检查订单金额是否超过阈值，并进行相应的校验操作。)
- INSTEAD OF触发器：不去执行其定义的操作（INSERT、UPDATE、DELETE），而是交给触发器执行。触发器检查操作是否正确，若正确则执行操作。这类触发器用来取代原本的操作，在记录变更之前被触发。(比如插入一条问卷数据后，执行你的逻辑业务，比如对根据问卷数据中的答案统计出反馈指标插入到反馈表中)
-
DDL触发器：为了响应各种数据定义语言DDL（如CREATE、ALTER、DROP）事件而激发。DDL触发器可以用于在数据库执行管理任务。
三、触发器语法（T-SQL）
1. 创建DML触发器
CREATE TRIGGER 触发器名称
ON { table | view }
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] | [ UPDATE ] | [ DELETE ] }
AS
SQL语句
举例：
AFTER触发器：修改student表数据后，查询修改后的数据。
CREATE TRIGGER trig_student_After
ON student
FOR UPDATE
AS
PRINT 'THE TRIGGER IS AFTER'
SELECT * FROM student2. 创建DDL触发器
CREATE TRIGGER 触发器名称
ON { ALL SERVER | DATABASE }
{ FOR | AFTER }
{ 事件类型 | 事件组 }
AS
SQL语句举例：创建数据库后输出提示信息。
CREATE TRIGGER trig_create
ON ALL SERVER
AFTER CREATE_DATABASE
AS
PRINT '创建数据库'3. 修改触发器
修改DML或DDL触发器时，可以将CREATE关键字修改为ALTER。
ALTER TRIGGER 触发器名称
ON { table | view | ALL SERVER | DATABASE }
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] | [ UPDATE ] | [ DELETE ] | 事件类型 | 事件组 }
AS
SQL语句4. 删除触发器
DROP TRIGGER 触发器名称5.使用系统存储过程查看触发器
-
sp_helptrigger
sp_helptrigger存储过程用于返回与指定表或视图关联的触发器的信息。EXEC sp_helptrigger 'your_table_or_view_name'; -
sp_helptext
sp_helptext存储过程用于返回指定数据库对象的定义文本，包括触发器、存储过程、视图等。EXEC sp_helptext 'your_trigger_name';这将返回指定触发器的定义文本，包括触发器的CREATE语句。
-
sys.triggers 和 sys.trigger_events
虽然这些不是存储过程，但它们是系统视图，提供了关于触发器及其事件的详细信息。你可以查询这些视图来获取触发器的各种属性。
SELECT t.name AS TriggerName, t.parent_id AS ParentObjectID, OBJECT_NAME(t.parent_id) AS ParentObjectName, te.type_desc AS TriggerType, te.event_type_desc AS EventType FROM sys.triggers t INNER JOIN sys.trigger_events te ON t.object_id = te.object_id;这个查询将返回触发器的名称、父对象（表或视图）的名称、触发器的类型（如AFTER、INSTEAD OF）以及触发事件（如INSERT、UPDATE、DELETE）。
-
sys.sql_modules
sys.sql_modules视图包含数据库中所有用户定义对象（如触发器、存储过程、函数等）的SQL定义。你可以通过查询这个视图来获取触发器的完整定义。SELECT m.definition AS TriggerDefinition, o.name AS TriggerName FROM sys.sql_modules m INNER JOIN sys.objects o ON m.object_id = o.object_id WHERE o.type = 'TR' -- 'TR' 表示触发器 AND o.name = 'your_trigger_name'; -- 可选，用于指定触发器名称这个查询将返回指定触发器的完整定义文本。如果不指定触发器名称，它将返回所有触发器的定义。
-
INFORMATION_SCHEMA.TRIGGERS
INFORMATION_SCHEMA.TRIGGERS视图提供了关于数据库中触发器的标准信息。然而，它可能不包含触发器的完整定义或某些特定于SQL Server的属性。SELECT TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE, ACTION_STATEMENT_TYPE, ACTION_TIMING FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_SCHEMA = 'your_schema_name'; -- 可选，用于指定架构名称这个查询将返回触发器的名称、触发事件类型、触发对象表名、动作语句类型以及动作时机（如BEFORE或AFTER，但注意SQL Server中通常是AFTER或INSTEAD OF）。
6.禁用触发器
- DDL
/** 禁用 */ DISABLE TARRGER DDL_TRG_NAME ON DATABASE GO/** 启用 */ ENABLE TARRGER DDL_TRG_NAME ON DATABASE GO
- DML
/** 禁用 **/ ALTER TABLE TableNanme DISABLE TRIGGER DML_TRG_NAME GO/** 启用 **/ ALTER TABLE TableNanme ENABLE TRIGGER DML_TRG_NAME