question

sqlrookie avatar image
sqlrookie asked

DML Triggers for auditing

Hi, I want to track all the DML changes on a database. I am using SQL Server 2008R2 Express edition hence can use only Triggers.I know that DDL Triggers are used on a database to track the changes but not for DML. Is there a way that we can create only one audit table per database and store all the DML changes happening on all tables in a database? Please help me out with the script and suggestions to achieve my goal. Thanks in advance!!
audittriggersdml
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

aderossi avatar image
aderossi answered
Hi, try this generic trigger: http://www.nigelrivett.net/SQLTriggers/AuditTrailTrigger.html You can start from there and then make improvements. Good Luck Alberto
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Dugi avatar image
Dugi answered
One option is to add the Output Clause in every Procedure that you are using for Insert/Delete/Update - and catching the values from the inserted and deleted special tables. For more info check the BOL: http://technet.microsoft.com/en-us/library/ms177564.aspx Another option is to use third party tools but as far as I know they are just per table so the Triggers will be created for every table, not to create one table for all operations!
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sqlrookie avatar image sqlrookie commented ·
Hey Dugi, Thanks for the suggestions. I know that we have to create trigger on all tables. I want to know whether we can save the change information to a single audit table per database or need to create a new table for each existing table as tablename_audit to track the changes?
0 Likes 0 ·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
I won't write a script for you (am on my smartphone right now) but can give you an idea. If the number of changes is not too massive, you could create a table with ID, tablename, beforevalues (either XML or nvarchar(Max)) and aftervalues, datetime and loginname. From insert, delete, and update triggers you use the DELETED and INSERTED virtual tables to populate the log-table, using the FOR XML clause. With DELETE-trigger the aftervalues Column is NULL, with INSERT-trigger the beforevalues-Column is NULL and with UPDATE-trigger the DELETE-table goes to the beforevalues and INSERTED-table goes to the aftervalues. But this solution Will use a LOT of space for a transaktion intensive system and Will also cause a Big hit on performance.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Dugi avatar image
Dugi answered
You can create a table with relevant columns that you want to store the DML, let say you need schemaname, tablename, columnname, old_value, new_value, date_action, UserName, Host_Name etc. Creating tables for every table to track changes it's not good practice at all!!!
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Wilfred van Dijk avatar image
Wilfred van Dijk answered
It would be so much easier if you upgrade to sql2008 .... ( server auditing)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sqlrookie avatar image
sqlrookie answered
Thank you all for your suggestions. I have written a script regarding this and i am stuck with an issue here. When i insert some data into the table, i end up getting the results of my audit table. I dont want that to be happen.Greatly appreciated for the help. Here is the query: -- Audit Table create table dbo.Audit_Test (AuditID int identity(1,1) not null, Operation nvarchar(50), Modified_Date datetime, Username sysname, ApplicationName nvarchar(200), [Changes] xml) --Table on which the trigger will be created create table dbo.Emp (ID int identity(1,1), Name nvarchar(50)) --Trigger Create trigger Tr_Emp on dbo.Emp for insert,update,delete as BEGIN set nocount on; DECLARE @operation as Varchar(10) DECLARE @Count as int SET @operation = 'Inserted' -- Setting operation to 'Inserted' SELECT @Count = COUNT(*) FROM DELETED if @Count > 0 BEGIN SET @operation = 'Deleted' -- Set Operation to 'Deleted' SELECT @Count = COUNT(*) FROM INSERTED IF @Count > 0 SET @operation = 'Updated' -- Set Operation to 'Updated' END if @operation = 'Deleted' BEGIN insert into dbo.Audit_Test(Operation,Modified_Date,Username,ApplicationName,[Changes]) select 'Deleted',GETDATE(),system_user,APP_NAME(), ( select i.ID as [ID_New], d.ID as [ID_Old], i.Name as [Name_New], d.Name as [Name_Old] from inserted i full outer join deleted d on i.ID = d.ID for xml raw, root('Audit_Test') ) as [Changes] from deleted END Else BEGIN select 'Inserted',GETDATE(),system_user,APP_NAME(), ( select i.ID as [ID_New], d.ID as [ID_Old], i.Name as [Name_New], d.Name as [Name_Old] from inserted i full outer join deleted d on i.ID = d.ID for xml raw, root('Audit_Test') ) as [Changes] from inserted END if @operation = 'Inserted' Begin insert into dbo.Audit_Test(Operation,Modified_Date,Username,ApplicationName,[Changes]) select 'Inserted',GETDATE(),system_user,APP_NAME(), ( select i.ID as [ID_New], d.ID as [ID_Old], i.Name as [Name_New], d.Name as [Name_Old] from inserted i full outer join deleted d on i.ID = d.ID for xml raw, root('Audit_Test') ) as [Changes] from inserted END Else BEGIN insert into dbo.Audit_Test(Operation,Modified_Date,Username,ApplicationName,[Changes]) select 'Updated',GETDATE(),system_user,APP_NAME(), ( select i.ID as [ID_New], d.ID as [ID_Old], i.Name as [Name_New], d.Name as [Name_Old] from inserted i full outer join deleted d on i.ID = d.ID for xml raw, root('Audit_Test') ) as [Changes] from inserted END END GO
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sqlrookie avatar image
sqlrookie answered
create table dbo.Audit_Test (AuditID int identity(1,1) not null, Operation nvarchar(50), Modified_Date datetime, Username sysname, ApplicationName nvarchar(200), [Changes] xml) create table dbo.Emp (ID int identity(1,1), Name nvarchar(50)) alter trigger Tr_Emp on dbo.Emp for insert,update,delete as BEGIN set nocount on; DECLARE @operation as Varchar(10) DECLARE @Count as int SET @operation = 'Inserted' -- Setting operation to 'Inserted' SELECT @Count = COUNT(*) FROM DELETED if @Count > 0 BEGIN SET @operation = 'Deleted' -- Set Operation to 'Deleted' SELECT @Count = COUNT(*) FROM INSERTED IF @Count > 0 SET @operation = 'Updated' -- Set Operation to 'Updated' END if @operation = 'Deleted' BEGIN insert into dbo.Audit_Test(Operation,Modified_Date,Username,ApplicationName,[Changes]) select 'Deleted',GETDATE(),system_user,APP_NAME(), ( select i.ID as [ID_New], d.ID as [ID_Old], i.Name as [Name_New], d.Name as [Name_Old] from inserted i full outer join deleted d on i.ID = d.ID for xml raw, root('Audit_Test') ) as [Changes] from deleted END Else BEGIN select 'Inserted',GETDATE(),system_user,APP_NAME(), ( select i.ID as [ID_New], d.ID as [ID_Old], i.Name as [Name_New], d.Name as [Name_Old] from inserted i full outer join deleted d on i.ID = d.ID for xml raw, root('Audit_Test') ) as [Changes] from inserted END if @operation = 'Inserted' Begin insert into dbo.Audit_Test(Operation,Modified_Date,Username,ApplicationName,[Changes]) select 'Inserted',GETDATE(),system_user,APP_NAME(), ( select i.ID as [ID_New], d.ID as [ID_Old], i.Name as [Name_New], d.Name as [Name_Old] from inserted i full outer join deleted d on i.ID = d.ID for xml raw, root('Audit_Test') ) as [Changes] from inserted END Else BEGIN insert into dbo.Audit_Test(Operation,Modified_Date,Username,ApplicationName,[Changes]) select 'Updated',GETDATE(),system_user,APP_NAME(), ( select i.ID as [ID_New], d.ID as [ID_Old], i.Name as [Name_New], d.Name as [Name_Old] from inserted i full outer join deleted d on i.ID = d.ID for xml raw, root('Audit_Test') ) as [Changes] from inserted END END GO
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sqlrookie avatar image
sqlrookie answered
Hey Alberto, Thanks for the script. It works perfect for me, but still need to make some changes according to my needs.At least i got an idea on how to write a query on this topic. Thanks again...
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.