907
Chapter 36: Creating Triggers
36
Summary
DDL triggers provide a safety net — a way to track every change to the schema. The event
model that can be tracked is comprehensive, and the data available to the trigger using the
EventData() function and XML is dynamic and complete. Because DDL triggers can fi re
for such a broad range of events, the EventData() function returns XML data about the
event.DDL triggers are well suited to the following scenarios:■ (^) Server-level DDL triggers can trap any event and are seen in Object Explorer under
the Server Objects ➪ Triggers node.
■ (^) Database-level DDL triggers exist in the user database, can only fi re for database-
level events, and are listed in Object Explorer in the [Database] ➪ Programmability
➪ Database Triggers node.
■ (^) DDL Triggers can fi re for any specifi c DDL event, or for DDL Event Groups — a hier-
archy of DDL events.
Triggers are a key feature of client/server databases. It is the trigger that enables the
developer to create complex custom business rules that are strongly enforced at the
Database Engine level. SQL Server has two types of triggers: INSTEAD OF triggers and
AFTER triggers.
■ (^) Triggers enable the developer to create complex custom business rules that are
strongly enforced at the Database Engine level. SQL Server has two types of DML
triggers: INSTEAD OF triggers and AFTER triggers.
■ (^) INSTEAD OF triggers cancel the fi ring DML statement and do something else
instead of the original DML statement.
■ (^) Triggers extend the lock duration, so try to place the code in the abstraction layer
before it goes into the trigger.
■ (^) Triggers fi re once per DML statement, not once per row, so be certain the trigger is
set-based and can handle multiple rows well.
■ (^) Use the inserted and deleted virtual tables to access the data being modifi ed by the
DML statement.
Trigger logic can easily become complex and cause signifi cant performance issues if care is
not taken to avoid this. This type of system is expensive to maintain or refactor.
c36.indd 907c36.indd 907 7/31/2012 10:03:10 AM7/31/2012 10:03:10 AM
http://www.it-ebooks.info