I need a help, I have to make a login in the MSSQL the company of the ERP and my superior needs to monitor the transactions of type INSERT , DELETE and UPDATE that are made by them, in case the scripts that were run on these types of transactions.
Examples:
INSERT INTO tabela001 (id, campo1, campo2) Values (1,'Texto1', 123 )
In Log I would save the login data, the transaction type and the executed script.
I just answered a similar question here / a>. You can work with triggers . Each table in the database will have a trigger for a certain operation (INSERT, DELETE AND UPDATE). Through this it is possible to get the information you want and store it in a table (suggestion).
Follow the example of a firebird trigger:
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