trigger - Registrar cambios de registro en el servidor SQL en una tabla de auditoría

tipos de auditoria sql server

Question

La mesa :

    CREATE TABLE GUESTS (
      GUEST_ID int IDENTITY(1,1) PRIMARY KEY, 
      GUEST_NAME VARCHAR(50), 
      GUEST_SURNAME VARCHAR(50), 
      ADRESS VARCHAR(100), 
      CITY VARCHAR(50), 
      CITY_CODE VARCHAR(10), 
      COUNTRY VARCHAR(50), 
      STATUS VARCHAR(20), 
      COMMENT nvarchar(max);

Para el registro:

CREATE TABLE AUDIT_GUESTS (
  ID int IDENTITY(1,1) PRIMARY KEY, 
  GUEST_ID int,
  OLD_GUEST_NAME VARCHAR(50), 
  NEW_GUEST_NAME VARCHAR(50), 
  OLD_GUEST_SURNAME VARCHAR(50), 
  NEW_GUEST_SURNAME VARCHAR(50),
  OLD_ADRESS VARCHAR(100), 
  NEW_ADRESS VARCHAR(100),
  OLD_CITY VARCHAR(50), 
  NEW_CITY VARCHAR(50),
  OLD_CITY_CODE VARCHAR(10), 
  NEW_CITY_CODE VARCHAR(10), 
  OLD_COUNTRY VARCHAR(50), 
  NEW_COUNTRY VARCHAR(50), 
  OLD_STATUS VARCHAR(20), 
  NEW_STATUS VARCHAR(20), 
  OLD_COMMENT nvarchar(max), 
  NEW_COMMENT nvarchar(max), 
  AUDIT_ACTION varchar(100),
  AUDIT_TIMESTAMP datetime);

Me gustaría crear un desencadenante en mi tabla GUESTS para registrar todos los cambios en mi tabla AUDIT_GUESTS . ¿Cómo puedo hacer eso en SQL Server 2014 Express?

Lo intenté :

create TRIGGER trgAfterUpdate ON [dbo].[GUESTS] 
FOR UPDATE
AS
    declare @GUEST_ID int;
    declare @GUEST_NAME varchar(50);
    declare @GUEST_SURNAME VARCHAR(50);
    declare @ADRESS VARCHAR(100); 
    declare @CITY VARCHAR(50);
    declare @CITY_CODE VARCHAR(10); 
    declare @COUNTRY VARCHAR(50);
    declare @STATUS VARCHAR(20);
    declare @COMMENT nvarchar(max);
    declare @AUDIT_ACTION varchar(100);
    declare @AUDIT_TIMESTAMP datetime;

    select @GUEST_ID=i.GUEST_ID from inserted i;            
    select @GUEST_NAME=i.GUEST_NAME from inserted i;    
    select @GUEST_SURNAME=i.GUEST_SURNAME from inserted i;
    select @ADRESS=i.ADRESS from inserted i;
    select @CITY=i.CITY from inserted i;
    select @CITY_CODE=i.CITY_CODE from inserted i;
    select @COUNTRY=i.COUNTRY from inserted i;
    select @STATUS=i.STATUS from inserted i;
    select @COMMENT=i.COMMENT from inserted i;

        if update(GUEST_NAME)
        set @audit_action=''Updated Record -- After Update Trigger.'';

        if update(GUEST_SURNAME)
        set @audit_action=''Updated Record -- After Update Trigger.'';

        if update(ADRESS)
        set @audit_action=''Updated Record -- After Update Trigger.'';

        if update(CITY)
        set @audit_action=''Updated Record -- After Update Trigger.'';

        if update(CITY_CODE)
        set @audit_action=''Updated Record -- After Update Trigger.'';

        if update(COUNTRY)
        set @audit_action=''Updated Record -- After Update Trigger.'';

        if update(STATUS)
        set @audit_action=''Updated Record -- After Update Trigger.'';

        if update(COMMENT)
        set @audit_action=''Updated Record -- After Update Trigger.'';

        insert into AUDIT_GUESTS
           (GUEST_ID,GUEST_NAME,GUEST_SURNAME,ADRESS,CITY,CITY_CODE,COUNTRY,STATUS,COMMENT,audit_action,AUDIT_TIMESTAMP) 
    values(@GUEST_ID,@GUEST_NAME,@GUEST_SURNAME,@ADRESS,@CITY,@CITY_CODE,@COUNTRY,@STATUS,@COMMENT,@audit_action,getdate());
    GO

Funciona bastante bien, pero me gustaría ver valores nuevos y antiguos.

En SQLite tuve:

CREATE TRIGGER [LOG_UPDATE]
AFTER UPDATE OF [GUEST_NAME], [GUEST_SURNAME], [ADRESS], [CITY], [CITY_CODE], [COUNTRY], [STATUS], [COMMENT]
ON [GUESTS]
BEGIN
INSERT INTO GUESTS_LOG
 ( GUEST_ID,
   NAME_OLD,NAME_NEW,
   SURNAME_OLD,SURNAME_NEW,
   ADRESS_OLD,ADRESS_NEW,
   CITY_OLD,CITY_NEW,
   CITY_CODE_OLD,CITY_CODE_NEW,
   COUNTRY_OLD,COUNTRY_NEW,
   STATUS_OLD,STATUS_NEW,   
   COMMENT_OLD,COMMENT_NEW,sqlAction,DATE_TIME)   

   VALUES   

 (OLD.GUEST_ID,
  OLD.GUEST_NAME,NEW.GUEST_NAME, 
  OLD.GUEST_SURNAME,NEW.GUEST_SURNAME,
  OLD.ADRESS,NEW.ADRESS,
  OLD.CITY,NEW.CITY,
  OLD.CITY_CODE,NEW.CITY_CODE,
  OLD.COUNTRY,NEW.COUNTRY,  
  OLD.STATUS,NEW.STATUS,
  OLD.COMMENT,NEW.COMMENT,''record changed'',datetime(''now'',''localtime''));  

END

y funcionó bien. Simplemente no sé cómo pasar esto al servidor SQL. Acabo de empezar a aprenderlo.

Answer 1

Echa un vistazo a este artículo en Simple-talk.com por Pop Rivett . Lo guía a través de la creación de un disparador genérico que registrará OLDVALUE, NEWVALUE para todas las columnas actualizadas. El código es muy genérico y puede aplicarlo a cualquier tabla que quiera auditar, también para cualquier operación CRUD, es decir, INSERTAR, ACTUALIZAR y BORRAR. El único requisito es que su tabla a ser auditada debe tener una CLAVE PRIMARIA (que las tablas mejor diseñadas deberían tener de todos modos).

Aquí está el código relevante para su tabla de INVITADOS.

1) Crear tabla AUDIT.

