Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Create a Simple SQL Server Trigger to Build an Audit Trail


By:   |   Read Comments (3)   |   Related Tips: More > Triggers

Problem

I have a SQL Server table in which I need to see who changed what and when. Is there a simple way to create an audit trail in SQL Server to meet these needs?

Solution

In this tip, we show you how to write T-SQL statements that will create a trigger to populate an audit table to store changes to existing records and record when new records are added to the table. We are using SQL Server 2016 CTP 2.0, but this could be done in any version of SQL Server.

Create Example SQL Server Tables

In the top portion of the image below we see where we create our example data table named tblOrders with a primary key column named OrderID, a date column named OrderApprovalDateTime and a varchar column named OrderStatus.

In the bottom portion of the image below we see where we create our table named tblOrdersAudit that will record changes to the data in the table tblOrders. The audit table has its own primary key column named OrderAuditID, all of the columns and their data types from tblOrders, plus columns named UpdatedBy and UpdatedOn. The data type for the UpdatedBy column is nvarchar(128) which is the data type returned by the SUSER_SNAME() function that will be used to get the Login name for the principal making the changes.

Create the data table and the audit table

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 updated or inserted in tblOrders, essentially taking a snapshot of the new or changed record along with which principal affected the record and when it happened. The T-SQL for creating this trigger is shown below.

Successfully create the trigger

Test SQL Server Audit Trigger for Inserts

To test the trigger for its behavior on inserting new records, we populate the table with three test records with the OrderApprovalDateTime set to NULL and the OrderStatus set to "Pending". We then select from both tables and inspect the results as shown below. We can see that the new records are in the audit table.

Successfully test the trigger on INSERT

Test SQL Server Audit Trigger for Updates

Next, we will test the trigger's behavior on an update. We will set the order status to approved and set the order approval date for the first record (OrderID=1). When we query both tables we can see how the changes to OrderID=1 are recorded in tblOrdersAudit.

Successfully test the trigger on UPDATE of the first record

Let's test again, but this time let's update the record where OrderID=2. When we query both tables we can see how the changes to OrderID=2 are recorded in tblOrdersAudit.

Successfully test again the trigger on UPDATE of the second record

This time let's update the OrderStatus to Cancelled for the record where OrderID=1 . We can see in tblOrdersAudit where the order was approved and then cancelled.

Test the trigger when the order status is set to Cancelled

Sample SQL Server Audit Trigger Code

use MSSQLTips
go

create table tblOrders
(
  OrderID integer Identity(1,1) primary key,
  OrderApprovalDateTime datetime,
  OrderStatus varchar(20)
)

create table tblOrdersAudit
(
  OrderAuditID integer Identity(1,1) primary key,
  OrderID integer,
  OrderApprovalDateTime datetime,
  OrderStatus varchar(20),
  UpdatedBy nvarchar(128),
  UpdatedOn datetime
)
go
  
create trigger tblTriggerAuditRecord on tblOrders
after update, insert
as
begin
  insert into tblOrdersAudit 
  (OrderID, OrderApprovalDateTime, OrderStatus, UpdatedBy, UpdatedOn )
  select i.OrderID, i.OrderApprovalDateTime, i.OrderStatus, SUSER_SNAME(), getdate() 
  from  tblOrders t 
  inner join inserted i on t.OrderID=i.OrderID 
end
go

insert into tblOrders values (NULL, 'Pending')
insert into tblOrders values (NULL, 'Pending')
insert into tblOrders values (NULL, 'Pending')
go

select * from tblOrders
select * from tblOrdersAudit

update tblOrders 
set OrderStatus='Approved', 
OrderApprovalDateTime=getdate()  
where OrderID=1
go

select * from tblOrders
select * from tblOrdersAudit order by OrderID, OrderAuditID
go

update tblOrders 
set OrderStatus='Approved', 
OrderApprovalDateTime=getdate()  
where OrderID=2

go

select * from tblOrders
select * from tblOrdersAudit order by OrderID, OrderAuditID
go

update tblOrders 
set OrderStatus='Cancelled'
where OrderID=1
go

select * from tblOrders
select * from tblOrdersAudit order by OrderID, OrderAuditID
go
Next Steps

