How to Create Generic SQL Server Stored Procedure to Perform Inserts into Audit Table Based on Inserted and Deleted in Trigger

How To Create Generic SQL Server Stored Procedure To Perform Inserts Into Audit Table Based on Inserted and Deleted In Trigger

We've solved that problem in the following way.

select <list of tracked columns here> into #deleted from deleted;

declare /*const*/ @TABLE_NAME sysname = '[table name]';

declare f cursor
local
forward_only
read_only
for
  select c.name, quotename(c.name, '[')
  from
    sys.columns c
    inner join sys.types t on c.system_type_id = t.system_type_id
  where
    c.object_id = object_id(@TABLE_NAME)
    and c.is_computed = 0
    and c.is_identity = 0
    and t.name not in ('text', 'image', 'timestamp', 'xml')
    and (substring(COLUMNS_UPDATED(), ((c.column_id - 1) / 8) + 1, 1) & power(2, (c.column_id - 1) % 8)) > 0
  ;

declare @field_name sysname, @field_name_sanitised sysname;
create table #results (row_id int not null, field_name sysname not null, oldval nvarchar(150) null, newval nvarchar(150) null);

-- For each changed field, insert what exactly changed into #results

open f;

fetch next from f into @field_name, @field_name_sanitised;
while @@fetch_status = 0
begin
  declare @query nvarchar(4000);

  set @query =  N'insert into #results(row_id, field_name, oldval, newval)
                  select d.row_id, @field_name, d.' + @field_name_sanitised + N', i.' + @field_name_sanitised + N'
                  from
                    #deleted d inner join ' + @TABLE_NAME + N' i on d.row_id = i.row_id
                  where
                    (d.' + @field_name_sanitised + N' <> i.' + @field_name_sanitised + N')
                    or
                    (case when d.' + @field_name_sanitised + N' is null then 1 else 0 end <> case when i.' + @field_name_sanitised + N' is null then 1 else 0 end);'
                ;    

  exec sp_executesql
    @stmt = @query,
    @params = N'@field_name sysname',
    @field_name = @field_name
  ;

  fetch next from f into @field_name, @field_name_sanitised;
end;

close f;
deallocate f;

-- Do something meaningful to #results here

Related reading:

• COLUMNS_UPDATED
• sys.columns

Procedure based trigger for audit table

I will preface by saying that Temporal Tables or SQL Audit are far better for this kind of thing, and you are basically reinventing the wheel.

---

Be that as it may, the below should give you a good model for a trigger

CREATE OR ALTER TRIGGER TR_users ON users
AFTER INSERT, UPDATE, DELETE
AS

SET NOCOUNT ON;  -- prevent issues with bad client drivers

IF NOT EXISTS (SELECT 1 FROM inserted) AND NOT EXISTS (SELECT 1 FROM deleted)
    RETURN;  -- early bail-out

INSERT users_audit (users_id, old, new, updated_by)
SELECT
  ISNULL(i.id, d.id),
  i.user_data,
  d.user_data,
  SUSER_SNAME()
FROM inserted i
FULL JOIN deleted d ON d.id = i.id  -- full join to match by all primary key columns
WHERE NOT EXISTS (
    SELECT i.user_data    -- add other columns here
    INTERSECT             -- because INTERSECT deals correctly with nulls
    SELECT d.user_data
);

go

If you want this code for each table, I strongly suggest that instead of trying to write a single dynamic trigger, you instead write a tool that can generate specific triggers and audit tables for each table. Note that primary keys may contain multiple columns and you need to match all of them.

Audit Triggers: Use INSERTED or DELETED system tables

I'd go with :

Appraoch 2: Store, in audit table, every record that goes into main table
( using system table INSERTED).

is one more row per item really going to kill the DB? This way you have the complete history together.

If you purge out rows (a range all older than X day) you can still tell if something has changed or not:

• if an audit row exists (not purged) you can see if the row in question changed.
• if no audit rows exist for the item (all were purged) nothing changed (since any change writes to the audit table, including completely new items)

if you go with Appraoch 1: and purge out a range, it will be hard (need to remember purge date) to tell new inserts vs. ones where all rows were purged.

How to access inserted and deleted logical tables with a stored procedure

You can use the inserted and deleted tables only in triggers or in the output clause of DML statements.

In an insert statement, the output clause can reference the inserted table:

DECLARE @Ids AS TABLE (id int);

INSERT INTO dbo.Table (ID) 
OUTPUT Inserted.ID INTO @Ids(id)
VALUES (1), (2), (3);

In an update statement, you can reference both the inserted table and the deleted table:

DECLARE @Ids AS TABLE (oldId int, newId int);

UPDATE dbo.Table 
SET ID = 1
OUTPUT Deleted.ID, Inserted.ID INTO @Ids(oldId, newId);

And in a delete statement, you can reference the deleted table:

DECLARE @Ids AS TABLE (id int);

DELETE dbo.Table 
OUTPUT Inserted.ID INTO @Ids(id)
FROM dbo.Table 
WHERE ID IN (1, 2, 3);

A Merge statement is unique since you have access to both the source table and the inserted/deleted tables in it's output clause, as demonstrated on this post.

Creating audit triggers in SQL Server

I just want to call out couple of points:

Use code generators You can't have a single procedure to track all tables, you will need to generate similar but distinct triggers on each tracked table. This kind of job is best suited for automated code generation. In your place I would use an XSLT transformation to generate the code from XML, and the XML can be generated automatically from metadata. This allows you to easily maintain the triggers by regenerating them each time you make a change to the audit logic/structure or a target table is added/altered.

Consider capacity planning for the audit. An audit table that tracks all value changes will be, by far, the biggest table in the database: it will contain all the current data and
all the history of the current data. Such a table will increase the database size by 2-3 orders of magnitude (x10, x100). And the audit table will quickly become the bottleneck of everything:

• every DML operation will require locks in the audit table
• all administrative and maintenance operations will have to accommodate the size of the database due to audit

Take into account the schema changes. A table named 'Foo' may be dropped and later a different table named 'Foo' may be created. The audit trail has to be able to distinguish the two different objects. Better use a slow changing dimension approach.

Consider the need to efficiently delete audit records. When the retention period dictated by your application subject policies is due, you need to be able to delete the due audit records. It may not seem such a big deal now, but 5 years later when the first records are due the audit table has grown to 9.5TB it may be a problem.

Consider the need to query the audit. The audit table structure has to be prepared to respond efficiently to the queries on audit. If your audit cannot be queried then it has no value. The queries will be entirely driven by your requirements and only you know those, but most audit records are queried for time intervals ('what changes occurred between 7pm and 8pm yesterday?'), by object ('what changes occurred to this record in this table?') or by author ('what changes did Bob in the database?').

Audit table update trigger does not work using a stored procedure

The point of matter was as I mentioned in the question:

Trigger could not be run from App but I was able to fire it directly from a database update.

The problem was application was connecting to DB with its own generic User which did not have Execute permission for Trigger.

To make things more complicated there was no exception at application level.

The fix was to add

WITH EXECUTE AS OWNER 

To the trigger.

---

Related Topics