DML 触发器DML Triggers

适用于: 是SQL Server是Azure SQL 数据库否Azure SQL 数据仓库否并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

DML 触发器为特殊类型的存储过程,可在发生数据操作语言 (DML) 事件时自动生效,以便影响触发器中定义的表或视图。DML triggers is a special type of stored procedure that automatically takes effect when a data manipulation language (DML) event takes place that affects the table or view defined in the trigger. DML 事件包括 INSERT、UPDATE 或 DELETE 语句。DML events include INSERT, UPDATE, or DELETE statements. DML 触发器可用于强制业务规则和数据完整性、查询其他表并包括复杂的 Transact-SQLTransact-SQL 语句。DML triggers can be used to enforce business rules and data integrity, query other tables, and include complex Transact-SQLTransact-SQL statements. 将触发器和触发它的语句作为可在触发器内回滚的单个事务对待。The trigger and the statement that fires it are treated as a single transaction, which can be rolled back from within the trigger. 如果检测到错误(例如,磁盘空间不足),则整个事务即自动回滚。If a severe error is detected (for example, insufficient disk space), the entire transaction automatically rolls back.

DML 触发器的优点DML Trigger Benefits

DML 触发器类似于约束,因为可以强制实体完整性或域完整性。DML triggers are similar to constraints in that they can enforce entity integrity or domain integrity. 一般情况下,实体完整性总应在最低级别上通过索引进行强制,这些索引应是 PRIMARY KEY 和 UNIQUE 约束的一部分,或者是独立于约束而创建的。In general, entity integrity should always be enforced at the lowest level by indexes that are part of PRIMARY KEY and UNIQUE constraints or are created independently of constraints. 域完整性应通过 CHECK 约束进行强制,而引用完整性 (RI) 则应通过 FOREIGN KEY 约束进行强制。Domain integrity should be enforced through CHECK constraints, and referential integrity (RI) should be enforced through FOREIGN KEY constraints. 当约束支持的功能无法满足应用程序的功能要求时,DML 触发器非常有用。DML triggers are most useful when the features supported by constraints cannot meet the functional needs of the application.

下面的列表比较 DML 触发器和约束,并在 DML 触发器优于约束时进行标识。The following list compares DML triggers with constraints and identifies when DML triggers have benefits over .

  • DML 触发器可以将更改通过级联方式传播给数据库中的相关表;不过,使用级联引用完整性约束可以更有效地执行这些更改。DML triggers can cascade changes through related tables in the database; however, these changes can be executed more efficiently using cascading referential integrity constraints. 除非 REFERENCES 子句定义了级联引用操作,否则 FOREIGN KEY 约束只能用与另一列中的值完全匹配的值来验证列值。FOREIGN KEY constraints can validate a column value only with an exact match to a value in another column, unless the REFERENCES clause defines a cascading referential action.

  • DML 触发器可以防止恶意或错误的 INSERT、UPDATE 以及 DELETE 操作,并强制执行比 CHECK 约束定义的限制更为复杂的其他限制。They can guard against malicious or incorrect INSERT, UPDATE, and DELETE operations and enforce other restrictions that are more complex than those defined with CHECK constraints.

    与 CHECK 约束不同,DML 触发器可以引用其他表中的列。Unlike CHECK constraints, DML triggers can reference columns in other tables. 例如,触发器可以使用另一个表中的 SELECT 比较插入或更新的数据,以及执行其他操作,如修改数据或显示用户定义错误信息。For example, a trigger can use a SELECT from another table to compare to the inserted or updated data and to perform additional actions, such as modify the data or display a user-defined error message.

  • DML 触发器可以评估数据修改前后表的状态,并根据该差异采取措施。They can evaluate the state of a table before and after a data modification and take actions based on that difference.

  • 一个表中的多个同类 DML 触发器(INSERT、UPDATE 或 DELETE)允许采取多个不同的操作来响应同一个修改语句。Multiple DML triggers of the same type (INSERT, UPDATE, or DELETE) on a table allow multiple, different actions to take place in response to the same modification statement.

  • 约束只能通过标准化的系统错误消息来传递错误消息。Constraints can communicate about errors only through standardized system error messages. 如果应用程序需要(或能受益于)使用自定义消息和较为复杂的错误处理,则必须使用触发器。If your application requires, or can benefit from, customized messages and more complex error handling, you must use a trigger.

  • DML 触发器可以禁止或回滚违反引用完整性的更改,从而取消所尝试的数据修改。DML triggers can disallow or roll back changes that violate referential integrity, thereby canceling the attempted data modification. 当更改外键且新值与其主键不匹配时,这样的触发器将生效。Such a trigger might go into effect when you change a foreign key and the new value does not match its primary key. 但是,FOREIGN KEY 约束通常用于此目的。However, FOREIGN KEY constraints are usually used for this purpose.

  • 如果触发器表上存在约束,则在 INSTEAD OF 触发器执行后但在 AFTER 触发器执行前检查这些约束。If constraints exist on the trigger table, they are checked after the INSTEAD OF trigger execution but prior to the AFTER trigger execution. 如果违反了约束,则回滚 INSTEAD OF 触发器操作并且不执行 AFTER 触发器。If the constraints are violated, the INSTEAD OF trigger actions are rolled back and the AFTER trigger is not executed.

