![\"301-image001a.jpg\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/301-image001a.jpg\")
<\/p>\n <\/p>\n
Good morning Pop, I see the turnips are doing well<\/em><\/p>\n Well, God bless you and keep you Miss, they do well, but we could do with a drop of rain. But do tell me, Miss why the long face?<\/p>\n Oh Pop…such a dreadful thing! I’ve just been checking through the accounts of the Womens’ Institute and someone has hacked in and removed everything we had in savings for the new Village Hall!<\/em><\/p>\n Lorks a’mercy, missy…but at least your audit trail will show up how it happened and who it was that did it?<\/p>\n What audit-tail?! [Subsiding into sobs<\/b>] We haven’t got such a thing. The vicar’s son put the system together as a college project. Frankly, it’s lucky that the tables even have primary keys<\/em>.<\/p>\n Well, when IT people talk of audit trails, they refer to keeping an auditable account of who changed the data in a table, when, from what and to what. Basically, an audit trail is a way of tracking the details of all changes to data in certain database tables – usually in order to check the integrity of financial transactions. It provides a record of who has accessed a database and what operations he or she has performed during a given period of time.<\/p>\n When data is financial, this is often built into the system. Records are never updated or deleted, merely superseded, or ‘terminated’, so that databases grow to become a history of all financial transactions. The application programmers are allowed access to the database only through stored procedures.<\/p>\n If you don’t have the luxury of a properly-designed financial system, as you don’t by the sounds of it, then you need to devise a means of logging all the transactions that you want to monitor. You might try looking in the database transaction log but the problem with this is that it’s an indiscriminate record of all transactions, whether data-feeds, cosmetic changes or data modifications – so it’s hard to use it to monitor and track down particular financial business transactions.<\/p>\n So what’s to be done then, Pop?!<\/em><\/p>\n Well, by my reckoning, an audit trigger is the way to go for you. Take a look at this…you simply create one of these triggers for every table that you need to monitor. This trigger will provide an ‘audit trail’ of all changes made to a table. In the line select @TableName = ‘trigtest’<\/b> you simply swap trigrest<\/b> for the name of your table, and then the trigger will monitor all changes to that table.<\/p>\n It will place a record of these changes in a table called Audit<\/b>. It records in Audit <\/b>details of all inserted, deleted, changed columns and the old\/new values of the fdata that was altered in the table that the trigger protects. Anyway, here is the code…notice that it will put out an error message if there is no primary key on the table as the routine would then be unable to identify the affected rows…so it’s lucky the vicar’s son got that right at least!<\/p>\n — Set up the tables IF NOT EXISTS <\/em>— now we will illustrate the use of this tool IF EXISTS <\/em>–note that for this system to work there must be a primary key <\/em>–and now create the trigger itself. This has to be created for every CREATE TRIGGER tr_trigtest ON trigtest FOR INSERT, UPDATE, <\/em>DELETE DECLARE @bit INT <\/em>, <\/em>— date and user <\/em>— Action <\/em>— get list of columns <\/em>INFORMATION_SCHEMA.KEY_COLUMN_USAGE c <\/em>— Get primary key select for insert IF @PKCols IS <\/em>NULL SELECT @field = 0<\/em>, GO<\/p>\n ——————————————————-<\/p>\n –now we can test the trigger out <\/p>\n INSERT trigtest SELECT 1,1,’hi’, ‘bye’<\/p>\n INSERT trigtest SELECT 2,2,’hi’, ‘bye’<\/p>\n INSERT trigtest SELECT 3,3,’hi’, ‘bye’<\/p>\n SELECT * FROM Audit<\/p>\n SELECT * FROM trigtest<\/p>\n UPDATE trigtest SET s = ‘hibye’ WHERE i <> 1<\/p>\n UPDATE trigtest SET s = ‘bye’ WHERE i = 1<\/p>\n UPDATE trigtest SET s = ‘bye’ WHERE i = 1<\/p>\n UPDATE trigtest SET t = ‘hi’ WHERE i = 1<\/p>\n SELECT * FROM Audit<\/p>\n SELECT * FROM trigtest<\/p>\n DELETE trigtest<\/p>\n SELECT * FROM Audit<\/p>\n SELECT * FROM trigtest<\/p>\n GO<\/p>\n DROP TABLE Audit<\/p>\n GO<\/p>\n DROP TABLE trigtest<\/p>\n GO<\/p>\n<\/pre>\n
— Firstly, we create the audit table.
— There will only need to be one of these in a database<\/p>\n
(SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[Audit]’<\/em>)
AND OBJECTPROPERTY(id, N’IsUserTable’) = 1<\/em>)
CREATE TABLE <\/em>Audit
(Type CHAR(1<\/em>),
TableName VARCHAR(128<\/em>),
PK VARCHAR(1000<\/em>),
FieldName VARCHAR(128<\/em>),
OldValue VARCHAR(1000<\/em>),
NewValue VARCHAR(1000<\/em>),
UpdateDate datetime<\/em>,
UserName VARCHAR(128<\/em>))
<\/em>GO<\/p>\n
— by creating a dummy test table called TrigTest. <\/p>\n
(SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[trigtest]’<\/em>)
AND OBJECTPROPERTY(id, N’IsUserTable’) = 1<\/em>)
DROP TABLE <\/em>[dbo].[trigtest]
GO
CREATE TABLE <\/em>trigtest
(i INT <\/em>NOT NULL,
j INT <\/em>NOT NULL,
s VARCHAR(10<\/em>),
t VARCHAR(10<\/em>))
<\/em>GO<\/p>\n
–to the table but then a table without a primary key
–isn’t really a table is it?
ALTER TABLE trigtest ADD CONSTRAINT pk PRIMARY KEY (i, j<\/em>)
<\/em>GO<\/p>\n
-table you want to monitor<\/p>\n
AS<\/p>\n
@field INT <\/em>,
@maxfield INT <\/em>,
@char INT <\/em>,
@fieldname VARCHAR(128<\/em>) ,
@TableName VARCHAR(128<\/em>) ,
@PKCols VARCHAR(1000<\/em>) ,
@sql VARCHAR(2000<\/em>),
@UpdateDate VARCHAR(21<\/em>) ,
@UserName VARCHAR(128<\/em>) ,
@Type CHAR(1<\/em>) ,
@PKSelect VARCHAR(1000<\/em>)
<\/em>
–You will need to change @TableName to match the table to be audited
<\/em>SELECT @TableName = <\/em>‘trigtest’<\/p>\n
SELECT @UserName = SYSTEM_USER <\/em>,
@UpdateDate = CONVERT(VARCHAR(8), GETDATE(), 112<\/em>)
+ ‘ ‘ + CONVERT(VARCHAR(12), GETDATE(), 114<\/em>)<\/p>\n
IF EXISTS (SELECT * FROM inserted<\/em>)
IF EXISTS (SELECT * FROM deleted<\/em>)
SELECT @Type = <\/em>‘U’
<\/em>ELSE
SELECT @Type = <\/em>‘I’
<\/em>ELSE
SELECT @Type = <\/em>‘D’<\/p>\n
SELECT * INTO #ins FROM <\/em>inserted
SELECT * INTO #del FROM <\/em>deleted
<\/em>
— Get primary key columns for full outer join
<\/em>SELECT @PKCols = COALESCE(@PKCols + ‘ and’, ‘ on’<\/em>)
+ ‘ i.’ + c.COLUMN_NAME + ‘ = d.’ + <\/em>c.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk <\/em>,<\/p>\n
WHERE pk.TABLE_NAME = <\/em>@TableName
AND CONSTRAINT_TYPE = <\/em>‘PRIMARY KEY’
AND c.TABLE_NAME = <\/em>pk.TABLE_NAME
AND c.CONSTRAINT_NAME = <\/em>pk.CONSTRAINT_NAME<\/p>\n
SELECT @PKSelect = COALESCE(@PKSelect+’+’,”<\/em>)
+ ”'<‘ + <\/em>COLUMN_NAME
+ <\/em>‘=”+convert(varchar(100),
coalesce(i.’ + COLUMN_NAME +’,d.’ + COLUMN_NAME + <\/em>‘))+”>”’
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk <\/em>,
<\/em>INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
WHERE pk.TABLE_NAME = <\/em>@TableName
AND CONSTRAINT_TYPE = <\/em>‘PRIMARY KEY’
AND c.TABLE_NAME = <\/em>pk.TABLE_NAME
AND c.CONSTRAINT_NAME = <\/em>pk.CONSTRAINT_NAME<\/p>\n
<\/em>BEGIN
RAISERROR(‘no PK on table %s’, 16, -1, @TableName<\/em>)
<\/em>RETURN
END<\/p>\n
@maxfield = MAX(ORDINAL_POSITION<\/em>)
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = <\/em>@TableName
WHILE @field < <\/em>@maxfield
<\/em>BEGIN
SELECT @field = MIN(ORDINAL_POSITION<\/em>)
FROM <\/em>INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = <\/em>@TableName
AND ORDINAL_POSITION > <\/em>@field
SELECT @bit = (@field – 1 )% 8 + <\/em>1
SELECT @bit = POWER(2,@bit – 1<\/em>)
SELECT @char = ((@field – 1) \/ 8) + <\/em>1
IF SUBSTRING(COLUMNS_UPDATED(),@char, 1) & @bit > 0
OR @Type IN (‘I’,’D’<\/em>)
<\/em>BEGIN
SELECT @fieldname = <\/em>COLUMN_NAME
FROM <\/em>INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = <\/em>@TableName
AND ORDINAL_POSITION = <\/em>@field
SELECT @sql = <\/em>‘
insert Audit ( Type,
TableName,
PK,
FieldName,
OldValue,
NewValue,
UpdateDate,
UserName)
select ”’ + @Type + <\/em>”’,”’
<\/em> + @TableName + ”’,’ + @PKSelect
+ ‘,”’ + @fieldname + ””
+ ‘,convert(varchar(1000),d.’ + @fieldname + ‘)’
+ ‘,convert(varchar(1000),i.’ + @fieldname + ‘)’
+ ‘,”’ + @UpdateDate + ””
+ ‘,”’ + @UserName + ””
+ ‘ from #ins i full outer join #del d’
+ @PKCols
+ ‘ where i.’ + @fieldname + ‘ <> d.’ + @fieldname
+ ‘ or (i.’ + @fieldname + ‘ is null and d.’
+ @fieldname
+ ‘ is not null)’
+ ‘ or (i.’ + @fieldname + ‘ is not null and d.’
+ @fieldname
+ ‘ is null)’
EXEC (@sql)
END
END<\/p>\n