SQL Trigger to find who updated a table and when it was done

I had a situation to find who is updating a table and when the table is getting updated but it was very difficult to enable trace for a long time.
I end up creating a trigger to capture the user who implemented the change (Insert/ Update/ Delete) and when it is implemented. Code snippet for your quick reference…

create table tab1(id int)

CREATE table triggerinfo(op varchar(20), opdatetime datetime, opuser varchar(60), opnotes varchar(200))

IF OBJECT_ID ('Trigger_tab1','TR') IS NOT NULL
   DROP TRIGGER Trigger_tab1
GO
create TRIGGER	Trigger_tab1 ON tab1 AFTER Insert, Update, DELETE
AS
BEGIN
	insert into triggerinfo(op,opdatetime,opuser,opnotes)
	select 'Delete', getdate(), SUSER_NAME(), 'tab1' + cast(id as varchar(10)) from deleted

	insert into triggerinfo(op,opdatetime,opuser,opnotes)
	select 'Insert', getdate(), SUSER_NAME(), 'tab1' + cast(id as varchar(10))  from inserted
END

Few updates to SQL Query to improve quality:

-- Create a table to track the changes
CREATE table triggerinfo(id int identity(1,1), op varchar(20), opdatetime datetime, opuser varchar(60), optable varchar(60), opnotes varchar(200))

-- Create trigger to push changes to triggerinfo table  
IF OBJECT_ID ('Trigger_tab1','TR') IS NOT NULL
   DROP TRIGGER Trigger_tab1
GO
create TRIGGER	Trigger_tab1 ON tab1 AFTER Insert, Update, DELETE
AS
BEGIN
	insert into triggerinfo(op,opdatetime,opuser,optable,opnotes)
	select 'Delete', getdate(), SUSER_NAME(), 'tab1' , cast(id as varchar(10)) from deleted

	insert into triggerinfo(op,opdatetime,opuser,opnotes)
	select 'Insert', getdate(), SUSER_NAME(), 'tab1' , cast(id as varchar(10))  from inserted
END


-- Query to generate script for Trigger creation on all tables of database 
-- NOTE: ID is not inserted in this query but can be modified 
select 'IF OBJECT_ID (''Trigger_' + name + ''',''TR'') IS NOT NULL
   DROP TRIGGER Trigger_' + name + '
GO
create TRIGGER	Trigger_' + name + ' ON ' + name + ' AFTER Insert, Update, DELETE
AS
BEGIN
	insert into Admindb..triggerinfo_SMILApplicationDB(op,opdatetime,opuser,optable,opnotes)
	select ''Delete'', getdate(), SUSER_NAME(), ''' + name + ''', '''' from deleted

	insert into Admindb..triggerinfo_SMILApplicationDB(op,opdatetime,opuser,optable,opnotes)
	select ''Insert'', getdate(), SUSER_NAME(), ''' +name + ''', '''' from inserted
END
--
--
' as Q
from sys.tables 



-- How to check all Triggers in database and if they are enabled?
select is_disabled, * from sys.triggers 


-- How to disable trigger 
-- Trigger can be created with Database or Server scope, that can be disabled with different syntax (Ref: https://msdn.microsoft.com/en-us/library/ms189748.aspx)
DISABLE TRIGGER TriggerName ON TableName;
GO
--
--

– 08/29/2016 – I had to update the query to capture the source from where the changes are being triggered.

CREATE table Customer_log(op varchar(20), opdatetime datetime, opuser varchar(60), CustomerID INT, ChangeTrackColumn INT,
[Computer_Name] [varchar] (80) NULL ,
[Windows_user] [varchar] (80) NULL ,
[sql_login] [varchar] (80) NULL ,
[program_Name] [varchar] (80) NULL ,
[MAC_address] [varchar] (80) NULL)

CREATE TRIGGER       Trigger_Customer ON Customer AFTER Insert, Update, DELETE
AS
BEGIN

       insert into Customer_log(op,opdatetime,opuser,CustomerID,ChangeTrackColumn,Computer_Name, Windows_User, sql_login, program_name, mac_address)
       select 'Delete', getdate(), SUSER_NAME(), CustomerID, ChangeTrackColumn,
	   (SELECT TOP 1 hostname FROM [sysprocesses] WHERE spid = @@spid ),
	(SELECT TOP 1 nt_username FROM [sysprocesses] WHERE spid = @@spid ),
	(SELECT TOP 1 loginame FROM [sysprocesses] WHERE spid = @@spid ),
	(SELECT TOP 1 program_name FROM [sysprocesses] WHERE spid = @@spid ),
	(SELECT TOP 1 net_address FROM [sysprocesses] WHERE spid = @@spid )
	   from deleted

       insert into Customer_log(op,opdatetime,opuser,CustomerID,ChangeTrackColumn,Computer_Name, Windows_User, sql_login, program_name, mac_address)
       select 'Insert', getdate(), SUSER_NAME(), CustomerID, ChangeTrackColumn,
	   (SELECT TOP 1 hostname FROM [sysprocesses] WHERE spid = @@spid ),
	(SELECT TOP 1 nt_username FROM [sysprocesses] WHERE spid = @@spid ),
	(SELECT TOP 1 loginame FROM [sysprocesses] WHERE spid = @@spid ),
	(SELECT TOP 1 program_name FROM [sysprocesses] WHERE spid = @@spid ),
	(SELECT TOP 1 net_address FROM [sysprocesses] WHERE spid = @@spid )
	   from inserted
END

--
--
Advertisements
This entry was posted in Productivity, Script, SQL, SQL Query and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s