Now you have an audit trail that is easy to query and analyze. You can also experiment with different combinations of inserts and updates.

Also, check out more tips on triggers in SQL Server on MSSQLTips.com.



Last Update:






About the author
MSSQLTips author Dallas Snider Dr. Dallas Snider is an Assistant Professor in the Computer Science Department at the University of West Florida and has 18+ years of SQL experience.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Tuesday, May 02, 2017 - 9:54:15 PM - Phil Burney Back To Top

 

 

 USE [RPDScale]

GO

/****** Object:  StoredProcedure [dbo].[usp_build_audit_trail]    Script Date: 5/2/2017 8:37:42 PM ******/

/* Here is a chunk of code I wrote back in 2007 for quickly adding an audit trail to a database.  Try it out on a small DB as see what you think.*/

/* Phil_Burney@msn.com */

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: Phil Burney

-- Create date: May 4, 2007

-- Description: Build Audit Trail and associated triggers

-- =============================================

/*

--------------------------------------------------------------------------------------------

  ***  Change USE RPDSCALE2 to proper database name before use

 

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 table for audit data, 

 

Created

LastChanged

RowVersion

LastChangedBy

--------------------------------------------------------------------------------------------

*/

ALTER PROCEDURE [dbo].[usp_build_audit_trail]

AS

BEGIN

 

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

 

    -- Insert statements for procedure here

PRINT '*** Make sure you used the correct database! ***'

print Char(13) + Char(10)

print Char(13) + Char(10)

DECLARE 

   @TableName VARCHAR(50), 

   @SchemaName VARCHAR(50), 

   @SQL NVARCHAR(max)

-- for each table

-- 1

DECLARE cTables CURSOR FAST_FORWARD READ_ONLY

  FOR

  SELECT t.name 

FROM sys.tables t

WHERE left(t.name,6) <> 'audit_' --AND (t.name = 'c_test' OR t.name = 'tbl_comm')

 

--2 

OPEN cTables

--3 

FETCH cTables INTO  @TableName   -- prime the cursor

WHILE @@Fetch_Status = 0 

  BEGIN

 

/*

--Step 1 Build Audit tables for each table

--Delete table if it exists

Build SQL statement to create table column by column using SELECT output

concatenated to other strings, then executed

 

*/

print ' --  ***********  BEGIN BUILD AUDIT TABLE SECTION ************ '

 

--Delete table if it already exists, then rebuild it --NOTE: This will delete existing audit trail

IF EXISTS (SELECT * FROM

sys.tables T

WHERE T.name ='Audit_' + @TableName)

BEGIN

SET @SQL = 'DROP TABLE Audit_' + @TableName

print ' --  *** DROP Existing Audit Table ***'

PRINT @SQL

print Char(13) + Char(10)

EXEC (@SQL)

END

 

-- *** BEGIN BUILD AUDIT TABLE - For currently fetched table name ***

 

SET @SQL = 'CREATE TABLE Audit_' + @TableName + '('

+ 'Audit_record_id BIGINT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED, ' + Char(13) + Char(10) 

+ 'DateModified DATETIME NULL, ' + Char(13) + Char(10) 

+ 'ModifiedBy NVARCHAR(128) NULL, ' + Char(13) + Char(10) 

+ 'HostName NVARCHAR(128) NULL, ' + Char(13) + Char(10) 

+ 'ApplicationName NVARCHAR(128) NULL, ' + Char(13) + Char(10) 

+ 'Operation NVARCHAR(2) NULL, ' + Char(13) + Char(10) 

+ 'RowVersion INT NULL ' + Char(13) + Char(10) 

--Build column definitions, row by row from system table

SELECT @SQL = @SQL +

 

+ ',' --leading comma so we dont have to take it off at the end

+ '['  + c.name + '] [' + ty.name + '] ' + ++++++  --multiple ++ are legal

 

CASE

WHEN ty.name = 'nvarchar'  THEN '(' + CAST(c.max_length/2 as nvarchar(10)) + ')'

ELSE ''

END  -- nvarchar vs varchar

 

 + ' NULL ' + Char(13) + Char(10) 

 

