I need to make a trigger, that I insert in an audit table, the name of the table, the date and the type of action that is performed, this trigger must be executed at the database level after an UPDATE, INSERT or DELETE of any of the tables in the BD. So far do this:
SELECT top 1 s.name, s.type_desc, s.modify_date
FROM sys.objects s
where s.type like 'U'
ORDER BY S.modify_date DESC
But he does not tell me what kind of modification was made. Can you give me a hand?
In SQL Server, there is no trigger that executes globally for all DML operations, so you must write the triggers for each of the tables. I'm going to make an example by writing a trigger for each operation, and only to mention that you could make a single trigger for all 3, but in that case, you should determine if it's a insert , update , or delete
Assuming you have a blog table like this:
create table BitacoraOperaciones (
TableName sysname
, Operacion varchar(50)
, Fecha datetime
);
And a table called Factura of which you want to register the log, the triggers would be like this:
create trigger trFacturaIns
on Factura
after insert
as
insert into BitacoraOperaciones (TableName, Operacion, Fecha) values ('Factura', 'insert', getdate());
go
create trigger trFacturaUpd
on Factura
after update
as
insert into BitacoraOperaciones (TableName, Operacion, Fecha) values ('Factura', 'update', getdate());
go
create trigger trFacturaIns
on Factura
after delete
as
insert into BitacoraOperaciones (TableName, Operacion, Fecha) values ('Factura', 'delete', getdate());
go
From this moment on, each operation insert , update or delete that is made on the invoice table, will leave a record in the log table.
Since you must repeat this process for all the tables in the database (except the log itself).
inserted and deleted to determine the operation in question. Since the structure of the trigger is the same for all the tables, you could use the data dictionary to generate an SQL script from a query to the table sys.Tables , which returns all the tables created by you, something like:
select s.name schema_name, t.name table_name
from sys.tables t
inner join sys.schemas s on s.schema_id = t.schema_id
where t.type = 'U'
and t.name != 'BitacoraOperaciones'