Implementing Triggers in SQL Server 2000

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.

Using the below script, first we shall create a trigger on the table USER_MASTER for the INSERT event of the table.

USE KDMNN

Go

CREATE TRIGGER trgInsert

ON User_Master

FOR INSERT

AS

      Print ('AFTER Trigger [trgInsert]  – Trigger executed !!')

GO

BEGIN TRANSACTION

DECLARE @ERR INT

INSERT INTO USER_MASTER(USERNAME, PASSWORD)

VALUES('Damerla','Damerla')

SET @ERR = @@Error

IF @ERR = 0   

             BEGIN

                           ROLLBACK TRANSACTION

                           PRINT 'ROLLBACK TRANSACTION'

             END

ELSE

             BEGIN

                           COMMIT TRANSACTION

                           PRINT 'COMMIT TRANSACTION'

             END

Output

AFTER Trigger [trgInsert]  – Trigger executed !!

(1 row(s) affected)

ROLLBACK TRANSACTION

By looking at the output, we can conclude that before the transaction is rolled back or committed, the AFTER trigger gets executed automatically. 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 two triggers 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

CREATE TRIGGER trgInsert3

ON User_Master

FOR INSERT

AS

BEGIN

     Print ('AFTER Trigger [trgInsert3]  – Trigger executed !!')

END

GO

BEGIN TRANSACTION

DECLARE @ERR INT

INSERT INTO USER_MASTER(USERNAME, PASSWORD)

VALUES('Damerla','Damerla')

SET @ERR = @@Error

IF @ERR = 0   

             BEGIN

                           ROLLBACK TRANSACTION

                           PRINT 'ROLLBACK TRANSACTION'

             END

ELSE

             BEGIN

                           COMMIT TRANSACTION

                           PRINT 'COMMIT TRANSACTION'

             END

Output

AFTER Trigger [trgInsert]  – Trigger executed !!

AFTER Trigger [trgInsert2]  – Trigger executed !!

AFTER Trigger [trgInsert3]  – Trigger executed !!

(1 row(s) affected)

ROLLBACK TRANSACTION