FROM sys.tables AS t

JOIN sys.columns AS c   --could be INNER JOIN

ON t.[object_id] = c.[object_id]  --use braces to distinguish between keyword object_id

JOIN sys.types AS ty

ON c.system_type_id = ty.user_type_id   --ty.user_type_id because 2 rows for system_type_id

WHERE

t.name = @TableName AND c.name <> 'ts'  AND c.name <> 'LastModified' AND c.name <> 'LastModifiedBy'

AND c.name <> 'RowVersion' and c.name <> 'Created' --dont use timestamp datatype in audit table

 

 

 

SELECT @SQL = @SQL + ') '

 

 

print ' -- *** Create Table Statement ***' 

print @sql  --rem this out if you want, displays the sql that builds the audit tables

exec (@SQL)

print Char(13) + Char(10) 

print Char(13) + Char(10) 

 

-- *** END BUILD AUDIT TABLE ***

 

/*

   ***  BEGIN ADD AUDIT DATA COLUMNS TO AUDITED TABLES  ***

 

*** Step 2 Add columns to each table for audit data, IF they dont already exist ***

Created

LastChanged

RowVersion

LastChangedBy

*/

 

print ' --  ***********  BEGIN ADD COLUMNS TO AUDITED TABLE SECTION ************ '

print ' -- *** IF they exist in table already, this section will be blank. ***'

 

-- Add created column

IF NOT EXISTS (SELECT * FROM

sys.tables T

INNER JOIN sys.columns C

ON t.object_id = c.object_id

WHERE T.name = @TableName AND  c.name = 'Created')

 

BEGIN

SET @SQL = 'ALTER TABLE ' + @TableName + ' ADD Created DateTime NOT NULL DEFAULT(GetDate())'

EXEC (@SQL)

PRINT @SQL

END

 

 

 

-- Add LastChanged column

IF NOT EXISTS (SELECT * FROM

sys.tables T

INNER JOIN sys.columns C

ON t.object_id = c.object_id

WHERE T.name = @TableName AND  c.name = 'LastModified')

 

BEGIN

   SET @SQL = 'ALTER TABLE ' + @TableName + ' ADD LastModified DateTime NOT NULL DEFAULT(GetDate())'

EXEC (@SQL)

PRINT @SQL

END

 

-- Add RowVersion column

IF NOT EXISTS (SELECT * FROM

sys.tables T

INNER JOIN sys.columns C

ON t.object_id = c.object_id

WHERE T.name = @TableName AND  c.name = 'RowVersion')

 

BEGIN

SET @SQL = 'ALTER TABLE ' + @TableName + ' ADD RowVersion INT NOT NULL DEFAULT(1)'

EXEC (@SQL)

PRINT @SQL

END

 

 

 

-- Add LastChangedBy column

IF NOT EXISTS (SELECT * FROM

sys.tables T

INNER JOIN sys.columns C

ON t.object_id = c.object_id

WHERE T.name = @TableName AND  c.name = 'LastModifiedBy')

 

BEGIN   

SET @SQL = 'ALTER TABLE '  + @TableName + ' ADD LastModifiedBy NVARCHAR(70) NOT NULL DEFAULT(system_user)'

EXEC (@SQL)

PRINT @SQL

   END

 

 

-- Add TS column, FOR CONCURRENCY TESTING

IF NOT EXISTS (SELECT * FROM

sys.tables T

INNER JOIN sys.columns C

ON t.object_id = c.object_id

WHERE T.name = @TableName AND  c.name = 'ts')

 

BEGIN   

PRINT '--  *  Added for testing concurrency *' 

SET @SQL = 'ALTER TABLE '  + @TableName + ' ADD ts TIMESTAMP NOT NULL '

EXEC (@SQL)

PRINT @SQL

   END

 

 

print Char(13) + Char(10) 

 

print ' -- ***  END of  Add Columns to audited table Section  ***'

print Char(13) + Char(10) 

 

 

print ' --  ***********  BEGIN BUILD TRIGGERS SECTION ************ '

print Char(13) + Char(10)

--  ***  BEGIN BUILD TRIGGERS SECTION ***

 

