881
Chapter 36: Creating Triggers
36
- Primary-key constraint.
- Check constraints.
- Foreign-key constraints.
- DML execution and update to the transaction log.
- AFTER trigger execution.
- Commit transaction.
Based on SQL Server’s transaction fl ow, keep the following points in mind:
■ An AFTER trigger occurs after all constraints are enforced. If a constraint is vio-
lated, then AFTER triggers are not fi red.
■ An INSTEAD OF trigger can circumvent foreign-key violations but not nullability,
data-type, or identity-column violations.
■ The AFTER trigger occurs before the DML transaction is committed, so it can roll
back the transaction if the data is unacceptable.Creating Triggers
A trigger can be fi red for any combination of insert, update, or delete events. Triggers are
created and modifi ed with the standard DDL commands, CREATE, ALTER, and DROP, as
follows:CREATE TRIGGER Schema.TriggerName ON Schema.TableName
AFTER | INSTEAD OF [Insert, Update, (and or) Delete]
AS
Trigger Code;You can also create, view and modify triggers using Management Studio’s Object Explorer,
as shown in Figure 36-1.After Triggers
A table may have one or more AFTER triggers on each of the DML events. AFTER triggers
cannot be applied to views.AFTER triggers are useful for the following:■ Complex data validation or business rules■ (^) Writing data-audit trails
■ Maintaining modifi cation tracking columns
■ (^) Enforcing custom referential-integrity or cascading actions
c36.indd 881c36.indd 881 7/31/2012 10:03:07 AM7/31/2012 10:03:07 AM
http://www.it-ebooks.info