||SQL Server Tips by Gama and Naughter
Triggers are objects bound to tables; they contain TSQL statements
and will execute for each row modified. INSERT, UPDATE, or DELETE
statements will cause a trigger to execute.
There are two types of triggers in SQL Server: AFTER and INSTEAD OF.
This type of trigger will execute after the operation that set it
off is completed. The trigger will only execute after all the
referential cascade actions and constraint checks reached
AFTER triggers are the type used by previous versions of SQL Server
while INSTEAD OF appeared with SQL Server 2000. This is something to
be considered when updating a database and reusing code.
Example: create a trigger that will accept inserting data only on
the first five days of the month, in table jobs from database Pubs.
CREATE TRIGGER "jobs_ITrig" ON jobs FOR INSERT AS
SET NOCOUNT ON
/* * Prevent INSERT if not within the first 5 days of the month */
RAISERROR 44447 'The record can''t be added. Insertion is
restricted to first 5 days of the week.'
Trying to add a new record (assuming a day of the month above five):
INSERT jobs(job_desc, min_lvl, max_lvl) VALUES( 'Trainee', 11, 12)
Will result on an error:
Server: Msg 44447, Level 16, State 1, Procedure jobs_ITrig, Line 6
The record can't be added. Insertion is
restricted to first 5 days of the week.
The above book excerpt is from:
Turbocharge Database Performance with C++ External Procedures
Joseph Gama, P. J. Naughter