-- *** BEGIN UPDATE TRIGGER ***

 

IF EXISTS (SELECT * FROM

sys.triggers T

WHERE T.name ='Audit_UPDATE_' + @TableName)

BEGIN

SET @SQL = 'DROP TRIGGER Audit_UPDATE_' + @TableName

print ' --  *** DROP Existing UPDATE Trigger ***'

PRINT @SQL

EXEC (@SQL)

END

 

print Char(13) + Char(10) 

print ' --  *** Build UPDATE Trigger ***'

 

SET @SQL = 'CREATE TRIGGER    Audit_UPDATE_' + @TableName + ' ON '

+ @TableName + Char(13) + Char(10) 

+ ' AFTER UPDATE ' + Char(13) + Char(10) 

+ ' NOT FOR REPLICATION '   + Char(13) + Char(10) 

+ ' AS ' + Char(13) + Char(10) 

    + ' SET NOCOUNT ON ' + Char(13) + Char(10) 

+ 'If Trigger_NestLevel() > 1 Return; ' + Char(13) + Char(10) 

     

-- get one datetime stamp to use in both inserted and deleted row in audit table

    + ' DECLARE @AuditTime DATETIME ' + Char(13) + Char(10) 

+ ' SET @AuditTime = GetDate() '  + Char(13) + Char(10) 

 

+ 'INSERT INTO Audit_' + @TableName + '(' 

 

 

 

SELECT @SQL = @SQL +

+ c.name + ',' 

 

FROM sys.tables AS t

JOIN sys.columns AS c   --could be INNER JOIN

ON t.[object_id] = c.[object_id]  --use braces to distinguish between keyword object_id

WHERE

t.name = @TableName  AND c.name <> 'ts'  AND c.name <> 'LastModified' AND c.name <> 'LastModifiedBy'

AND c.name <> 'RowVersion' and c.name <> 'Created'

--NOTE:  RowVersion data is added explicitly below, this could be different

--       but I wanted to add ALL audit related data explicitly.

 

 

SET @SQL = @SQL + 'DateModified,ModifiedBy,HostName,ApplicationName,Operation,RowVersion)'  --Closing paren

 

 

 

SET @SQL = @SQL + 

+ ' SELECT ' 

 

SELECT @SQL = @SQL +

+ c.name + ','

 

FROM sys.tables AS t

JOIN sys.columns AS c   --could be INNER JOIN

ON t.[object_id] = c.[object_id]  --use braces to distinguish between keyword object_id

WHERE

t.name = @TableName AND c.name <> 'ts'  AND c.name <> 'LastModified' AND c.name <> 'LastModifiedBy'

AND c.name <> 'RowVersion' and c.name <> 'Created'

 

 

SET @SQL = @SQL + '@AuditTime,suser_sname(),Host_Name(),APP_NAME(),''UD'',RowVersion'

 

 + ' FROM DELETED  '  + Char(13) + Char(10) 

 

 

 

 

 

--  Inserted portion of UPDATE

 

+ '  INSERT      INTO Audit_' + @TableName + '(' 

 

SELECT @SQL = @SQL +

+ c.name + ',' 

 

FROM sys.tables AS t

JOIN sys.columns AS c   --could be INNER JOIN

ON t.[object_id] = c.[object_id]  --use braces to distinguish between keyword object_id

WHERE

t.name = @TableName AND c.name <> 'ts'  AND c.name <> 'LastModified' AND c.name <> 'LastModifiedBy'

AND c.name <> 'RowVersion' and c.name <> 'Created' --always name timestamp columns ts

 

 

SET @SQL = @SQL + 'DateModified,ModifiedBy,HostName,ApplicationName,Operation,RowVersion)'  --Closing paren

 

 

 

SET @SQL = @SQL + 

+ ' SELECT ' 

 

SELECT @SQL = @SQL +

+ c.name + ','

 

FROM sys.tables AS t

JOIN sys.columns AS c   --could be INNER JOIN

ON t.[object_id] = c.[object_id]  --use braces to distinguish between keyword object_id

WHERE

t.name = @TableName AND c.name <> 'ts'  AND c.name <> 'LastModified' AND c.name <> 'LastModifiedBy'

