DML Triggers execute in response to any
data modifications so its affect the transactional state in which they're
fired. Knowing these effects can prevent conflicts with constraints, locking,
and blocking on the affected tables.
So when a
modification occurs, every transaction invokes various checks in the following
order:
1.
IDENTITY INSERT check.
2.
Nullability constraint.
3.
Data-type check.
4.
The statement is executed (INSERT, UPDATE, or
DELETE).
5.
If an INSTEAD OF trigger is defined on the table,
it executes in place of the actual modification.
Execution
of DML will stop here.
6.
Any constraints on the table are checked. If
constraints are violated, an error is returned and the process is stopped.
Like, Primary-key
constraint, Check constraints, Foreign-key constraints and many more.
7.
DML execution and update to the transaction log.
8.
AFTER trigger execution:
The first
and last trigger to fire can be specified; the rest of the AFTER triggers
execute in an undefined order.
If a
rollback transaction is encountered, all modifications must be rolled back.
9.
The transaction is committed.
A trigger
executes once per INSERT, UPDATE, or DELETE statement even if multiple rows are
modified. It is common for UPDATE and DELETE statements to affect multiple
rows, but this is also possible with INSERT statements.
The inserted and deleted tables contain a copy
of all affected rows.
No comments:
Post a Comment