Tuning the Schema - Professional SQL Server 2005 Performance Tuning - page 218

Thus, you can prevent SQL Server from unnecessarily checking the master database simply by using a
different prefix for your stored procedure names.

Triggers
SQL Server supports two different types of triggers. They are Data Manipulation Language triggers,
and Data Definition Language triggers. Triggers are special types of stored procedures. They execute
whenever certain actions occur against a table. They also include two special structures known as the
pseudo-tables ''inserted'' and ''deleted.''

DML
A DML trigger executes whenever a DML event occurs. These events are: UPDATE , INSERT ,or DELETE
statements. This type of trigger is used to enforce rules whenever data is modified. Another purpose of
this type of trigger is to extend integrity enforcement.

There are two types of DML triggers. They are: AFTER triggers and INSTEAD OF triggers.

AFTER
This type of trigger occurs after the specified event (Insert, Update, or Delete) occurs. Using the Billing
table example, here's an AFTER trigger defined on the Billing table for UPDATE. This trigger will fill in
the UpdateUID, and UpdateTS columns for rows in the Billing tables which get updated.

create trigger UpdateAuditCols on Billing for UPDATE as
begin

Update T
SET UpdateUID = SUSER_SNAME(),

UpdateTS = GETUTCDATE()

FROM BillingCase T

INNER JOIN inserted i ON T.BillingID = i.BillingID

end

go

INSTEAD OF
INSTEAD OF triggers execute in place of the event that originally initiated the operation. For example,
define an INSTEAD OF trigger on a table for the Delete event. Next, try to delete some rows. The
INSTEAD OF trigger will prevent the rows from getting deleted. Again, using the Billing table as an
example consider the following INSTEAD OF trigger:

create trigger DeleteAuditCols on Billing INSTEAD OF DELETE as
begin
Update T
SET DeleteUID = SUSER_SNAME(),

DeleteTS = GETUTCDATE()

FROM BillingCase T

INNER JOIN deleted d ON T.BillingID = d.BillingID

end

go

This trigger will prevent the rows from actually being deleted from the Billing table. However, it will
mark those rows with deleted information.