Re: SQL Trigger Update Delete 1

[SMA00]

Hi

we have Inventory Trx table which does not maintain history of changes.

I would like to record it using Trigger on table INV90001 where if user changes any record or delete i want to record changes in New table called AUDIT900.

Can some one provide the code how to write trigger on table if some one update or delete record.

Thanks
Shaikh

 

[nomadicalloy]

CREATE TRIGGER [TriggerName] ON [DBNAME].[TableName]
FOR UPDATE
AS

DECLARE @USERID VARCHAR(50)

DECLARE @OLDVAL VARCHAR(50)

DECLARE @NEWVAL VARCHAR(100)

DECLARE @CHANGEBY VARCHAR(100)

DECLARE @CHANGEDATE DATETIME

-- TOOLBAR

IF UPDATE(FieldName)

BEGIN

SELECT @USERID = (SELECT USERID FROM DELETED)

SELECT @OLDVAL = ISNULL((SELECT FieldName FROM DELETED),'')

SELECT @NEWVAL = ISNULL((SELECT FieldName FROM INSERTED),'')

SELECT @CHANGEBY = (SELECT SYSTEM_USER)

SELECT @CHANGEDATE = (SELECT GETDATE() )

IF @OLDVAL <> @NEWVAL
BEGIN

INSERT INTO DBNAME.tContactHistory (COLNAME,USERID, OLDVAL, NEWVAL,CHANGEBY,DATECHANGE)
VALUES ('FieldName',@USERID, CAST(@OLDVAL AS VARCHAR(100)),CAST(@NEWVAL AS VARCHAR(100)),@CHANGEBY,@CHANGEDATE)

END

END

Please change the values of DBNAME, FieldName, Tablename

Thanks



SQL Help Blog

http://www.xpherion.com/blog.asp

 

[amrita418]

You should look into Books online on how to create triggers.

Just to get you started:

CREATE TRIGGER inv_upddel
ON INV90001
FOR Update, Delete
AS
Begin
INSERT INTO AUDIT900(Id, Name, ChangeDate)
SELECT Id, Name, ChangeDate FROM DELETED
END

If you want to kick the trigger of on update of specific columns only then you should be using something like

CREATE TRIGGER inv_upddel
ON INV90001
FOR Update
AS
if update(Id)
or update(Name)
Begin
INSERT INTO AUDIT900(Id, Name, ChangeDate)
SELECT Id, Name, ChangeDate FROM DELETED
END

deleted and inserted are logical (conceptual) tables. They are structurally similar to the table on which the trigger is defined, that is, the table on which the user action is attempted, and hold the old values or new values of the rows that may be changed by the user action.

Regards,
AA

 

[SMA00]

Hi Guys,

It works Great !!!! Thanks for your Tips & Code.

Regards
Shaikh