DML 触发器的类型Types of DML Triggers

AFTER 触发器AFTER trigger
在执行 INSERT、UPDATE、MERGE 或 DELETE 语句的操作之后执行 AFTER 触发器。AFTER triggers are executed after the action of the INSERT, UPDATE, MERGE, or DELETE statement is performed. 如果违反了约束,则永远不会执行 AFTER 触发器;因此,这些触发器不能用于任何可能防止违反约束的处理。AFTER triggers are never executed if a constraint violation occurs; therefore, these triggers cannot be used for any processing that might prevent constraint violations. 对于在 MERGE 语句中指定的每个 INSERT、UPDATE 或 DELETE 操作,将为每个 DML 操作触发相应的触发器。For every INSERT, UPDATE, or DELETE action specified in a MERGE statement, the corresponding trigger is fired for each DML operation.

INSTEAD OF 触发器INSTEAD OF trigger
INSTEAD OF 触发器替代下列触发语句的标准操作。INSTEAD OF triggers override the standard actions of the triggering statement. 因此,触发器可用于对一个或多个列执行错误或值检查,然后在插入、更新或删除行之前执行其他操作。Therefore, they can be used to perform error or value checking on one or more columns and the perform additional actions before insert, updating or deleting the row or rows. 例如,当在工资表中小时工资列的更新值超过指定值时,可以将触发器定义为产生错误消息并回滚该事务,或在将记录插入工资表中之前将新记录插入到审核记录。For example, when the value being updated in an hourly wage column in a payroll table exceeds a specified value, a trigger can be defined to either produce an error message and roll back the transaction, or insert a new record into an audit trail before inserting the record into the payroll table. INSTEAD OF 触发器的主要优点是可以使不能更新的视图支持更新。The primary advantage of INSTEAD OF triggers is that they enable views that would not be updatable to support updates. 例如,基于多个基表的视图必须使用 INSTEAD OF 触发器来支持引用多个表中数据的插入、更新和删除操作。For example, a view based on multiple base tables must use an INSTEAD OF trigger to support inserts, updates, and deletes that reference data in more than one table. INSTEAD OF 触发器的另一个优点是使您得以编写这样的逻辑代码:在允许批处理的其他部分成功的同时拒绝批处理中的某些部分。Another advantage of INSTEAD OF triggers is that they enable you to code logic that can reject parts of a batch while letting other parts of a batch to succeed.

下表对 AFTER 触发器和 INSTEAD OF 触发器的功能进行了比较。This table compares the functionality of the AFTER and INSTEAD OF triggers.

