SQL command or trigger needed to capture changes in tables [duplicate]

Posted on

Question :

I want a query or trigger that will identify what changes done in the database tables from the Third Party Software such CMS etc.
I want to check that which data is modifying with time.
There is no log maintained in that Third Party Software so i want to check it thorugh SQL Server

Answer :

There are thousands of examples you can search .. one approach of a trigger that logs deletes/updates in a table.

You must first create a LOG table with the the same schema of the source table and add 3 columns – type(char1), dateLog datetime, UserLog(varchar(50)

CREATE TRIGGER logTable
ON [dbo].[table] 
AFTER DELETE, UPDATE
AS 
BEGIN
    INSERT INTO log_table (<column list here>)
     SELECT del.*
        , CASE WHEN 
            EXISTS (
                select * 
                from inserted
                ) 
            AND EXISTS (
                select * 
                from deleted
                ) THEN 'U' ELSE 'D' END
        , getdate()
        , SYSTEM_USER
    FROM deleted del;
END

GO

Leave a Reply

Your email address will not be published. Required fields are marked *