Audit Log Not Updating

Question

Any updates done to my table I would like them to be logged. So I decided to search it up and came across creating Database triggers. I followed a tutorial and I was successfully able to log any ALTERS done to tables but any UPDATES done aren't being logged. Followed this Tutorial: https://jackworthen.com/2018/03/19/creating-a-log-table-to-track-changes-to-database-objects-in-sql-server/

Query:

CREATE TRIGGER [backup_objects]
ON DATABASE
FOR CREATE_PROCEDURE, 
    ALTER_PROCEDURE, 
    DROP_PROCEDURE,
    CREATE_TABLE, 
    ALTER_TABLE, 
    DROP_TABLE,
    CREATE_FUNCTION, 
    ALTER_FUNCTION, 
    DROP_FUNCTION,
    CREATE_VIEW,
    ALTER_VIEW,
    DROP_VIEW
AS

SET NOCOUNT ON

DECLARE @data XML
SET @data = EVENTDATA()

INSERT INTO [dbo].UserChangelog(databasename, eventtype, 
    objectname, objecttype, sqlcommand, loginname)
VALUES(
@data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'),
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'), 
@data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'), 
@data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)'), 
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'), 
@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)')
)


GO

I thought the trigger was missing, "UPDATE_TABLE" but that unfortunately doesn't even exist.

Anyone got an idea of what I might have to add to my trigger? I technically don't need it to be focused on the entire Database but that's just a bonus.

What information are you looking for on the UPDATE? Usually an audit on a table for UPDATES is looking for the changes made to the data but your columns don't seem to communicate this. — HereGoes, Jul 29 '19 at 11:47

score 0 · Answer 1 · answered Jul 26 '19 at 18:27

0

Try...

CREATE TRIGGER [backup_objects]
   ON on_your_table AFTER INSERT, DELETE, UPDATE AS ....

answered Jul 26 '19 at 18:27

HereGoes

1,302
1
9
14

Not working. After doing that all my columns are coming out as NULLS. – Jona Jul 26 '19 at 18:57
Could you please update your question with your revised code? – HereGoes Jul 29 '19 at 14:37
I was able to get it working but using the following: https://stackoverflow.com/questions/19737723/log-record-changes-in-sql-server-in-an-audit-table – Jona Jul 29 '19 at 15:26

Audit Log Not Updating

1 Answers1