How to audit the name of the affected column, the previous value and the new value with a trigger dynamically?

0

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

    
asked by Hugo Felix 30.08.2018 в 02:11
source

0 answers