MSSQL Trigger on update or insert

Question

I am trying to create a trigger that will insert a record into a table2 whenever a record in table1 is inserted or updated. The idea is to create a history log (table2) of every "state" a record has been through in table1.

Thanks

That's great. Do you have an actual question or just sharing your project description with us? — Sean Lange
Check this out: - Quick And Easy Audit Tables - Dave Britten — SqlZim

VolkanCetinkaya VolkanCetinkaya · Accepted Answer · 2017-06-23T22:14:55

you can it but you must remember; you can not multi row update or insert.

this is a UPDATE trigger:

CREATE TRIGGER [dbo].[trVEHICLE_DEBUGLOG]
ON [dbo].[VEHICLE] AFTER UPDATE
AS
    DECLARE @ID BIGINT = (SELECT ID FROM INSERTED)

    DECLARE @NEWVALUE BIGINT = (SELECT VEHICLEGROUPID FROM INSERTED)
    DECLARE @OLDVALUE BIGINT = (SELECT VEHICLEGROUPID FROM DELETED WHERE ID = @ID)

    IF (@NEWVALUE <> @OLDVALUE) BEGIN
        INSERT INTO dbo.DEBUGLOG(DEBUGTABLE, DEBUGCOLUMN, DEBUGID, DEBUGOLDVALUE, DEBUGNEWVALUE, DEBUGDATETIME)
        VALUES
        ('VEHICLE','VEHICLEGROUPID', @ID, @OLDVALUE, @NEWVALUE, GETDATE())
    END

GO

MSSQL Trigger on update or insert

2 Answers