Audit Changes Made To SQL Server Table

Question

How do you implement a trigger on SQL Server 2005?  I want to know what has been changed in the table, then run a query to display the records that were changed and by who.

I just need an after insert & after update trigger.  I just can't find a good guide on how to write these types of triggers to log changes made.

Answer 1

Hi Indigo,

Below I am showing you an example of how you can log the changes that happened in a table using triggers and also which user executed the change (Update and Delete):

Create Table Emp_master (EmpId int identity(1,1), Emp_Name varchar(50),DOJ datatime,Salary int)

Create Table Emp_Master_Change_Log(Session_Id int, Changed_By varchar(100), Change_Time datatime,EmpId int , Emp_Name varchar(50),DOJ datatime,Salary int)

Create Trigger Log_Emp_master_Change on dbo.Emp_Master

After update,delete

AS
Declare @Spid as int

Declare @UserName as varchar(100)

Set @Spid = (select @@Spid)

set @Username = (select login_name from sys.sysprocesses where spid = @Spid)

if exists(select * from deleted)

Begin

insert into Emp_Master_Change_Log

select @Spid as Session_Id,@UserName as Changed_By, getdate() as  Change_Time, EmpId, Emp_Name, DOJ,Salary from deleted

End

To check the changes that happened on the Emp_Master table query the Emp_Master_Change_Log and the Emp_Master and compare the data.

---

Thanks and Regards Alankar Chakravorty MCITP Database Administrator SQL Server 2008 MCITP Database Administrator SQL Server 2005

Answer 2

Hi ,

Try below links :

http://ajitananthram.wordpress.com/2012/05/26/auditing-external-activator/

http://www.allaboutmssql.com/2012/08/sql-server-audit-trigger.html

---

sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.