How to create Trigger in SQL server ?

In this post you can see, how to create trigger in SQL server 2005/2008

ALTER TRIGGER t_XYZ_Update 
   ON  tbl_tableName1
   AFTER UPDATE
AS 
IF ( UPDATE (tableField1) OR UPDATE (tableField2) OR UPDATE (tableField3) OR UPDATE (tableField4))
    BEGIN
    SET NOCOUNT ON;
    
    DELETE FROM tbl_tableName2 WHERE FieldId=(select FieldId from inserted)
    print 'Row Deleted Successfully'
    END
GO

Name you trigger like below line

ALTER TRIGGER t_XYZ_Update

Assign the table name on which you have to perform operation like below line

ON  tbl_tableName1

Design when you want to allow this trigger to get fire, here let take after UPDATE operation.

AFTER UPDATE

If any of the tbl_tableName1 field like tableField1,tableField2,tableField3,tableField4 get updated outside by query or in SP, then this trigger will get fire and perform operation assign to this trigger, you can find when field get updated by below query,

 
IF ( UPDATE (tableField1) OR UPDATE (tableField2) OR UPDATE (tableField3) OR UPDATE (tableField4))

Here in below box, you can see the operation you want to perform when this trigger get fire, here we have used "inserted" table, whenever any of field found updated, same row is inserted in "inserted" table so by this table you can find which row is updated and you can make use of that rowid to perform other operation,

 
    BEGIN
    SET NOCOUNT ON;
    
    DELETE FROM tbl_tableName2 WHERE FieldId=(select FieldId from inserted)
    print 'Row Deleted Successfully'
    END

Hope this post will help you,
if yes please put comment below of this page,
Rajesh Singh,
Senior Asp.Net Developer
Email: raj143svmit@gmail.com
e-Procurement Technologies Ltd (India)
www.abcprocure.com