Trigger Help Please

[shiggyshag]

HI

I think Triggers are what I need but not sure how to use them in the following case.

I have a table called tContacts, now when any field on that tabel is changed I would like to add that into tContactHistory table so I get the ContactID, ColumnName, oldValue, NewValue is this possible.

Any help would be great

Cheers

 

[johnherman]

Something like:

CREATE TRIGGER CONTACT_HIST_INS_UPD ON tContacts FOR INSERT, UPDATE AS

INSERT tContactHistory VALUES (ContactID, ColumnName, oldValue, NewValue, date/time of change)

-------------------------
The reasonable man adapts himself to the world. The unreasonable one persists in trying to adapt the world to himself. Therefore all progress depends on the unreasonable man. - George Bernard Shaw

 

[nomadicalloy]

This is just an example :

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