Understanding DML Triggers
Microsoft SQL Server 2005 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 two general types of triggers: DML triggers and DDL triggers.
DDL triggers are new to SQL Server 2005. These 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.
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.
See Also
Concepts
Types of DML Triggers
Triggers Compared to Constraints
DML Triggers in the AdventureWorks Sample Database