IF NOT EXISTS
      (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N''[dbo].[Audit]'') 
               AND OBJECTPROPERTY(id, N''IsUserTable'') = 1)
       CREATE TABLE Audit 
               (Type CHAR(1), 
               TableName VARCHAR(128), 
               PK VARCHAR(1000), 
               FieldName VARCHAR(128), 
               OldValue VARCHAR(1000), 
               NewValue VARCHAR(1000), 
               UpdateDate datetime, 
               UserName VARCHAR(128))
GO

2) CREAR un desencadenante de ACTUALIZACIÓN en la tabla de INVITADOS de la siguiente manera.

CREATE TRIGGER TR_GUESTS_AUDIT ON GUESTS FOR UPDATE
AS

DECLARE @bit INT ,
       @field INT ,
       @maxfield INT ,
       @char INT ,
       @fieldname VARCHAR(128) ,
       @TableName VARCHAR(128) ,
       @PKCols VARCHAR(1000) ,
       @sql VARCHAR(2000), 
       @UpdateDate VARCHAR(21) ,
       @UserName VARCHAR(128) ,
       @Type CHAR(1) ,
       @PKSelect VARCHAR(1000)


--You will need to change @TableName to match the table to be audited. 
-- Here we made GUESTS for your example.
SELECT @TableName = ''GUESTS''

-- date and user
SELECT         @UserName = SYSTEM_USER ,
       @UpdateDate = CONVERT (NVARCHAR(30),GETDATE(),126)

