Table audit log

				
--**************************************
-- Name: Table audit log
-- Description:This shows how you can create a trigger on a table that will show what is happening in your tables. This shows that you can log the original value before change, the new value after change, who changed it and when. This sample is based on the authors table in the Pubs db.
-- By: JesseLeon
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=721&lngWId=5--for details.--**************************************

/* This is the sample Log table*/
CREATE TABLE [LogTable] (
	[LogID] [int] IDENTITY (1000, 1) NOT NULL ,
	[FromLine] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[RECID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	CONSTRAINT [PK_LogTable] PRIMARY KEY CLUSTERED 
	(
		[LogID]
	) ON [PRIMARY] 
) ON [PRIMARY]
GO
/* This is the sample trigger to be added to the authors table in the Pubs db*/
CREATE TRIGGER [LogTrig] ON [dbo].[authors] 
FOR INSERT, UPDATE, DELETE
AS
declare @Myvar char(50), @Myvar2 Char(50)
set @myvar = (SELECT au_id FROM deleted)
set @Myvar2 = (Select State from authors where au_id = @myvar)
INSERT INTO LogTable 
(fromLine,RECID)
SELECT 'Changed State from ' + isnull(state,'<NULL>') + ' to ' + isnull(rtrim(@MyVar2),'<NULL>') + ' by ' + system_user + ' on ' + convert(char,getdate()),au_id
FROM deleted