Log record changes in SQL server in an audit table
Take a look at this article on Simple-talk.com by Pop Rivett. It walks you through creating a generic trigger that will log the OLDVALUE and the NEWVALUE for all updated columns. The code is very generic and you can apply it to any table you want to audit, also for any CRUD operation i.e. INSERT, UPDATE and DELETE. The only requirement is that your table to be audited should have a PRIMARY KEY (which most well designed tables should have anyway).
Here's the code relevant for your GUESTS Table.
- Create AUDIT Table.
IF NOT EXISTS
(SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[Audit]')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE TABLE Audit
(Type CHAR(1),
TableName VARCHAR(128),
PK VARCHAR(1000),
FieldName VARCHAR(128),
OldValue VARCHAR(1000),
NewValue VARCHAR(1000),
UpdateDate datetime,
UserName VARCHAR(128))
GO
- CREATE an UPDATE Trigger on the GUESTS Table as follows.
CREATE TRIGGER TR_GUESTS_AUDIT ON GUESTS FOR UPDATE
AS
DECLARE @bit INT ,
@field INT ,
@maxfield INT ,
@char INT ,
@fieldname VARCHAR(128) ,
@TableName VARCHAR(128) ,
@PKCols VARCHAR(1000) ,
@sql VARCHAR(2000),
@UpdateDate VARCHAR(21) ,
@UserName VARCHAR(128) ,
@Type CHAR(1) ,
@PKSelect VARCHAR(1000)
--You will need to change @TableName to match the table to be audited.
-- Here we made GUESTS for your example.
SELECT @TableName = 'GUESTS'
-- date and user
SELECT @UserName = SYSTEM_USER ,
@UpdateDate = CONVERT (NVARCHAR(30),GETDATE(),126)
-- Action
IF EXISTS (SELECT * FROM inserted)
IF EXISTS (SELECT * FROM deleted)
SELECT @Type = 'U'
ELSE
SELECT @Type = 'I'
ELSE
SELECT @Type = 'D'
-- get list of columns
SELECT * INTO #ins FROM inserted
SELECT * INTO #del FROM deleted
-- Get primary key columns for full outer join
SELECT @PKCols = COALESCE(@PKCols + ' and', ' on')
+ ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
WHERE pk.TABLE_NAME = @TableName
AND CONSTRAINT_TYPE = 'PRIMARY KEY'
AND c.TABLE_NAME = pk.TABLE_NAME
AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
-- Get primary key select for insert
SELECT @PKSelect = COALESCE(@PKSelect+'+','')
+ '''<' + COLUMN_NAME
+ '=''+convert(varchar(100),
coalesce(i.' + COLUMN_NAME +',d.' + COLUMN_NAME + '))+''>'''
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
WHERE pk.TABLE_NAME = @TableName
AND CONSTRAINT_TYPE = 'PRIMARY KEY'
AND c.TABLE_NAME = pk.TABLE_NAME
AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
IF @PKCols IS NULL
BEGIN
RAISERROR('no PK on table %s', 16, -1, @TableName)
RETURN
END
SELECT @field = 0,
@maxfield = MAX(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName
WHILE @field < @maxfield
BEGIN
SELECT @field = MIN(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
AND ORDINAL_POSITION > @field
SELECT @bit = (@field - 1 )% 8 + 1
SELECT @bit = POWER(2,@bit - 1)
SELECT @char = ((@field - 1) / 8) + 1
IF SUBSTRING(COLUMNS_UPDATED(),@char, 1) & @bit > 0
OR @Type IN ('I','D')
BEGIN
SELECT @fieldname = COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
AND ORDINAL_POSITION = @field
SELECT @sql = '
insert Audit ( Type,
TableName,
PK,
FieldName,
OldValue,
NewValue,
UpdateDate,
UserName)
select ''' + @Type + ''','''
+ @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
GO
Find changes from an audit table
This will provide the result as specified in your question, though it is far from a sensible or scalable solution. If at all possible, I would recommend you completely revisit your change auditing:
declare @EmpAudit table (
empID int
, empName varchar(50)
, empAge int
, auditDataState varchar(50)
, auditDMLAction varchar(50)
, auditUser varchar(50)
, auditDateTime datetime
, updateColumns varchar(50)
);
insert into @EmpAudit values
(1, 'Alex', 22, 'New', 'Insert','c@a.com',getdate(),''),
(2, 'Jhonny', 18, 'New', 'Insert','c@a.com',getdate()-0.5,''),
(2, 'Jhonny', 18, 'Old', 'Update','b@a.com',getdate()-1,'Employee Name, Employee Age'),
(2, 'Jone', 25, 'New', 'Update','b@a.com',getdate()-1.5,'Employee Name, Employee Age'),
(2, 'Jone', 25, 'Old', 'Update','a@a.com',getdate()-2,'Employee Age'),
(2, 'Jone', 30, 'New', 'Update','a@a.com',getdate()-2.5,'Employee Age'),
(2, 'Jone', 30, 'Old', 'Update','a@a.com',getdate()-3,'Employee Age'),
(2, 'Jone', 20, 'New', 'Update','a@a.com',getdate()-3.5,'Employee Age'),
(2, 'Jone', 20, 'Old', 'Update','a@a.com',getdate()-4,'Employee Name'),
(2, 'Jhone', 20, 'New', 'Update','a@a.com',getdate()-4.5,'Employee Name');
with d as
(
select empID
,empName
,empAge
,auditDataState
,auditDMLAction
,auditUser
,auditDateTime
,updateColumns
,row_number() over (partition by empID order by auditDateTime) as rn
from @EmpAudit
)
select case when o.empName <> n.empName then 'Employee Name : from ' + o.empName + ' to ' + n.empName else '' end
+case when charindex(',',o.UpdateColumns) > 0 then ', ' else '' end
+case when o.empAge <> n.empAge then 'Employee Age : from ' + cast(o.empAge as varchar(3)) + ' to ' + cast(n.empAge as varchar(3)) else '' end as Change
from d as o
join d as n
on o.empID = n.empID
and o.updateColumns = n.updateColumns
and o.rn = n.rn+1
and n.auditDataState = 'New'
where o.auditDataState = 'Old';
Output:
Change
-----------------------------------------------------------------
Employee Name : from Jone to Jhone
Employee Age : from 30 to 20
Employee Age : from 25 to 30
Employee Name : from Jhonny to Jone, Employee Age : from 18 to 25
How to make Audit Log in SQL Server?
Thanks to everyone that had commented to help me out but I was able to solve it by using Ruben's link given and making adjustments accordingly.
Here's the link: Log record changes in SQL server in an audit table
Just follow it and should answer any problems you may encounter.
Thanks.
Best way to implement an audit trail in SQL Server?
There are many ways to do that; it depends which version of SQL Server you are using.
Here are few
Audit trail with shadow table and trigger Here is the link
Also you can consider to use SQL Server 2008 Audit feature Here is the link
Select only changes to a value from an audit log table
You need to get the previous value. This version uses MySQL syntax with a correlated subquery to get the result:
select t.*
from (select t.*,
(select cxp from t t2 where t2.date < t.date order by date desc limit1
) as prevcxp
from t
) t
where prevcxp is NULL or prevcxp <> cxp
In other databases, you might use lag() instead of the subquery, the limit might be replaced by a top or even fetch first 1 row.
How to keep an audit/history of changes to the table
There are two common ways of creating audit trails.
- Code your data access layer.
- In the database itself using triggers.
There are advantages and disadvantages to both. Some people prefer one over the other. It's often down to the type of app and the type of database use you can expect.
If you do it in your DA layer it's pretty much up to you. You just need to add code to every method that saves to the database to also save a log of the changes. This auditing code could be in your DA layer code, or even in your stored procs in your database if you are using stored procs for everything. Essentially the premise is the same, any time you make a change to the database, log that change.
If you want to go down the triggers route, you can write custom triggers for each table, or fashion a more generic trigger that works the same on lots of tables. Check out this article on audit triggers. This works by firing of triggers whenever a change is made, and the triggers log the changes. Remember that if you want to audit SELECT statements, you can't use triggers, you'll have to do that with in code/stored proc auditing. It's also worth remember that depending on your database, triggers may not fire in all circumstances. For example, most databases don't fire triggers during TRUNCATE statements. Check that your triggers get fired in any case that you need auditing.
Alternately, you could also take a look at using the service broker to do async auditing on a dedicated machine. This is more complex and takes a bit of configuring to set up.
Which ever way you do it you need to decide on the format the audit log will take. Normally you would save this log in your database, but you could just save it in a log file or whatever suits your requirements. You could use a single audit table that logs all changes, or you could have an audit table per main table being audited. For large scale implementations you could even consider putting the audit tables in a totally separate database. If your logging into a table, it's common to have a "change type" field which indicates if the audited change was an insert, update or delete style of change, along with the changed data, user who made the change and the date/time the change was made. Don't forget to include the old and new data for update style changes.
(T-SQL) How to query an audit table, and find changes between 2 dates
Here is an alternative approach that my prove useful, using OUTER APPLY. Note that the AuditID column is used as a tie-breaker mostly because the sample data does not have datetime values.
SQL Fiddle
CREATE TABLE AuditTable (
AuditID int
, VendorID int
, PaymentType int
, CreateDateUTC date
);
INSERT INTO AuditTable
VALUES (999, 8048, 2, '2017-10-30'),
(1000, 1234, 5, '2017-10-31'),
(1001, 8048, 7, '2017-10-31'),
(1002, 1234, 5, '2017-10-31'),
(1003, 1234, 7, '2017-10-31'),
(1004, 1234, 5, '2017-11-01');
Query 1:
select
*
from AuditTable a
outer apply (
select top(1) PaymentType, CreateDateUTC
from AuditTable t
where a.VendorID = t.VendorID
and a.CreateDateUTC >= t.CreateDateUTC
and a.AuditID > t.AuditID
order by CreateDateUTC DESC, AuditID DESC
) oa (PrevPaymentType, PrevDate)
order by
vendorid
, CreateDateUTC
Results:
| AuditID | VendorID | PaymentType | CreateDateUTC | PrevPaymentType | PrevDate |
|---------|----------|-------------|---------------|-----------------|------------|
| 1000 | 1234 | 5 | 2017-10-31 | (null) | (null) |
| 1002 | 1234 | 5 | 2017-10-31 | 5 | 2017-10-31 |
| 1003 | 1234 | 7 | 2017-10-31 | 5 | 2017-10-31 |
| 1004 | 1234 | 5 | 2017-11-01 | 7 | 2017-10-31 |
| 999 | 8048 | 2 | 2017-10-30 | (null) | (null) |
| 1001 | 8048 | 7 | 2017-10-31 | 2 | 2017-10-30 |
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?').
How to create Audit history table table
You need to write a trigger function on that specific table.
Check the below example,
CREATE TABLE Customer (Id int, customer_name varchar(50));
INSERT INTO Customer VALUES(1, 'Jhon');
INSERT INTO Customer VALUES(2, 'Smith');
-- Create audit table
CREATE TABLE [dbo].[AuditTable](
[Id] [int] NOT NULL,
[OldValue] [varchar](50) NULL,
[NewValue] [varchar](50) NULL
) ON [PRIMARY]
Assume you have a customer table and want to insert a audit record whenever a changes made to customer table. Then write a trigger as follow,
CREATE TRIGGER Customer_Audit on Customer
AFTER UPDATE
AS
INSERT INTO AuditTable(Id, OldValue, NewValue)
select d.Id, d.customer_name, c.customer_name
from deleted d
inner join Customer c
on d.Id = c.Id
GO
Now whenever changes made to customer table, a audit record will insert to audit table as follow,
UPDATE Customer SET customer_name = 'Shamique' WHERE id = 2
Output after update the record
Hope that is clear. Cheerz!
Edited
Alter the table with updatedDate and updatedTime column then change trigger query as follow,
INSERT INTO AuditTable(Id, OldValue, NewValue, updateDate, updateTime)
select d.Id, d.customer_name, c.customer_name,
CONVERT(VARCHAR(10),getdate(),101) as DatePart,
CONVERT(VARCHAR(10),getdate(),108) as TimePart from deleted d inner join
Customer c on d.Id = c.Id
If you need to add Date and Time as one, then simply you can use getDate() syntax in your select statement.
Related Topics
Return Multiple Fields as a Record in Postgresql with Pl/Pgsql
Disable Rails SQL Logging in Console
SQL Error: Ora-01861: Literal Does Not Match Format String 01861
Use Email Address as Primary Key
How to Run a Stored Procedure in SQL Server Every Hour
How to Have Nhibernate Only Generate the SQL Without Executing It
Generate Script in SQL Server Management Studio
Passing Table and Column Name Dynamically Using Bind Variables
Convert Date Format into Dd/Mmm/Yyyy Format in SQL Server
How to Do a Before Updated Trigger with SQL Server
SQL - Select First 10 Rows Only
Finding Duplicate Rows in SQL Server
Calculate Business Hours Between Two Dates
Dynamic Sorting Within SQL Stored Procedures
SQL Server Select Distinct Rows Using Most Recent Value Only
How to Show All Privileges from a User in Oracle