question

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!

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.

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!!!

It would be so much easier if you upgrade to sql2008 .... ( server auditing)

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

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

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...