AND c.name <> 'RowVersion' and c.name <> 'Created'

 

 

SET @SQL = @SQL + '@AuditTime,suser_sname(),Host_Name(),APP_NAME(),''UI'',RowVersion+1'

 

 + ' FROM INSERTED  '

 

print @sql  --rem this out if you want, displays the sql that builds the audit tables

exec (@SQL)   

 

print Char(13) + Char(10) 

print Char(13) + Char(10) 

-- *******   END UPDATE  TRIGGER *******

 

 

 

-- *** BEGIN INSERT TRIGGER ***

 

IF EXISTS (SELECT * FROM

sys.triggers T

WHERE T.name ='Audit_INSERT_' + @TableName)

BEGIN

SET @SQL = 'DROP TRIGGER Audit_INSERT_' + @TableName

print ' --  *** DROP Existing INSERT Trigger ***'

PRINT @SQL

EXEC (@SQL)

END

 

print Char(13) + Char(10) 

print ' --  *** Build INSERT Trigger ***'

 

 

SET @SQL = 'CREATE TRIGGER    Audit_INSERT_' + @TableName + ' ON '

+ @TableName + Char(13) + Char(10) 

+ ' AFTER INSERT ' + Char(13) + Char(10) 

+ ' NOT FOR REPLICATION AS ' + Char(13) + Char(10) 

    + ' SET NOCOUNT ON ' + Char(13) + Char(10) 

-- get one datetime stamp to use in both inserted and deleted row in audit table

    + ' DECLARE @AuditTime DATETIME ' + Char(13) + Char(10) 

+ ' SET @AuditTime = GetDate() '  + Char(13) + Char(10) 

 

+ 'INSERT INTO Audit_' + @TableName + '(' 

 

 

--start of building columns clause

SELECT @SQL = @SQL +

+ c.name + ',' 

 

FROM sys.tables AS t

JOIN sys.columns AS c   --could be INNER JOIN

ON t.[object_id] = c.[object_id]  --use braces to distinguish between keyword object_id

WHERE

t.name = @TableName AND c.name <> 'ts'  AND c.name <> 'LastModified' AND c.name <> 'LastModifiedBy'

AND c.name <> 'RowVersion' and c.name <> 'Created'

 

 

SET @SQL = @SQL + 'DateModified,ModifiedBy,HostName,ApplicationName,Operation,RowVersion)'  --Closing paren

 

-- start of building values clause

 

SET @SQL = @SQL + 

+ ' SELECT ' 

 

SELECT @SQL = @SQL +

+ c.name + ','

 

FROM sys.tables AS t

JOIN sys.columns AS c   --could be INNER JOIN

ON t.[object_id] = c.[object_id]  --use braces to distinguish between keyword object_id

WHERE

t.name = @TableName AND c.name <> 'ts'  AND c.name <> 'LastModified' AND c.name <> 'LastModifiedBy'

AND c.name <> 'RowVersion' and c.name <> 'Created'

 

 

SET @SQL = @SQL + '@AuditTime,suser_sname(),Host_Name(),APP_NAME(),''I'',RowVersion'

 

 + ' FROM INSERTED  '  + Char(13) + Char(10) 

 

 

 

print @sql  --rem this out if you want, displays the sql that builds the audit tables

exec (@SQL)   

print Char(13) + Char(10) 

print Char(13) + Char(10) 

 

 

 

-- *** BEGIN DELETE TRIGGER ***

IF EXISTS (SELECT * FROM

sys.triggers T

WHERE T.name ='Audit_DELETE_' + @TableName)

BEGIN

SET @SQL = 'DROP TRIGGER Audit_DELETE_' + @TableName

print ' --  *** DROP Existing DELETE Trigger ***'

PRINT @SQL

EXEC (@SQL)

END

 

print Char(13) + Char(10) 

print ' -- *** Build DELETE Trigger ***'

 

SET @SQL = 'CREATE TRIGGER    Audit_DELETE_' + @TableName + ' ON '

+ @TableName + Char(13) + Char(10) 

+ ' AFTER DELETE ' + Char(13) + Char(10) 

