After Trigger in Microsoft SQL Server 2005 (CREATE and ALTER)

After giving quiet a good details about Triggers in my previous article, I would like to move further and want to give introduction about After Trigger in SQL Server.

AFTER Trigger was the only trigger before Microsoft SQL Server 2000 and it is useful as well. Table can contain more than one AFTER trigger. You can use AFTER trigger for recording data audit trails, complex business rule and for complex data validation.

AFTER trigger fire after all transaction gets complete respected to DML command but before COMMIT.

--create table for demo

CREATE TABLE empData

(

Name varchar(10),

Age INT

)

--create AFTER trigger on empData table,

--which will show message if Age > 100

--but won't stop inserting records.

CREATE TRIGGER empAgeCheck ON empData

AFTER INSERT

AS

SET NOCOUNT ON

DECLARE @Age INT

SELECT @Age=Age FROM INSERTED

IF (@Age>100)

BEGIN

print 'you can not do this'

END

GO

--try inserting records and see message

INSERT INTO empData VALUES('TestName',101)

--if you want to abort the batch

--if it break the rule than ALTER your TRIGGER

--and add rollback in it

ALTER TRIGGER empAgeCheck ON empData

AFTER INSERT

AS

SET NOCOUNT ON

DECLARE @Age INT

SELECT @Age=Age FROM INSERTED

IF (@Age>100)

BEGIN

print 'you can not do this'

ROLLBACK

END

GO

--try inserting records and see message

INSERT INTO empData VALUES('TestName',101)

--finally check your data in empData table.

select * from empData

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com