what is the correct syntax for creating a database trigger for insert, modify and delete

Question

i have what seems like a basic scenario for a db trigger in SQL server and i am running into an issue.

i have table Users (id, name, phone, etc) and i have tables UsersHistory (id, user_id action, fields, timestamp)

i want a database trigger where anytime inserts, updates or deletes into Users, i want a new record created in UsersHistory with the user id and the action that was done (insert new, updated fields, deleted id. Basically an audit log table.

this is how far i got, but i can't figure out how to:

1. Get the id on modify and deletes and also
2. How to get a list of fields that have changed and the action that was committed (insert, delete, update)

---

CREATE TRIGGER Update_Users_History 
   ON  Users
   AFTER INSERT,DELETE,UPDATE
 AS 
 BEGIN
-- Insert statements for trigger here

insert into UsersHistory (user_id, [action], [fields], timestamp)
select  max(id) as user_id, {action ??},{fields??}  getdate() from Users)

END
GO

any suggestions?

Solution 1

The easiest might be to just simply create three triggers - one for each operation:

CREATE TRIGGER trgUserInsert
ON dbo.User AFTER INSERT
AS BEGIN
   INSERT INTO dbo.UserHistory............
END     

CREATE TRIGGER trgUserDelete
ON dbo.User AFTER DELETE
AS BEGIN
   INSERT INTO dbo.UserHistory............
END 

CREATE TRIGGER trgUserUpdate
ON dbo.User AFTER UPDATE
AS BEGIN
   INSERT INTO dbo.UserHistory............
END 

That way, things are simple and you easily understand what you're doing, plus it gives you the ability to turn off a trigger for a single operation, if you e.g. need to insert or delete a huge list of items.

Inside the trigger, you have two "pseudo-tables" - Inserted (for INSERT and UPDATE) and Deleted (for UPDATE and DELETE). These pseudo tables contain the values for the newly inserted values (or the updated ones in UPDATE), or the ones that were deleted (for DELETE) or have been updated (the old values, before the update, for the UPDATE operation).

You need to be aware that a trigger will be called once even if you update a huge number of rows, e.g. Inserted and Deleted will typically contain multiple rows.

As a sample, you could write a "AFTER INSERT" trigger like this (just guessing what your table structure might be....):

CREATE TRIGGER trgUserInsert
ON dbo.User AFTER INSERT
AS BEGIN
   INSERT INTO 
      dbo.UserHistory(UserID, Action, DateTimeStamp, AuditMessage)
      SELECT 
         i.UserID, 'INSERT', getdate(), 'User inserted into table'
      FROM
         Inserted i
END     

You are looking for a way to find out which "action" this trigger caused? I don't see any way to do this - another reason to keep the three trigger separate. The only way to find this out would be to count the rows in the Inserted and Updated tables:

• if both counts are larger than zero, it's an UPDATE
• if the Inserted table has rows, but the Deleted does not, it's an INSERT
• if the Inserted table has no rows, but the Deleted does, it's a DELETE

You're also looking for a "list of fields that were updated" - again, you won't have any simple solution, really. You could either just loop through the fields in the "Users" table that are of interest, and check

IF UPDATE(fieldname) ......

but that gets a bit tedious.

Or you could use the COLUMNS_UPDATED() function - this however doesn't give you a nice list of column names, but a VARBINARY in which each column is basically one bit, and if it's turned on, that column was updated. Not very easy to use.....

---

If you really want to create a single, big trigger, this could serve as a basis - it detects what operation has caused the trigger to fire, and will insert entries into your User_History table:

CREATE TRIGGER trgUser_Universal
ON dbo.Users
AFTER INSERT, UPDATE, DELETE
AS BEGIN
  DECLARE @InsHasRows BIT = 0
  DECLARE @DelHasRows BIT = 0

  IF EXISTS(SELECT TOP 1 * FROM INSERTED)
    SET @InsHasRows = 1

  IF EXISTS(SELECT TOP 1 * FROM DELETED)
    SET @DelHasRows = 1

  DECLARE @TriggerAction VARCHAR(20)

  IF @InsHasRows = 1 AND @DelHasRows = 1 
     SET @TriggerAction = 'UPDATE'
  ELSE
     IF @InsHasRows = 1
        SET @TriggerAction = 'INSERT'
     ELSE  
        SET @TriggerAction = 'DELETE'

  IF @InsHasRows = 1
    INSERT INTO dbo.UsersHistory(user_id, [action], [fields], timestamp)
      SELECT i.UserId, @TriggerAction, null, getdate()
      FROM INSERTED i
  ELSE  
    INSERT INTO dbo.UsersHistory(user_id, [action], [fields], timestamp)
      SELECT d.UserId, @TriggerAction, null, getdate()
      FROM DELETED d
END

I haven't included the figuring out which fields have been updated part just yet - that's left as an exercise to the reader :-)

---

Does that help at all?

Solution 2

There are two "tables" that are used in the trigger. One is DELETED and one is INSERTED. When you delete a row, that row is captured in the DELETED table. When you insert a row, that row is captured in the INSERTED table. When you update a row, the old row is in the DELETED table, and the new row is in the INSERTED table. The DELETED and INSERTED tables have the same schema as the table on which you are adding the trigger.

You might check out this solution that will create a query for you that will make all the auditing triggers you want, as well as the table in which to store the audits, excluding any selected tables. It will only do UPDATE triggers, but could easily be modified to make INSERT and DELETE triggers as well.