Trigger- SQL SERVER

SQL Server

SQL

SQL Azure

13/03/2020

Estou fazendo uma trilha de auditoria em um banco pelo SQL SERVER. Utilizando a opção After Update consigo saber qual o valor que foi alterado usando a tabela ficticia deleted da transaction. Porém preciso colocar de um modo mais genérico, preciso buscar o nome do campo na tabela e o seu valor de modo que a trigger possa funcionar para quaisquer tabelas do banco.

Alguém sabe uma maneira de fazer isto?
Chromusmaster

Chromusmaster

Curtidas 0

Melhor post

Chromusmaster

Chromusmaster

13/03/2020

Para quem precisar, consegui fazer da seguinte maneira:
CREATE TABLE [dbo].[History](
	[id] [uniqueidentifier] NOT NULL,
	[Table] [nvarchar](max) NOT NULL,
	[Field] [nvarchar](max) NOT NULL,
	[Value] [nvarchar](max) NOT NULL,
	[AlterDate] [datetime2](7) NOT NULL,
	[AlterAuthor] [uniqueidentifier] NOT NULL,
	[FieldId] [uniqueidentifier] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE OR ALTER TRIGGER [dbo].[TriggerHistory]
   ON  [dbo].[Configuration]
   AFTER UPDATE
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    DECLARE @Old_record Nvarchar(Max),
	@New_record Nvarchar(Max),
	@Field NVARCHAR(MAX),
	@ModifiedId Uniqueidentifier,
	@User Uniqueidentifier;

	IF ( UPDATE (id))
	RAISERROR (50009, 16, 10);
	IF( UPDATE ([name]))
		BEGIN
    -- Insert statements for trigger here
    SELECT @Old_record = Name from DELETED;
    SELECT @New_record = Name from INSERTED;
	SELECT @ModifiedId=id from deleted;
	SELECT @User=AuthorId from inserted;
		IF @Old_record != @New_record
			BEGIN
				INSERT INTO [dbo].[History](iD,[Table],[Field],[Value],[AlterDate],[AlterAuthor],[FieldId])
				SELECT NEWID(),'Configuration','Name',@Old_record,GETDATE(),@User,@ModifiedId FROM deleted;
			END
		END
	IF ( UPDATE ([value]))
		BEGIN
    -- Insert statements for trigger here
    SELECT @Old_record = Value from DELETED;
    SELECT @New_record = Value from INSERTED;
	SELECT @ModifiedId=id from deleted;
	SELECT @User=AuthorId from inserted;
		IF @Old_record != @New_record
			BEGIN
				INSERT INTO [dbo].[History](iD,[Table],[Field],[Value],[AlterDate],[AlterAuthor],[FieldId])
				SELECT NEWID(),'Configuration','Value',@Old_record,GETDATE(),@User,@ModifiedId FROM deleted;
			END
		END
	IF ( UPDATE ([system]))
		BEGIN
    -- Insert statements for trigger here
    SELECT @Old_record = System from DELETED;
    SELECT @New_record = System from INSERTED;
	SELECT @ModifiedId=id from deleted;
	SELECT @User=AuthorId from inserted;
		IF @Old_record != @New_record
			BEGIN
				INSERT INTO [dbo].[History](iD,[Table],[Field],[Value],[AlterDate],[AlterAuthor],[FieldId])
				SELECT NEWID(),'Configuration','system',@Old_record,GETDATE(),@User,@ModifiedId FROM deleted;
			END
		END
	IF ( UPDATE ([deactivated]))
		BEGIN
    -- Insert statements for trigger here
    SELECT @Old_record = Deactivated from DELETED;
    SELECT @New_record = Deactivated from INSERTED;
	SELECT @ModifiedId=id from deleted;
	SELECT @User=AuthorId from inserted;
		IF @Old_record != @New_record
			BEGIN
				INSERT INTO [dbo].[History](iD,[Table],[Field],[Value],[AlterDate],[AlterAuthor],[FieldId])
				SELECT NEWID(),'Configuration','deactivated',@Old_record,GETDATE(),@User,@ModifiedId FROM deleted;
			END
		END
	IF ( UPDATE ([AuthorId]))
		BEGIN
    -- Insert statements for trigger here
    SELECT @Old_record = AuthorId from DELETED;
    SELECT @New_record = AuthorId from INSERTED;
	SELECT @ModifiedId=id from deleted;
	SELECT @User=AuthorId from inserted;
		IF @Old_record != @New_record
			BEGIN
				INSERT INTO [dbo].[History](iD,[Table],[Field],[Value],[AlterDate],[AlterAuthor],[FieldId])
				SELECT NEWID(),'Configuration','AuthorId',@Old_record,GETDATE(),@User,@ModifiedId FROM deleted;
			END
		END
	IF ( UPDATE ([Category]))
		BEGIN
    -- Insert statements for trigger here
    SELECT @Old_record = Category from DELETED;
    SELECT @New_record = Category from INSERTED;
	SELECT @ModifiedId=id from deleted;
	SELECT @User=AuthorId from inserted;
		IF @Old_record != @New_record
			BEGIN
				INSERT INTO [dbo].[History](iD,[Table],[Field],[Value],[AlterDate],[AlterAuthor],[FieldId])
				SELECT NEWID(),'Configuration','Category',@Old_record,GETDATE(),@User,@ModifiedId FROM deleted;
			END
		END
END
GOSTEI 1
POSTAR