Wednesday, August 14, 2013

Create Trigger in SQL

Create Trigger in SQL : Suppose we have 2 tables. one is main table (AR_ServiceXUser ) and other table is backup of that table (AR_ServiceXUserAudit) if any data is update in main table then the old data of main table should transfer to backup table

CREATE TRIGGER tg_AR_ServiceXUser  --Name of Table
ON AR_ServiceXUser --Main Table
FOR UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

INSERT INTO [AR_ServiceXUserAudit] --Audit Table to take backup on any updation of data
([ServiceUserID]
,[ServiceID]
,[UserID]
,[DeliveryStatusID]
,[OrderDate]
,[TransactionID]
,[CenterID]
)


SELECT [ID]
,[ServiceID]
,[UserID]
,[DeliveryStatusID]
,[OrderDate]
,[TransactionID]
,[CenterID]
FROM DELETED


SET NOCOUNT OFF;
END

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.