+ ' NOT FOR REPLICATION AS ' + Char(13) + Char(10) 

    + ' SET NOCOUNT ON ' + Char(13) + Char(10) 

-- get one datetime stamp to use in both inserted and deleted row in audit table

    + ' DECLARE @AuditTime DATETIME ' + Char(13) + Char(10) 

+ ' SET @AuditTime = GetDate() '  + Char(13) + Char(10) 

 

+ 'INSERT INTO Audit_' + @TableName + '(' 

 

 

--start of building columns clause

SELECT @SQL = @SQL +

+ c.name + ',' 

 

FROM sys.tables AS t

JOIN sys.columns AS c   --could be INNER JOIN

ON t.[object_id] = c.[object_id]  --use braces to distinguish between keyword object_id

WHERE

t.name = @TableName  AND c.name <> 'ts'   AND c.name <> 'LastModified' AND c.name <> 'LastModifiedBy'

AND c.name <> 'RowVersion' and c.name <> 'Created'

 

 

SET @SQL = @SQL + 'DateModified,ModifiedBy,HostName,ApplicationName,Operation,RowVersion)'  --Closing paren

 

 

 

SET @SQL = @SQL + 

+ ' SELECT ' 

-- start of building values clause

 

SELECT @SQL = @SQL +

+ c.name + ','

 

FROM sys.tables AS t

JOIN sys.columns AS c   --could be INNER JOIN

ON t.[object_id] = c.[object_id]  --use braces to distinguish between keyword object_id

WHERE

t.name = @TableName  AND c.name <> 'ts'   AND c.name <> 'LastModified' AND c.name <> 'LastModifiedBy'

AND c.name <> 'RowVersion' and c.name <> 'Created'

 

 

SET @SQL = @SQL + '@AuditTime,suser_sname(),Host_Name(),APP_NAME(),''D'',RowVersion'

 

 + ' FROM DELETED  '  + Char(13) + Char(10) 

 

print @sql  --rem this out if you want, displays the sql that builds the audit tables

exec (@SQL)   

print Char(13) + Char(10) 

print Char(13) + Char(10) 

print Char(13) + Char(10)

 

 

 

-- *** BEGIN MODIFIED TRIGGER ***

 

IF EXISTS (SELECT * FROM

sys.triggers T

WHERE T.name ='Audit_MODIFIED_' + @TableName)

BEGIN

SET @SQL = 'DROP TRIGGER Audit_MODIFIED_' + @TableName

print ' --  *** DROP Existing MODIFIED Trigger ***'

PRINT @SQL

EXEC (@SQL)

END

 

print Char(13) + Char(10)

print ' -- ***Build Modified Trigger ***'

 

SET @SQL = 'CREATE TRIGGER    Audit_MODIFIED_' + @TableName + ' ON '

+ @TableName + Char(13) + Char(10) 

+ ' AFTER UPDATE ' + Char(13) + Char(10) 

+ ' NOT FOR REPLICATION AS ' + Char(13) + Char(10) 

    + ' SET NOCOUNT ON ' + Char(13) + Char(10) 

-- get one datetime stamp to use in both inserted and deleted row in audit table

    + ' DECLARE @AuditTime DATETIME ' + Char(13) + Char(10) 

+ ' SET @AuditTime = GetDate() '  + Char(13) + Char(10) 

 

+ 'BEGIN TRY ' +  Char(13) + Char(10) 

+ '  ' +  Char(13) + Char(10) 

 

+ ' If Trigger_NestLevel() > 1 Return; ' + Char(13) + Char(10) 

 

+ ' If (UPDATE(Created) or UPDATE(LastModified) or UPDATE(LastModifiedBy) or UPDATE(RowVersion)) ' + Char(13) + Char(10) 

+ ' AND Trigger_NestLevel() = 1 ' + Char(13) + Char(10) --Check on this later

+ ' BEGIN ' + Char(13) + Char(10) 

+ ' Raiserror(''Not authorized to update this field.  Update failed.'', 16, 1); ' + Char(13) + Char(10) 

+ ' Rollback; ' + Char(13) + Char(10) 

+ ' Return;  ' + Char(13) + Char(10) 

