-- 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].[<Table_Name, sysname, Table>]') 
	ORDER BY COLID

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

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

-- LOOP EVERY 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' BEGIN
		-- 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 +'] '
		SET @FIELDS = @FIELDS + ' [' + @CURCOL +']'
		IF @CUR <= @MAX - 1 SELECT @SQLSTR=@SQLSTR + ',', @FIELDS = @FIELDS + ','
	END	
	ELSE IF @COLTYPE <> 'TEXT' AND @COLTYPE <> 'NTEXT' AND @COLTYPE <> 'IMAGE' AND @COLTYPE <> 'TIMESTAMP' BEGIN
		-- ANOTHER FIELD DO NOTHING JUST COPY IT AS IT IS
		SET @SQLSTR = @SQLSTR + ' ['+@CURCOL + ']'
		SET @FIELDS = @FIELDS + ' [' + @CURCOL +']'
		IF @CUR <= @MAX - 1 SELECT @SQLSTR=@SQLSTR + ',', @FIELDS = @FIELDS + ','
	END	
	SET @CUR = @CUR + 1
	
END
-- ADD THE AUDIT FIELDS
-- SET @FIELDS = @FIELDS + ', TRG_ACTION, TRG_DATE, TRG_USER, TRG_PC'
SET @SQLSTR = @SQLSTR +',CAST(''      '' AS CHAR(6)) AS TRG_ACTION,CAST(GETDATE() AS DATETIME) AS TRG_DATE,CURRENT_USER AS TRG_USER, SYSTEM_USER AS TRG_SYSUSER, HOST_NAME() AS TRG_PC'
-- SET UP THE SELECT FOR CREATING THE AUDIT TABLE
SET @SQLSTR = 'SELECT TOP 0 ' + @SQLSTR + ' INTO [DBO].[AUDIT_<Table_Name, sysname, Table>] FROM [DBO].[<Table_Name, sysname, Table>]'

IF NOT EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID(N'[DBO].[AUDIT_<Table_Name, sysname, Table>]') AND OBJECTPROPERTY(ID, N'ISUSERTABLE') = 1)
BEGIN
	PRINT @SQLSTR	
	EXEC(@SQLSTR)
	SET @SQLSTR = 'ALTER TABLE [DBO].[AUDIT_<Table_Name, sysname, Table>] ADD RID BIGINT IDENTITY(1,1)'
	PRINT @SQLSTR	
	EXEC(@SQLSTR)
	SET @SQLSTR = 'ALTER TABLE [DBO].[AUDIT_<Table_Name, sysname, Table>] ADD CONSTRAINT PK_AUDIT_<Table_Name, sysname, Table> PRIMARY KEY ( RID )'
	EXEC(@SQLSTR)
END 

SET @SQLSTR = '
CREATE TRIGGER TRG_<Table_Name, sysname, Table>
ON [DBO].[<Table_Name, sysname, Table>]
FOR DELETE,INSERT,UPDATE
AS
-- JUST CHANGE <Table_Name, sysname, 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 @ACT = ''INSERT'' INSERT [DBO].[AUDIT_<Table_Name, sysname, Table>] (' + ISNULL(@Fields,'-') + ', TRG_ACTION, TRG_DATE, TRG_USER, TRG_SYSUSER, TRG_PC) SELECT ' + ISNULL(@Fields,'-') + ',''INSERT'' ,GETDATE(),CURRENT_USER,SYSTEM_USER,HOST_NAME() FROM INSERTED
IF @ACT = ''DELETE'' INSERT [DBO].[AUDIT_<Table_Name, sysname, Table>] (' + ISNULL(@Fields,'-') + ', TRG_ACTION, TRG_DATE, TRG_USER, TRG_SYSUSER, TRG_PC) SELECT ' + ISNULL(@Fields,'-') + ',''DELETE'' ,GETDATE(),CURRENT_USER,SYSTEM_USER,HOST_NAME() FROM DELETED
IF @ACT = ''UPDATE'' BEGIN 
	INSERT [DBO].[AUDIT_<Table_Name, sysname, Table>] (' + ISNULL(@Fields,'-') + ', TRG_ACTION, TRG_DATE, TRG_USER, TRG_SYSUSER, TRG_PC) SELECT ' + ISNULL(@Fields,'-') + ',''UPDATE'' ,GETDATE(),CURRENT_USER,SYSTEM_USER,HOST_NAME() FROM DELETED
	INSERT [DBO].[AUDIT_<Table_Name, sysname, Table>] (' + ISNULL(@Fields,'-') + ', TRG_ACTION, TRG_DATE, TRG_USER, TRG_SYSUSER, TRG_PC) SELECT ' + ISNULL(@Fields,'-') + ',''UPDATE'' ,GETDATE(),CURRENT_USER,SYSTEM_USER,HOST_NAME() FROM INSERTED
END		
'
PRINT @SQLSTR	
EXEC(@SQLSTR)