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