+ ' END ' + Char(13) + Char(10) 

 

+ ' ' + Char(13) + Char(10) 

 

+ ' UPDATE ' + @TableName  + Char(13) + Char(10) 

 

+ ' SET [LastModified] = getdate(), ' + Char(13) + Char(10) 

+ ' [LastModifiedBy] = system_user, ' +  Char(13) + Char(10) 

+ ' [RowVersion] = ' + @TableName + '.[RowVersion] + 1 ' + Char(13) + Char(10)

 

+ ' FROM ' + @TableName + ' JOIN INSERTED ON   ' + Char(13) + Char(10) 

--start of building columns clause    TRY INSERTED if DELETED Doesnt work

SELECT @SQL = @SQL +

+ '(' + @TableName + '.' + c.name + ' = INSERTED.' + c.name +

+ ' OR (' + @TableName + '.' + c.name + ' IS NULL AND INSERTED.' + c.name + ' IS NULL))'

 

+ ' AND ' 

 

FROM sys.tables AS t

JOIN sys.columns AS c   --could be INNER JOIN

ON t.[object_id] = c.[object_id]  --use braces to distinguish between keyword object_id

WHERE

t.name = @TableName  AND c.name <> 'ts' AND c.name <> 'LastModified' AND c.name <> 'LastModifiedBy'

AND c.name <> 'RowVersion' and c.name <> 'Created'

 

 

SET @SQL = @SQL + + '0=0' + Char(13) + Char(10) --Fix this sometime, truncate last AND

 

 

 

+ ' ' + Char(13) + Char(10) 

 

+ 'END TRY ' +  Char(13) + Char(10) 

 

+ '  BEGIN CATCH ' +  Char(13) + Char(10) 

+ ' RAISERROR(''Not authorized to update this field.  Update failed.'', 16, 1 ) with log ' +  Char(13) + Char(10) 

+ '  END CATCH  ' +  Char(13) + Char(10) 

+ '  ' +  Char(13) + Char(10) 

 

 

 print @sql  --rem this out if you want, displays the sql that builds the audit tables

exec (@SQL)   

print Char(13) + Char(10) 

print Char(13) + Char(10) 

print Char(13) + Char(10) 

 

 

 

 

 

 

 

      FETCH cTables INTO @TableName   -- fetch next

  END

-- 4  

CLOSE cTables

-- 5

DEALLOCATE cTables

 

print '*** END OF Stored Procedure. ***' 

PRINT ' * NOTE: This output can be copied into new query window *'

PRINT ' *       and executed in pieces to aide troubleshooting. *'

END

 

 


Wednesday, June 01, 2016 - 6:51:25 AM - Steve Tipton Back To Top

"I do not understand how the trigger can work on updates as well as it works on inserts since you only state the "inserted" in the join clause ..."

 

In response to Jakub, I had a similar confusion and located this, which explains the usage of automatically-created tables to store inserts (this accounts for updates) and deletes:

https://msdn.microsoft.com/en-us/library/ms191300(v=sql.130).aspx

I hope that helps clear up confusion!

Steve


Monday, April 25, 2016 - 7:34:50 AM - Jakub Svoboda Back To Top

Hey Dallas,

Thanks for a nice guide, however I do not understand one part of the trigger. Maybe I just overlooked something. Can you help me out? I do not understand how the trigger can work on updates as well as it works on inserts since you only state the "inserted" in the join clause.

See here:

create trigger tblTriggerAuditRecord on tblOrders
after update, insert
as
begin
  insert into tblOrdersAudit 
  (OrderID, OrderApprovalDateTime, OrderStatus, UpdatedBy, UpdatedOn )
  select i.OrderID, i.OrderApprovalDateTime, i.OrderStatus, SUSER_SNAME(), getdate() 
  from  tblOrders t 
  inner join inserted i on t.OrderID=i.OrderID 
end
go

From my understanding, the inserted in the join clause is saying insert to the audit table ONLY the rows that were inserted to the main table. So, should not I just specify somewhere that when there is update on that table, the trigger should do the insert as well?

Hope my concern is clear, thanks a lot for help!

Jakub


Learn more about SQL Server tools