SQL-触发器(trigger)的详解以及代码演示

一、触发器的概念

触发器是一种特殊的存储过程，但是触发器不存在输入和输出参数，所以不能被显式的去调用，而是与特定的表相关联，当表中的数据发生变化时，触发器被激活并执行其定义的SQL代码。触发器可以是行级触发器（针对每一行数据变化）或语句级触发器（针对整个SQL语句的变化），行级触发器允许对每一行单独处理，而语句级触发器则在整个操作完成后执行一次。

触发器的功能

1. 强化约束：触发器能够实现比CHECK语句更为复杂的约束。它可以很方便地引用其他表的列，进行逻辑上的检查。
2. 跟踪变化：触发器可以检测数据库内的操作，从而禁止数据库中未经许可的更新和变化，以确保输入表中的数据的有效性。
3. 级联运行：触发器可以检测数据库内的操作，并自动地级联影响整个数据库的不同表的各项内容。
4. 调用存储过程：为了响应数据库更新，可以调用一个或多个触发器。

二、触发器种类及分类

触发器主要分为DML触发器和DDL触发器两种。

1. DML触发器：用户通过数据操作语句DML（对表或视图的INSERT、DELETE、UPDATE）编辑数据，则执行DML触发器。DML触发器可以在记录变更前（INSTEAD OF）或记录变更后（AFTER）被触发。

   • AFTER触发器：在记录已经被修改完，事务已提交后被触发执行。主要用于记录变更后的处理或检查。(比如在订单系统中，当订单金额超过某个阈值时，需要进行额外的校验，如检查客户的信用额度。就可以在订单表被插入或更新时，检查订单金额是否超过阈值，并进行相应的校验操作。)
   • INSTEAD OF触发器：不去执行其定义的操作（INSERT、UPDATE、DELETE），而是交给触发器执行。触发器检查操作是否正确，若正确则执行操作。这类触发器用来取代原本的操作，在记录变更之前被触发。(比如插入一条问卷数据后，执行你的逻辑业务，比如对根据问卷数据中的答案统计出反馈指标插入到反馈表中)

2. 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 student

 2. 创建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