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.