• Article

Understanding DML Triggers

Microsoft SQL Server provides two primary mechanisms for enforcing business rules and data integrity: constraints and triggers. A trigger is a special type of stored procedure that automatically takes effect when a language event executes. SQL Server includes three general types of triggers: DML triggers, DDL triggers, and logon triggers.

DDL triggers are invoked when a data definition language (DDL) event takes place in the server or database. They are explained in more detail in DDL Triggers. Logon triggers fire stored procedures in response to a LOGON event. This event is raised when a user session is established with an instance of SQL Server. For more information, see Logon Triggers.

DML triggers are invoked when a data manipulation language (DML) event takes place in the database. DML events include INSERT, UPDATE, or DELETE statements that modify data in a specified table or view. A DML trigger can query other tables and can include complex Transact-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 triggers are useful in these ways:

  • They can cascade changes through related tables in the database; however, these changes can be executed more efficiently using cascading referential integrity constraints.

  • 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.

    Unlike CHECK constraints, DML triggers can reference columns in other tables. 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.

  • They can evaluate the state of a table before and after a data modification and take actions based on that difference.

  • 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.