Database Reference
In-Depth Information
Chapter 8
Triggers
Triggers define the code that runs in response to specific events. There are three types of triggers available in
SQL Server:
1.
DML triggers fire when data modification occurs. You can use DML triggers in cases
when you need to enforce specific business rules during data modifications, and the
system does not have a dedicated data-access tier implemented. You can think about
audit-trail functional, which captures who changed the data in the table as an example.
When a system has multiple applications working with the database directly, audit-trail
implementation based on triggers is the simplest one.
2.
DDL triggers fire in response to events that change database and server objects. You can
use DDL triggers to prevent or audit those changes, for example, dropping tables, altering
stored procedures, or creating new logins.
3.
Logon triggers fire during the user login process. You can use triggers for audit purposes as
well as to prevent users from logging in to the system when needed.
DML Triggers
DML triggers allow you to define the code that will be executed during data modification operations, such as insert,
update, or delete. There are two types of DML triggers: INSTEAD OF and AFTER triggers. INSTEAD OF triggers run as a
replacement
of the actual data modification operation on a table or view. With these types of triggers, you can evaluate
and/or implement business rules. You also need to issue the actual DML statement against a table if you want the data to
be modified. AFTER triggers fire following a data modification operation, when the data in the table has been changed.
Let's see what happens when we insert the data into a table that has triggers and constraints defined. First let's
create the table using the code shown in Listing 8-1.
Listing 8-1.
Inserting data into the table: Table and two triggers creation
create table dbo.OrderLineItems
(
OrderId int not null,
OrderLineItemId int identity(1,1) not null,
ProductId int not null,
ProductName nvarchar(64) not null,
CreationDate smalldatetime not null,
constraint DEF_OrderLineItems_CreationDate
default GetUtcDate(),
