Creating audit triggers in SQL Server
I need to implement change tracking on two tables in my SQL Server 2005 database. I need to audit additions, deletions, updates (with detail on what was updated). I was planning on using a trigger to do this, but after poking around on Google I found that it was incredibly easy to do this incorrectly, and I wanted to avoid that on the get-go.
Can anybody post an example of an update trigger that accomplishes this successfully and in an elegant manner? I am hoping to end up with an audit table with the following structure:
- TransactionType (update/insert/delete)
... but I am open for suggestions.
Creating audit triggers in SQL Server, Create Example SQL Server Audit Trigger. In this example, we want our trigger to insert a row into the tblOrdersAudit table any time a record is Tuesday, May 02, 2017 - 9:54:15 PM - Phil Burney Step 1 Build Audit tables for each table add columns for DateModified, ModifiedBy, HostName, ApplicationName, Operation Step 2 Build triggers for each table UPDATE INSERT DELETE Modify - updates actual table with username, Step 3 Add columns to each
We are using ApexSQL Audit that generates audit triggers and below are data structures used by this tool. If you don’t plan on buying a 3rd party solution you can install this tool in trial mode, see how they implemented triggers and storage and then create something similar for yourself.
I didn’t bother getting into too many details on how these tables work but hopefully this will get you started.
Create a Simple SQL Server Trigger to Build an Audit Trail, Create a SQL Server audit trigger for INSERT or UPDATE events and gain visibility into activity across your tables to secure sensitive data. Create Audit trigger by running the following script: (please note that you should join tables by a public key; in this case it is ContactID). create trigger AuditTrigger1 on Sales1 after update, insert insert into AuditTable1
There is no generic way to do it the way you want. Ultimately you end up writing reams of code for each table. Not to mention it can be fairy slow if you need to compare each column for change.
Also the fact that you might be updating multiple rows at the same time implies you need to open a cursor to loop through all the records.
The way I'd do it will be using table with structure identical to the tables you are tracking and unpivot it later to show which columns have actually changed. I'd also keep track of the session that actually did the change. This assumes that you have primary key in the table being tracked.
So given a table like this
CREATE TABLE TestTable (ID INT NOT NULL CONSTRAINT PK_TEST_TABLE PRIMARY KEY, Name1 NVARCHAR(40) NOT NULL, Name2 NVARCHAR(40))
I'd create an audit table like this in the audit schmea.
CREATE TABLE Audit.TestTable (SessionID UNIQUEIDENTIFER NOT NULL, ID INT NOT NULL, Name1 NVARCHAR(40) NOT NULL, Name2 NVARCHAR(40), Action NVARCHAR(10) NOT NULL CONSTRAINT CK_ACTION CHECK(Action In 'Deleted','Updated'), RowType NVARCHAR(10) NOT NULL CONSTRAINT CK_ROWTYPE CHECK (RowType in 'New','Old','Deleted'), ChangedDate DATETIME NOT NULL Default GETDATE(), ChangedBy SYSNHAME NOT NULL DEFAULT USER_NAME())
And a trigger for Update like this
CREATE Trigger UpdateTestTable ON DBO.TestTable FOR UPDATE AS BEGIN SET NOCOUNT ON DECLARE @SessionID UNIQUEIDENTIFER SET @SessionID = NEWID() INSERT Audit.TestTable(Id,Name1,Name2,Action,RowType,SessionID) SELECT ID,name1,Name2,'Updated','Old',@SessionID FROM Deleted INSERT Audit.TestTable(Id,Name1,Name2,Action,RowType,SessionID) SELECT ID,name1,Name2,'Updated','New',@SessionID FROM Inserted END
This runs quite fast. During reporting , you simply join the rows based on sessionID, and Primary key and produce a report. Alternatively you can have a batch job that periodically goes through all the tables in the audit table and prepare a name-value pair showing the changes.
How to Create a SQL Server Audit Trigger, How to create and use DML triggers to audit data changes. USE AdventureWorks2014 GO IF OBJECT_ID ('Purchasing.StandardPriceHistory', 'U') IS NOT NULL DROP TABLE Purchasing. USE AdventureWorks2014 GO IF OBJECT_ID ('Purchasing.uStandardPriceHistory', 'TR') IS NOT NULL DROP TRIGGER Purchasing. Run Netwrix Auditor → Select “Reports” → choose “SQL Server” → Select "All SQL Server Data Changes" report → Click "View". Subscribe to this report and receive it via e-mail or have it delivered to a specified shared folder according to the schedule you set.
It looks simple and should work well until you have image/varbinary etc elements in your tables You have whole old record and whole new record as xml. Should also work properly for inserting multiple columns at 1 batch.
CREATE TABLE _AuditTable (Aud_Id int identity(1,1) primary key, Aud_TableName varchar(100), Aud_ActionType char(1), Aud_Username varchar(100), Aud_OLDValues xml, Aud_NEWValues xml, Aud_OperationDate datetime DEFAULT GETDATE() )
And trigger code
CREATE TRIGGER _test2_InsertUpdate on _test2 FOR INSERT, UPDATE AS BEGIN SET NOCOUNT ON; IF NOT EXISTS(SELECT 1 FROM deleted) AND NOT EXISTS(SELECT 1 FROM inserted) RETURN; declare @tablename varchar(100) SELECT @tablename = OBJECT_NAME(parent_object_id) FROM sys.objects WHERE sys.objects.name = OBJECT_NAME(@@PROCID) /*Action*/ DECLARE @ActionType char(1) IF EXISTS (SELECT * FROM inserted) IF EXISTS (SELECT * FROM deleted) SELECT @ActionType = 'U' ELSE SELECT @ActionType = 'I' ELSE SELECT @ActionType = 'D' declare @inserted xml, @deleted xml SET @inserted = (SELECT * FROM inserted FOR XML PATH) SET @deleted = (SELECT * FROM deleted FOR XML PATH) INSERT INTO _AuditTable(Aud_TableName, Aud_ActionType, Aud_Username, Aud_OLDValues, Aud_NEWValues) SELECT @tablename, @ActionType, SUSER_SNAME(), @deleted, @inserted END
Aud_Id | Aud_TableName | Aud_ActionType | Aud_Username | Aud_OLDValues | Aud_NEWValues | Aud_OperationDate 1 |_test2 | I |abc\mR | NULL |<row><name>abc</name></row> | 2018-11-07 12:38:34.937
How to create and use DML triggers to audit data changes, Currently, four types of triggers exist in SQL Server, and the first two are the most commonly used: DDL triggers (auditing CREATE, ALTER, Step 4 Creating Insert Trigger: First, we will start with the Insert Trigger: We have created an Insert Trigger named trInventoryInsert. This Insert Trigger is created to insert the IN/Out quantity of every transaction to the audit table. In this trigger, we have three conditions: First, we check for the item already existing in the audit table.
I finally found a universal solution, that does not require dynamic sql and logs changes of all columns.
Its not needed to change the trigger if the table changes.
This is the audit log:
CREATE TABLE [dbo].[Audit]( [ID] [bigint] IDENTITY(1,1) NOT NULL, [Type] [char](1) COLLATE Latin1_General_CI_AS NULL, [TableName] [nvarchar](128) COLLATE Latin1_General_CI_AS NULL, [PK] [int] NULL, [FieldName] [nvarchar](128) COLLATE Latin1_General_CI_AS NULL, [OldValue] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL, [NewValue] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL, [UpdateDate] [datetime] NULL, [Username] [nvarchar](8) COLLATE Latin1_General_CI_AS NULL, CONSTRAINT [PK_AuditB] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
This is the trigger for one table:
INSERT INTO ILSe.dbo.Audit ([Type], TableName, PK, FieldName, OldValue, NewValue, Username) SELECT CASE WHEN NOT EXISTS (SELECT ID FROM deleted WHERE ID = ISNULL(ins.PK,del.PK)) THEN 'I' WHEN NOT EXISTS (SELECT ID FROM inserted WHERE ID = ISNULL(ins.PK,del.PK)) THEN 'D' ELSE 'U' END as [Type], 'AGB' as TableName, ISNULL(ins.PK,del.PK) as PK, ISNULL(ins.FieldName,del.FieldName) as FieldName, del.FieldValue as OldValue, ins.FieldValue as NewValue, ISNULL(ins.Username,del.Username) as Username FROM (SELECT insRowTbl.PK, insRowTbl.Username, attr.insRow.value('local-name(.)', 'nvarchar(128)') as FieldName, attr.insRow.value('.', 'nvarchar(max)') as FieldValue FROM (Select i.ID as PK, i.LastModifiedBy as Username, convert(xml, (select i.* for xml raw)) as insRowCol from inserted as i ) as insRowTbl CROSS APPLY insRowTbl.insRowCol.nodes('/row/@*') as attr(insRow) ) as ins FULL OUTER JOIN (SELECT delRowTbl.PK, delRowTbl.Username, attr.delRow.value('local-name(.)', 'nvarchar(128)') as FieldName, attr.delRow.value('.', 'nvarchar(max)') as FieldValue FROM (Select d.ID as PK, d.LastModifiedBy as Username, convert(xml, (select d.* for xml raw)) as delRowCol from deleted as d ) as delRowTbl CROSS APPLY delRowTbl.delRowCol.nodes('/row/@*') as attr(delRow) ) as del on ins.PK = del.PK and ins.FieldName = del.FieldName WHERE isnull(ins.FieldName,del.FieldName) not in ('LastModifiedBy', 'ID', 'TimeStamp') and ((ins.FieldValue is null and del.FieldValue is not null) or (ins.FieldValue is not null and del.FieldValue is null) or (ins.FieldValue != del.FieldValue))
This trigger is for one Table named AGB. The Table with the name AGB has a primary Key Column with the name ID and a Column with the Name LastModifiedBy which contains the username that made the last edit.
The trigger consists of two parts, first it converts columns of inserted and deleted tables into rows. This is explained in detail here: https://stackoverflow.com/a/43799776/4160788
Then it joins the rows (one row per column) of the inserted and deleted tables by primary key and field name, and logs a line for each changed column. It does NOT log changes of ID, TimeStamp or LastModifiedByColumn.
You can insert your own TableName, Columns names.
You can also create the following stored procedure, and then call this stored procedure to generate your triggers:
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[_create_audit_trigger]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[_create_audit_trigger] AS' END ALTER PROCEDURE [dbo].[_create_audit_trigger] @TableName varchar(max), @IDColumnName varchar(max) = 'ID', @LastModifiedByColumnName varchar(max) = 'LastModifiedBy', @TimeStampColumnName varchar(max) = 'TimeStamp' AS BEGIN PRINT 'start ' + @TableName + ' (' + @IDColumnName + ', ' + @LastModifiedByColumnName + ', ' + @TimeStampColumnName + ')' /* if you have other audit trigger on this table and want to disable all triggers, enable this: EXEC ('ALTER TABLE ' + @TableName + ' DISABLE TRIGGER ALL')*/ IF EXISTS (SELECT * FROM sys.objects WHERE [type] = 'TR' AND [name] = 'tr_audit_'+@TableName) EXEC ('DROP TRIGGER [dbo].tr_audit_'+@TableName) EXEC (' CREATE TRIGGER [dbo].[tr_audit_'+@TableName+'] ON [ILSe].[dbo].['+@TableName+'] FOR INSERT, UPDATE, DELETE AS BEGIN SET NOCOUNT ON; INSERT INTO ILSe.dbo.Audit ([Type], TableName, PK, FieldName, OldValue, NewValue, Username) SELECT CASE WHEN NOT EXISTS (SELECT '+@IDColumnName+' FROM deleted WHERE '+@IDColumnName+' = ISNULL(ins.PK,del.PK)) THEN ''I'' WHEN NOT EXISTS (SELECT '+@IDColumnName+' FROM inserted WHERE '+@IDColumnName+' = ISNULL(ins.PK,del.PK)) THEN ''D'' ELSE ''U'' END as [Type], '''+@TableName+''' as TableName, ISNULL(ins.PK,del.PK) as PK, ISNULL(ins.FieldName,del.FieldName) as FieldName, del.FieldValue as OldValue, ins.FieldValue as NewValue, ISNULL(ins.Username,del.Username) as Username FROM (SELECT insRowTbl.PK, insRowTbl.Username, attr.insRow.value(''local-name(.)'', ''nvarchar(128)'') as FieldName, attr.insRow.value(''.'', ''nvarchar(max)'') as FieldValue FROM (Select i.'+@IDColumnName+' as PK, i.'+@LastModifiedByColumnName+' as Username, convert(xml, (select i.* for xml raw)) as insRowCol from inserted as i) as insRowTbl CROSS APPLY insRowTbl.insRowCol.nodes(''/row/@*'') as attr(insRow)) as ins FULL OUTER JOIN (SELECT delRowTbl.PK, delRowTbl.Username, attr.delRow.value(''local-name(.)'', ''nvarchar(128)'') as FieldName, attr.delRow.value(''.'', ''nvarchar(max)'') as FieldValue FROM (Select d.'+@IDColumnName+' as PK, d.'+@LastModifiedByColumnName+' as Username, convert(xml, (select d.* for xml raw)) as delRowCol from deleted as d) as delRowTbl CROSS APPLY delRowTbl.delRowCol.nodes(''/row/@*'') as attr(delRow)) as del on ins.PK = del.PK and ins.FieldName = del.FieldName WHERE isnull(ins.FieldName,del.FieldName) not in ('''+@LastModifiedByColumnName+''', '''+@IDColumnName+''', '''+@TimeStampColumnName+''') and ((ins.FieldValue is null and del.FieldValue is not null) or (ins.FieldValue is not null and del.FieldValue is null) or (ins.FieldValue != del.FieldValue)) END ') PRINT 'end ' + @TableName PRINT '' END
Auditing triggers in SQL Server databases - Solution center, Native tools can help you create SQL Server audit triggers — for example, you can create a trigger to track changes to a table, such as data DDL Triggers DDL triggers in SQL Server are fired on DDL events. i.e. against create, alter and drop statements, etc. These triggers are created at the database level or server level based on the type of DDL event. These triggers are useful in the below cases.
How to Create a SQL Server Audit Trigger, For auditing a particular table ,we need to create a similar structure audit table and create Insert , Update and Delete triggers to keep track of the changes in the table. This is utility procedure. SQL Server. Updated 4/21/2017. License. B. Creating a server audit with a Windows Application log target with options. The following example creates a server audit called HIPAA_Audit with the target set for the Windows Application log. The queue is written every second and shuts down the SQL Server engine on failure. CREATE SERVER AUDIT HIPAA_Audit TO APPLICATION_LOG WITH ( QUEUE
Script Create Audit Table and Insert\Update\Delete Triggers for a , in SQL Server databases, many of which include table triggers to log the changes. In this post I provide a streamlined approach to setting up those triggers and Introduction to SQL Server CREATE TRIGGER statement The CREATE TRIGGER statement allows you to create a new trigger that is fired automatically whenever an event such as INSERT, DELETE, or UPDATE occurs against a table. The following illustrates the syntax of the CREATE TRIGGER statement:
Code Generation for SQL Server Data Auditing Triggers, How do I create an audit trail in SQL Server? CREATE TRIGGER (Transact-SQL) 10/30/2019; 24 minutes to read +14; In this article. APPLIES TO: SQL Server Azure SQL Database Azure Synapse Analytics (SQL DW) Parallel Data Warehouse . Creates a DML, DDL, or logon trigger. A trigger is a special type of stored procedure that automatically runs when an event occurs in the database server.