Hello,

to circumvent the text-, ntext-, image- and timestamp-problem i converted this into a template, so with ctrl-shift-m i can insert the proper table name:

-- 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-2 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-2 = MAX(ID) FROM @MEMTABLE

-- LOOP EVERY FIELD

WHILE @CUR <= @max-2

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-2 - 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-2 - 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)

Note: since i am using this trigger for debugging purposes i added a second insert for the update to get the before-values...