函数Function AFTER 触发器AFTER trigger INSTEAD OF 触发器INSTEAD OF trigger
适用范围Applicability Tables 表和视图Tables and views
每个表或视图包含触发器的数量Quantity per table or view 每个触发操作(UPDATE、DELETE 和 INSERT)包含多个触发器Multiple per triggering action (UPDATE, DELETE, and INSERT) 每个触发操作(UPDATE、DELETE 和 INSERT)包含一个触发器One per triggering action (UPDATE, DELETE, and INSERT)
级联引用Cascading references 无任何限制条件No restrictions apply 不允许在作为级联引用完整性约束目标的表上使用 INSTEAD OF UPDATE 和 DELETE 触发器。INSTEAD OF UPDATE and DELETE triggers are not allowed on tables that are targets of cascaded referential integrity constraints.
执行Execution 晚于:After:

约束处理Constraint processing

声明性引用操作Declarative referential actions

创建插入的删除的inserted and deleted tables creation

触发操作The triggering action
早于:约束处理Before: Constraint processing

替换:触发操作In place of: The triggering action

之后:创建 插入的删除的After: inserted and deleted tables creation
执行顺序Order of execution 可指定第一个和最后一个执行First and last execution may be specified 不适用Not applicable
插入的删除的表中的 varchar(max)nvarchar(max)varbinary(max) 列引用varchar(max), nvarchar(max), and varbinary(max) column references in inserted and deleted tables AllowedAllowed AllowedAllowed
插入的删除的表中的 textntextimage 列引用。text, ntext, and image column references in inserted and deleted tables 不允许Not allowed AllowedAllowed

CLR 触发器CLR Triggers
CLR 触发器可以是 AFTER 触发器或 INSTEAD OF 触发器。A CLR Trigger can be either an AFTER or INSTEAD OF trigger. CLR 触发器还可以是 DDL 触发器。A CLR trigger can also be a DDL trigger. CLR 触发器将执行在托管代码(在 .NET Framework 中创建并在 Transact-SQLTransact-SQL 中上载的程序集的成员)中编写的方法,而不用执行 SQL ServerSQL Server存储过程。Instead of executing a Transact-SQLTransact-SQL stored procedure, a CLR trigger executes one or more methods written in managed code that are members of an assembly created in the .NET Framework and uploaded in SQL ServerSQL Server.

任务Task 主题Topic
说明如何创建 DML 触发器。Describes how to create a DML trigger. 创建 DML 触发器Create DML Triggers
说明如何创建 CLR 触发器。Describes how to create a CLR trigger. 创建 CLR 触发器Create CLR Triggers
说明如何创建 DML 触发器,以处理单行和多行数据修改。Describes how to create a DML trigger to handle both single-row and multi-row data modifications. 创建 DML 触发器以处理多行数据Create DML Triggers to Handle Multiple Rows of Data
说明如何嵌套触发器。Describes how to nest triggers. 创建嵌套触发器Create Nested Triggers
说明如何指定激发 AFTER 触发器的顺序。Describes how to specify the order in which AFTER triggers are fired. 指定第一个和最后一个触发器Specify First and Last Triggers
说明如何在触发器代码中使用特殊的插入和删除表。Describes how to use the special inserted and delete tables in trigger code. 使用插入的和删除的表Use the inserted and deleted Tables
说明如何修改或重命名 DML 触发器。Describes how to modify or rename a DML trigger. 修改或重命名 DML 触发器Modify or Rename DML Triggers
说明如何查看有关 DML 触发器的信息。Describes how to view information about DML triggers. 获取有关 DML 触发器的信息Get Information About DML Triggers
说明如何删除或禁用 DML 触发器。Describes how to delete or disable DML triggers. 删除或禁用 DML 触发器Delete or Disable DML Triggers
说明如何管理触发器安全性。Describes how to manage trigger security. 管理触发器安全性Manage Trigger Security

另请参阅See Also

CREATE TRIGGER (Transact-SQL) CREATE TRIGGER (Transact-SQL)
ALTER TRIGGER (Transact-SQL) ALTER TRIGGER (Transact-SQL)
DROP TRIGGER (Transact-SQL) DROP TRIGGER (Transact-SQL)
DISABLE TRIGGER (Transact-SQL) DISABLE TRIGGER (Transact-SQL)
触发器函数 (Transact-SQL)Trigger Functions (Transact-SQL)