Trigger- SQL SERVER
13/03/2020
0
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?
Alguém sabe uma maneira de fazer isto?
Chromusmaster
Curtir tópico
+ 0
Responder
Post mais votado
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
Chromusmaster
Responder
Clique aqui para fazer login e interagir na Comunidade :)