I have a table to audit: Tabla
Tabla
ID, col1, col2, col3, col4... col500
If you make update
in Tabla
my trigger is triggered which inserts in the table Tabla_audit
Tabla_audit
ID, ColmunaAfectada, ValorAnterior, NuevoValor
This is how it works:
USE [DB]
GO
ALTER TRIGGER [dbo].[Tabla_Audit_trigger] on [dbo].[Tabla]
AFTER update
AS
BEGIN
IF EXISTS
(
SELECT 1 FROM DELETED d INNER JOIN INSERTED i ON i.ID=d.ID AND ISNULL(i.col1,'') <> ISNULL(d.col1,'')
)
BEGIN
SET @HistoryFieldAltered = 'col1'
SET @HistoryFieldOriginalValue = (SELECT d.col1
FROM DELETED d
INNER JOIN INSERTED i ON i.col1 <> d.col1
WHERE i.ID=d.ID)
SET @HistoryFieldNewValue = (SELECT i.col1
FROM DELETED d
INNER JOIN INSERTED i ON i.col1 <> d.col1
WHERE i.ID=d.ID)
END
insert into dbo.IncidentsAudit
(ColmunaAfectada, ValorAnterior, NuevoValor)
select i.ID, @HistoryFieldAltered, @HistoryFieldOriginalValue, @HistoryFieldNewValue from dbo.Tabla t
inner join inserted i on t.ID=i.ID
END
This works for a single column and specifying the name of this: I want to detect the number of affected columns to put them in a cycle; also detect the name of the affected column and with that name dynamically call the inserted
and the deleted
to put them in Tabla_audit
.
I have a good time trying and not in finding the way, with dynamic queries the DELETED
or INSERTED
does not work