Trigger - After Update need trigger to audit table when there are multiple updates to a record

Question

question

caoneill asked Jul 10, '20

Trigger - After Update need trigger to audit table when there are multiple updates to a record

Hi, Looking for an archive table to contain all updates for each record. Currently have it working but only displays one of the updates when I made two on the same record. Here is a test case. End result should be two records in the archive table. One for update to Locationid and the other for Location. Any help is appreciated!!

CREATE TABLE [dbo].[employees](
[Id] [int] NOT NULL,
[Name] [varchar](50) NULL,
[LocationID] int  NULL,
[Location] [varchar](50) NULL
) ON [PRIMARY]
GO 
insert into [dbo].[employees] values (1,'Steve',1,'New York') 
CREATE TABLE [dbo].[auditemployees](
[Id] [int] NOT NULL,
[Name] [varchar](50) NULL,
[LocationID] int  NULL,
[Location] [varchar](50) NULL,
[UpdateTime] datetime null,
[ActionPerformed] varchar(50)
) ON [PRIMARY]
GO
CREATE TRIGGER [dbo].[AfterUPDATETrigger] on [dbo].[employees]
FOR UPDATE 
AS DECLARE @ID INT,
       @Name VARCHAR(50),
   @LocationID int,
   @Location [varchar](50),
   @ActionPeformed VARCHAR(50);
SELECT @ID = ins.ID FROM INSERTED ins;
SELECT @Name = ins.Name FROM INSERTED ins;
SELECT @LocationID = ins.LocationID FROM INSERTED ins;
SELECT @Location = ins.Location FROM INSERTED ins;
IF UPDATE(LocationID)
BEGIN
     SET @ActionPeformed = 'U-LocationID'
END
IF UPDATE(Location)
BEGIN
      SET @ActionPeformed = 'U-Location'
END
INSERT INTO [auditemployees]( 
       ID
      ,Name
      ,LocationID
      ,Location
      ,[UpdateTime]
      ,[ActionPerformed])
VALUES (@ID,
        @Name,
@LocationID,
@Location,
GETDATE(),
@ActionPeformed);
GO
ALTER TABLE [dbo].[employees] ENABLE TRIGGER [AfterUPDATETrigger]
GO
update employees set locationid = '2',location = 'Boston' where id = 1 
select * from auditemployees

sql-server-2012 trigger

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2020-07-10T15:18:49Z

Kev Riley answered Jul 10, '20

There are 2 issues here.

First, don't code your trigger to assume only one row will be affected - you are querying the INSERTED table and setting the values of scalar variables - what happens if more than one row is affected by your update?

Second, you say you want 2 rows in the archive table because there are 2 updates - but there's not. There is 1 update that is affecting 2 columns. That single atomic update changed both locationid and location in one UPDATE - not sure what benefit you would have by seeing those as 2 separate rows in auditemployees

But if thats what you want, then the trigger should be something more like this

alter trigger [dbo].[AfterUPDATETrigger]
on [dbo].[employees]
for update
as
if update(LocationID)
begin
insert into [auditemployees] (Id, Name, LocationID, Location, [UpdateTime], [ActionPerformed])
select Id, Name, LocationID, Location, getdate(), 'U-LocationID' from INSERTED
end;
if update(Location)
begin
insert into [auditemployees] (Id, Name, LocationID, Location, [UpdateTime], [ActionPerformed])
select Id, Name, LocationID, Location, getdate(), 'U-Location' from INSERTED
end;

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2020-07-10T15:38:19Z

caoneill answered Jul 10, '20

thank you!

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question