You can do this via auditing triggers which provide auditing data changes as answers to the following questions:
- Who changed the data?
- What was the date and time when a change occurred?
- Which client software was used to access the data?
- In case the request was data modification (the UPDATE statement), what was the data value prior and after the change?
It means that when a trigger is fired, all this info has to be collected from a database or SQL Server instance and stored in an auditing repository
For example, a trigger fired when a record is inserted into the Currency.Sales table is created using the following SQL
CREATE TRIGGER Sales.tr_i_AUDIT_Currency
NOT FOR REPLICATION
Then, the trigger must insert the computer name where INSERT was executed, the user name of the person who inserted the record and the name of the application used to insert the record. Triggers leverage built-in SQL Server functions to get these values:
- HOST_NAME () returns the workstation name
- APP_NAME()returns a name of the application in the current session
- SUSER_SNAME() – returns the login name
- GETDATE() – returns the system timestamp for the database currently connected to
These values are stored in a pre-defined table used as an auditing data repository
2, -- ACTION ID For INSERT
1 THEN ' '
1 THEN ' '