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...
Best regards
karl