Monday, February 18, 2008

MS-SQL Server 2000 - Triggers

A trigger is a database object that is attached to a table. In many aspects it is similar to a stored
procedure. As a matter of fact, triggers are often referred to as a "special kind of stored procedure." The main difference between a trigger and a stored procedure is that the former is attached to a table and is only fired when an INSERT, UPDATE or DELETE occurs. You specify the modification action(s) that fire the trigger when it is created.
The following shows how to create a trigger that displays the current system time when a row is inserted into the table to which it is attached.
SET NOCOUNT ON
CREATE TABLE Source (Sou_ID int IDENTITY, Sou_Desc varchar(10))
go
CREATE TRIGGER tr_Source_INSERT
ON Source
FOR INSERT
AS
PRINT GETDATE()
go
INSERT Source (Sou_Desc) VALUES ('Test 1')
-- Results --Apr 28 2001 9:56AM
________________________________________________________________________________________
AFTER Triggers
The type of trigger that gets executed automatically after the statement that triggered it completes is called an AFTER trigger. An AFTER trigger is a trigger that gets executed automatically before the transaction is committed or rolled back.

A table can have several AFTER triggers for each of the three triggering actions i.e., INSERT, DELETE and UPDATE. Using the below script, we shall create a trigger on the table User_Master for the INSERT triggering action.

CREATE TRIGGER trgInsert2
ON User_Master
FOR INSERT
AS
BEGIN
Print ('AFTER Trigger [trgInsert2] – Trigger executed !!')
END
GO