Create an audit trigger on a table

CREATE TRIGGER TRG_##YOUR_TABLE##
ON [DBO].[##YOUR_TABLE##]
FOR DELETE,INSERT,UPDATE
AS
-- JUST CHANGE ##YOUR_TABLE## INTO YOUR OWN TABLENAME TO MAKE IT WORK 
DECLARE @ACT CHAR(6)
DECLARE @DEL BIT
DECLARE @INS BIT 
DECLARE @SQLSTRING VARCHAR(2000)

SET @DEL = 0
SET @INS = 0

IF EXISTS (SELECT TOP 1 1 FROM DELETED) SET @DEL=1
IF EXISTS (SELECT TOP 1 1 FROM INSERTED) SET @INS = 1 

IF @INS = 1 AND @DEL = 1 SET @ACT = 'UPDATE'
IF @INS = 1 AND @DEL = 0 SET @ACT = 'INSERT'
IF @DEL = 1 AND @INS = 0 SET @ACT = 'DELETE'

IF @INS = 0 AND @DEL = 0 RETURN

IF NOT EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID(N'[DBO].[AUDIT_##YOUR_TABLE##]') AND OBJECTPROPERTY(ID, N'ISUSERTABLE') = 1)
BEGIN
	-- CREATE A MEMORY TABLE CONTAINING THE FIELDS AND TYPES OF THE TABLE
	DECLARE @MEMTABLE TABLE
	( 
		ID INT IDENTITY
		,COLUMNAME SYSNAME
		,TYPENAME VARCHAR(20)
	 )
	-- INSERT THE COLUMNAMES AND THE DATATYPES
	INSERT @MEMTABLE 
		(COLUMNAME,TYPENAME) 
		SELECT NAME,TYPE_NAME(XTYPE) 
		FROM SYSCOLUMNS 
		WHERE ID = OBJECT_ID('[DBO].[##YOUR_TABLE##]') 
		ORDER BY COLID

	DECLARE @CUR INTEGER
	DECLARE @MAX INTEGER
	DECLARE @SQLSTR AS VARCHAR(8000)
	DECLARE @CURCOL SYSNAME
	DECLARE @COLTYPE AS VARCHAR(10)

	-- SETUP VARIABLES
	SET @SQLSTR = ''
	SET @CUR=1
	SELECT @MAX = MAX(ID) FROM @MEMTABLE

	-- LOOP EVEY FIELD
	WHILE @CUR <= @MAX
	BEGIN
		-- GET VALUES FROM THE MEMTABLE	
		SELECT @CURCOL = COLUMNAME,@COLTYPE = TYPENAME FROM @MEMTABLE WHERE ID = @CUR
		IF @COLTYPE = 'INT' OR @COLTYPE = 'BIGINT' OR @COLTYPE='UNIQUEIDENTIFIER'
			-- WE DO WANT TO COPY INT/BIGINT/UNIQUEIDENTIFIER FIELDS BUT IF THEY ARE AN 
			-- IDENTITY OR A ROWGUIDCOLUMN WE DO NOT WANT TO COPY THAT ATTRIBUTES 
			SET @SQLSTR = @SQLSTR + ' CAST('+@CURCOL + ' AS '+@COLTYPE+') AS [' + @CURCOL +'] '
		ELSE
			-- ANOTHER FIELD DO NOTHING JUST COPY IT AS IT IS
			SET @SQLSTR = @SQLSTR + ' '+@CURCOL + ' AS [' + @CURCOL +'] '
		IF @CUR <= @MAX - 1 SET @SQLSTR=@SQLSTR + ','
		SET @CUR = @CUR + 1
	END
	-- ADD THE AUDIT FIELDS
	SET @SQLSTR = @SQLSTR +',CAST(''      '' AS CHAR(6)) AS TRG_ACTION,CAST(GETDATE() AS DATETIME) AS TRG_DATE'
	-- SET UP THE SELECT FOR CREATING THE AUDIT TABLE
	SET @SQLSTR = 'SELECT TOP 0 ' + @SQLSTR + ' INTO [DBO].[AUDIT_##YOUR_TABLE##] FROM [DBO].[##YOUR_TABLE##]'
	EXEC(@SQLSTR)
END

IF @ACT = 'INSERT' INSERT [DBO].[AUDIT_##YOUR_TABLE##] SELECT *,'INSERT' ,GETDATE() FROM INSERTED
IF @ACT = 'DELETE' INSERT [DBO].[AUDIT_##YOUR_TABLE##] SELECT *,'DELETE' ,GETDATE() FROM DELETED
IF @ACT = 'UPDATE' INSERT [DBO].[AUDIT_##YOUR_TABLE##] SELECT *,'UPDATE' ,GETDATE() FROM INSERTED