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
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