Login transaction log MSSQL 2012

Question

I need a help, I have to provide a login on MSSQL ERP company and my superior need to monitor transactions of type INSERT, DELETE and UPDATE that are made by them, in case the scripts that were executed in those types of transactions.

Examples :

INSERT INTO tabela001 (id, campo1, campo2) Values (1,'Texto1', 123 )

In the Log I would save the login data, transaction type and executed script.

Answer 1

I just answered a similar question here. You can work with triggers. Each table in the database will have a Trigger for a certain operation (INSERT, DELETE AND UPDATE). Through this you can get the information you want and store in a table (suggestion).

Follow the example of a Trigger from Firebird:

CREATE TRIGGER au FOR table
  AFTER UPDATE
  POSITION 0
AS
BEGIN
  IF RDB$GET_CONTEXT('USER_TRANSACTION', 'MY_LOCK') IS NULL THEN
  BEGIN
    RDB$SET_CONTEXT('USER_TRANSACTION', 'MY_LOCK', 1);

    ...
    Do your update operations here
    ...

    RDB$SET_CONTEXT('USER_TRANSACTION', 'MY_LOCK', NULL);
  END

  WHEN ANY DO
  BEGIN
    RDB$SET_CONTEXT('USER_TRANSACTION', 'MY_LOCK', NULL);
    EXCEPTION;
  END
END