Change Tracking Trigger

Creates a trigger that tracks all successful changes on a table and writes the time of last change while dynamically determining the calling table name.

DROP TRIGGER [dbo].[Tracker]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[Tracker]
ON [dbo].[TableToTrack]
AFTER INSERT, UPDATE, DELETE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @TableName varchar(900);
 select @tablename = object_schema_name(parent_id) + '.' + object_name(parent_id) 
 from sys.triggers where object_id = @@PROCID
	--Determine if this is an INSERT,UPDATE, or DELETE Action or a "failed delete".
	DECLARE @Action as char(1);
 SET @Action = (CASE WHEN EXISTS(SELECT * FROM INSERTED)
 AND EXISTS(SELECT * FROM DELETED)
 THEN 'U' -- Set Action to Updated.
 WHEN EXISTS(SELECT * FROM INSERTED)
 THEN 'I' -- Set Action to Insert.
 WHEN EXISTS(SELECT * FROM DELETED)
 THEN 'D' -- Set Action to Deleted.
 ELSE NULL -- Skip. It may have been a "failed delete". 
 END)
	IF (@Action IS NOT NULL)
	BEGIN
		DELETE FROM dbo.ChangeTracking WHERE TableName = @TableName;
		INSERT INTO dbo.ChangeTracking(TableName) VALUES (@TableName);	
	END
END

GO

ALTER TABLE [dbo].[TableToTrack] ENABLE TRIGGER [Tracker]
GO