-- Action
IF EXISTS (SELECT * FROM inserted)
       IF EXISTS (SELECT * FROM deleted)
               SELECT @Type = ''U''
       ELSE
               SELECT @Type = ''I''
ELSE
       SELECT @Type = ''D''

-- get list of columns
SELECT * INTO #ins FROM inserted
SELECT * INTO #del FROM deleted

-- Get primary key columns for full outer join
SELECT @PKCols = COALESCE(@PKCols + '' and'', '' on'') 
               + '' i.'' + c.COLUMN_NAME + '' = d.'' + c.COLUMN_NAME
       FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,

              INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
       WHERE   pk.TABLE_NAME = @TableName
       AND     CONSTRAINT_TYPE = ''PRIMARY KEY''
       AND     c.TABLE_NAME = pk.TABLE_NAME
       AND     c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

-- Get primary key select for insert
SELECT @PKSelect = COALESCE(@PKSelect+''+'','''') 
       + ''''''<'' + COLUMN_NAME 
       + ''=''''+convert(varchar(100),
coalesce(i.'' + COLUMN_NAME +'',d.'' + COLUMN_NAME + ''))+''''>'''''' 
       FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
               INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
       WHERE   pk.TABLE_NAME = @TableName
       AND     CONSTRAINT_TYPE = ''PRIMARY KEY''
       AND     c.TABLE_NAME = pk.TABLE_NAME
       AND     c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

IF @PKCols IS NULL
BEGIN
       RAISERROR(''no PK on table %s'', 16, -1, @TableName)
       RETURN
END

SELECT         @field = 0, 
       @maxfield = MAX(ORDINAL_POSITION) 
       FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName
WHILE @field < @maxfield
BEGIN
       SELECT @field = MIN(ORDINAL_POSITION) 
               FROM INFORMATION_SCHEMA.COLUMNS 
               WHERE TABLE_NAME = @TableName 
               AND ORDINAL_POSITION > @field
       SELECT @bit = (@field - 1 )% 8 + 1
       SELECT @bit = POWER(2,@bit - 1)
       SELECT @char = ((@field - 1) / 8) + 1
       IF SUBSTRING(COLUMNS_UPDATED(),@char, 1) & @bit > 0
                                       OR @Type IN (''I'',''D'')
       BEGIN
               SELECT @fieldname = COLUMN_NAME 
                       FROM INFORMATION_SCHEMA.COLUMNS 
                       WHERE TABLE_NAME = @TableName 
                       AND ORDINAL_POSITION = @field
               SELECT @sql = ''
insert Audit (    Type, 
               TableName, 
               PK, 
               FieldName, 
               OldValue, 
               NewValue, 
               UpdateDate, 
               UserName)
select '''''' + @Type + '''''','''''' 
       + @TableName + '''''','' + @PKSelect
       + '','''''' + @fieldname + ''''''''
       + '',convert(varchar(1000),d.'' + @fieldname + '')''
       + '',convert(varchar(1000),i.'' + @fieldname + '')''
       + '','''''' + @UpdateDate + ''''''''
       + '','''''' + @UserName + ''''''''
       + '' from #ins i full outer join #del d''
       + @PKCols
       + '' where i.'' + @fieldname + '' <> d.'' + @fieldname 
       + '' or (i.'' + @fieldname + '' is null and  d.''
                                + @fieldname
                                + '' is not null)'' 
       + '' or (i.'' + @fieldname + '' is not null and  d.'' 
                                + @fieldname
                                + '' is null)'' 
               EXEC (@sql)
       END
END

GO

Answer 2

Este es el código con dos correcciones de errores. La primera corrección de errores fue mencionada por Royi Namir en el comentario sobre la respuesta aceptada a esta pregunta. El error se describe en en Error en Trigger Code . El segundo fue encontrado por @ Fandango68 y corrige columnas con múltiples palabras para sus nombres.

ALTER TRIGGER [dbo].[TR_person_AUDIT]
ON [dbo].[person]
FOR UPDATE
AS
           DECLARE @bit            INT,
                   @field          INT,
                   @maxfield       INT,
                   @char           INT,
                   @fieldname      VARCHAR(128),
                   @TableName      VARCHAR(128),
                   @PKCols         VARCHAR(1000),
                   @sql            VARCHAR(2000),
                   @UpdateDate     VARCHAR(21),
                   @UserName       VARCHAR(128),
                   @Type           CHAR(1),
                   @PKSelect       VARCHAR(1000)


           --You will need to change @TableName to match the table to be audited.
           -- Here we made GUESTS for your example.
           SELECT @TableName = ''PERSON''

           SELECT @UserName = SYSTEM_USER,
                  @UpdateDate = CONVERT(NVARCHAR(30), GETDATE(), 126)

           -- Action
           IF EXISTS (
                  SELECT *
                  FROM   INSERTED
              )
               IF EXISTS (
                      SELECT *
                      FROM   DELETED
                  )
                   SELECT @Type = ''U''
               ELSE
                   SELECT @Type = ''I''
           ELSE
               SELECT @Type = ''D''

           -- get list of columns
           SELECT * INTO #ins
           FROM   INSERTED

           SELECT * INTO #del
           FROM   DELETED

           -- Get primary key columns for full outer join
           SELECT @PKCols = COALESCE(@PKCols + '' and'', '' on'') 
                  + '' i.['' + c.COLUMN_NAME + ''] = d.['' + c.COLUMN_NAME + '']''
           FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk,
                  INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
           WHERE  pk.TABLE_NAME = @TableName
                  AND CONSTRAINT_TYPE = ''PRIMARY KEY''
                  AND c.TABLE_NAME = pk.TABLE_NAME
                  AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

           -- Get primary key select for insert
           SELECT @PKSelect = COALESCE(@PKSelect + ''+'', '''') 
                  + ''''''<['' + COLUMN_NAME 
                  + '']=''''+convert(varchar(100),
           coalesce(i.['' + COLUMN_NAME + ''],d.['' + COLUMN_NAME + '']))+''''>''''''
           FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk,
                  INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
           WHERE  pk.TABLE_NAME = @TableName
                  AND CONSTRAINT_TYPE = ''PRIMARY KEY''
                  AND c.TABLE_NAME = pk.TABLE_NAME
                  AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

           IF @PKCols IS NULL
           BEGIN
               RAISERROR(''no PK on table %s'', 16, -1, @TableName)

               RETURN
           END

           SELECT @field = 0,
                  -- @maxfield = MAX(COLUMN_NAME) 
                  @maxfield = -- FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName


                  MAX(
                      COLUMNPROPERTY(
                          OBJECT_ID(TABLE_SCHEMA + ''.'' + @TableName),
                          COLUMN_NAME,
                          ''ColumnID''
                      )
                  )
           FROM   INFORMATION_SCHEMA.COLUMNS
           WHERE  TABLE_NAME = @TableName






           WHILE @field < @maxfield
           BEGIN
               SELECT @field = MIN(
                          COLUMNPROPERTY(
                              OBJECT_ID(TABLE_SCHEMA + ''.'' + @TableName),
                              COLUMN_NAME,
                              ''ColumnID''
                          )
                      )
               FROM   INFORMATION_SCHEMA.COLUMNS
               WHERE  TABLE_NAME = @TableName
                      AND COLUMNPROPERTY(
                              OBJECT_ID(TABLE_SCHEMA + ''.'' + @TableName),
                              COLUMN_NAME,
                              ''ColumnID''
                          ) > @field

               SELECT @bit = (@field - 1)% 8 + 1

               SELECT @bit = POWER(2, @bit - 1)

               SELECT @char = ((@field - 1) / 8) + 1





               IF SUBSTRING(COLUMNS_UPDATED(), @char, 1) & @bit > 0
                  OR @Type IN (''I'', ''D'')
               BEGIN
                   SELECT @fieldname = COLUMN_NAME
                   FROM   INFORMATION_SCHEMA.COLUMNS
                   WHERE  TABLE_NAME = @TableName
                          AND COLUMNPROPERTY(
                                  OBJECT_ID(TABLE_SCHEMA + ''.'' + @TableName),
                                  COLUMN_NAME,
                                  ''ColumnID''
                              ) = @field



                   SELECT @sql = 
                          ''
           insert into Audit (    Type, 
           TableName, 
           PK, 
           FieldName, 
           OldValue, 
           NewValue, 
           UpdateDate, 
           UserName)
           select '''''' + @Type + '''''','''''' 
                          + @TableName + '''''','' + @PKSelect
                          + '','''''' + @fieldname + ''''''''
                          + '',convert(varchar(1000),d.'' + @fieldname + '')''
                          + '',convert(varchar(1000),i.'' + @fieldname + '')''
                          + '','''''' + @UpdateDate + ''''''''
                          + '','''''' + @UserName + ''''''''
                          + '' from #ins i full outer join #del d''
                          + @PKCols
                          + '' where i.'' + @fieldname + '' <> d.'' + @fieldname 
                          + '' or (i.'' + @fieldname + '' is null and  d.''
                          + @fieldname
                          + '' is not null)'' 
                          + '' or (i.'' + @fieldname + '' is not null and  d.'' 
                          + @fieldname
                          + '' is null)'' 



                   EXEC (@sql)
               END
           END

Answer 3

Hey es muy simple ver esto

@OLD_GUEST_NAME = d.GUEST_NAME de d eliminado;

esta variable almacenará su antiguo valor eliminado y luego podrá insertarlo donde desee.

por ejemplo-

Create trigger testupdate on test for update, delete
  as
declare @tableid varchar(50);
declare @testid varchar(50);
declare @newdata varchar(50);
declare @olddata varchar(50);


select @tableid = count(*)+1 from audit_test
select @testid=d.tableid from inserted d;
select @olddata = d.data from deleted d;
select @newdata = i.data from inserted i;

insert into audit_test (tableid, testid, olddata, newdata) values (@tableid, @testid, @olddata, @newdata)

go

Answer 4

Sé que esto es viejo, pero tal vez esto ayude a alguien más.

No registre valores "nuevos". Su tabla existente, INVITADOS, tiene los nuevos valores. Tendrá doble entrada de datos, además de que el tamaño de su base de datos crecerá demasiado rápido de esa manera.

Limpié esto y lo minimicé para este ejemplo, pero aquí están las tablas que necesitaría para cerrar la sesión de cambios:

CREATE TABLE GUESTS (
      GuestID INT IDENTITY(1,1) PRIMARY KEY, 
      GuestName VARCHAR(50), 
      ModifiedBy INT, 
      ModifiedOn DATETIME
)

CREATE TABLE GUESTS_LOG (
      GuestLogID INT IDENTITY(1,1) PRIMARY KEY, 
      GuestID INT, 
      GuestName VARCHAR(50), 
      ModifiedBy INT, 
      ModifiedOn DATETIME
)

Cuando un valor cambia en la tabla HUÉSPEDES (p. Ej., Nombre del invitado), simplemente cierre la sesión de la fila completa de datos, tal como está, en su tabla de Registro / Auditoría usando el disparador. Su tabla HUÉSPEDES tiene datos actuales, la tabla Registro / Auditoría tiene los datos antiguos.

Luego use una declaración de selección para obtener datos de ambas tablas:

SELECT 0 AS ''GuestLogID'', GuestID, GuestName, ModifiedBy, ModifiedOn FROM [GUESTS] WHERE GuestID = 1
UNION
SELECT GuestLogID, GuestID, GuestName, ModifiedBy, ModifiedOn FROM [GUESTS_LOG] WHERE GuestID = 1
ORDER BY ModifiedOn ASC

Sus datos saldrán con la apariencia de la tabla, desde la más antigua a la más reciente, con la primera fila como se creó y la última fila con los datos actuales. Puede ver exactamente qué cambió, quién lo cambió y cuándo lo cambiaron.

Opcionalmente, solía tener una función que pasaba en bucle a través del RecordSet (en ASP clásico) y solo mostraba los valores que habían cambiado en la página web. Fue una gran pista de auditoría para que los usuarios pudieran ver lo que había cambiado con el tiempo.