November 5, 2004 at 7:05 am
Hi,
Would anybody know if and how it is possible to exploit the "Inserted" and "Deleted" conceptual tables from within "After Insert" or "After Delete" triggers in T/SQL ?
My goal is to write a generic, reusable trigger that copies changed data together with the name of the table to a log.
It should probably look something like this :
Create trigger [CopyToLog] on myTable After Insert as
Insert Into myLog Select * from Inserted.TableName
This would allow me not to have to hardcode any of the TableNames, as I would like to use this trigger for most of the tables in my database ...
Thanks, Garry
November 5, 2004 at 8:08 am
Garry,
No, you cannot do that; the INSERTED and DELETED tables will have the same columns and datatypes as whatever table the trigger fired for.
--
Adam Machanic
whoisactive
November 5, 2004 at 8:46 am
Hi Garry,
I have done some work (with help from these forums) recently using a history table through a trigger on the live table.
On first entry of data to the table, a mirror table is created with OLD appended to the table name. On every insert or update, values are copied in along with modified date time and modified by (USER).
Not sure if this is what you are trying to accomplish but ff this will help I will post the trigger code.
Have fun
Steve
We need men who can dream of things that never were.
November 5, 2004 at 1:57 pm
Steve, Adam,
Thanks for the fast reply ! It's highly appreciated.
And I believe Adam is right; I can't seem to find any details on the use of the Inserted and Deleted tables. And although I understand and I am able to use them as described in e.g. the Create Trigger statement, but I would like to make the trigger code as generic as possible; consider this :
CREATE TRIGGER [DmlScript_Generator] on [UpdatedTable] After Insert
As
Insert Into DmlLog
Values (@@UpdatedTable, GetDate())
This will add a record to DmlLog ("DataManipulationLanguage"-Log) for every record added to [UpdatedTable]. Imagine I have 100 tables that I want to have the same trigger ... I can copy/paste the code but it would be nice not to have to change the @@UpdatedTable for every Table... So, it would be nice if the trigger would be so intelligent to be able to expose the name of the table to which it was applied ... if you see what I mean.
But Steve, I would still be very happy to learn how you implemented your logger; so if you would still want to send me your trigger code ?
Thanks a lot. Garry
November 8, 2004 at 4:23 am
Hi Garry,
Just remember this is still under development 😉
Stored procedure, pass it the name of the table you want the trigger on. Creates a table called audit_(your table name) on first insert of data. Adds columns Action, Modified By and Modified Date to the Audit table.
Trigger copies values across on Insert, Update and Delete.
CREATE PROCEDURE [sproc_CreateTrigger]
@TableName varchar(250)
AS
DECLARE @TableRealName sysname,
@SQL varchar(8000)
SET @TableRealName = @TableName
BEGIN TRAN
SET @SQL = 'CREATE TRIGGER TRG_' + @TableRealName + '
ON dbo.' + @TableRealName + '
FOR DELETE,INSERT,UPDATE
AS
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_' + @TableRealName + ']'') AND OBJECTPROPERTY(ID,
N''ISUSERTABLE'') = 1)
BEGIN
DECLARE @MEMTABLE TABLE
(
ID INT IDENTITY
,COLUMNAME SYSNAME
,TYPENAME VARCHAR(20)
)
INSERT @MEMTABLE
(COLUMNAME,TYPENAME)
SELECT NAME,TYPE_NAME(XTYPE)
FROM SYSCOLUMNS
WHERE ID = OBJECT_ID(''[DBO].[' + @TableRealName + ']'')
ORDER BY COLID
DECLARE @CUR INTEGER
DECLARE @max-2 INTEGER
DECLARE @SQLSTR AS VARCHAR(8000)
DECLARE @CURCOL SYSNAME
DECLARE @COLTYPE AS VARCHAR(10)
SET @SQLSTR = ''''
SET @CUR=1
SELECT @max-2 = MAX(ID) FROM @MEMTABLE
WHILE @CUR <= @max-2
BEGIN
SELECT @CURCOL = COLUMNAME,@COLTYPE = TYPENAME FROM @MEMTABLE WHERE ID = @CUR
IF @COLTYPE = ''INT'' OR @COLTYPE = ''BIGINT'' OR @COLTYPE=''UNIQUEIDENTIFIER''
SET @SQLSTR = @SQLSTR + '' CAST(''+@CURCOL + '' AS ''+@COLTYPE+'') AS ['' + @CURCOL +''] ''
ELSE
SET @SQLSTR = @SQLSTR + '' ''+@CURCOL + '' AS ['' + @CURCOL +''] ''
IF @CUR <= @max-2 - 1 SET @SQLSTR=@SQLSTR + '',''
SET @CUR = @CUR + 1
END
SET @SQLSTR = @SQLSTR +'',CAST('''' '''' AS CHAR(6)) AS Action,CAST(GETDATE() AS DATETIME) AS ModifiedDate, CAST(USER AS VARCHAR(100)) AS ModifiedBy''
SET @SQLSTR = ''SELECT TOP 0 '' + @SQLSTR + '' INTO [DBO].[AUDIT_' + @TableRealName + '] FROM [DBO].[' + @TableRealName + ']''
EXEC(@SQLSTR)
END
IF @ACT = ''INSERT'' INSERT [DBO].[AUDIT_' + @TableRealName + '] SELECT *, ''INSERT'', GETDATE(), USER FROM INSERTED
IF @ACT = ''DELETE'' INSERT [DBO].[AUDIT_' + @TableRealName + '] SELECT *, ''DELETE'', GETDATE(), USER FROM DELETED
IF @ACT = ''UPDATE'' INSERT [DBO].[AUDIT_' + @TableRealName + '] SELECT *, ''UPDATE'',GETDATE(), USER FROM INSERTED'
EXEC (@SQL)
COMMIT TRAN
GO
Best Regards
Have fun
Steve
We need men who can dream of things that never were.
November 8, 2004 at 8:36 am
Brilliant Steve.
It is not exactly the way I would have liked it, but I now know it is the closest I am going to get.
Thanks a bunch for the excellent effort ! I'll buy you a beer in the "Farmers Arms" when I am in the neighborhood.
Kind Regards. Garry
November 8, 2004 at 8:39 am
No worries Garry,
Chop it up and use the bits that are relevant.
I'm always available at The Farmers when i'm not at work mate.
Best regards
Steve
We need men who can dream of things that never were.
November 8, 2004 at 8:48 am
Garry,
Just a thought. Can you not incorporate xp_cmdshell into the dynamic sql in the trigger?
Straight out of BOL:
This example writes the contents of the @var variable to a file named var_out.txt in the current server directory.
DECLARE @cmd sysname, @var sysnameSET @var = 'Hello world'SET @cmd = 'echo ' + @var + ' > var_out.txt'EXEC master..xp_cmdshell @cmdI'm sure we could work this in given a little time and thought....... Just a bit busy doing 'paid' work at the moment - if you know what i mean......;-)Steve
We need men who can dream of things that never were.
November 8, 2004 at 8:57 am
I'll look into the xp_cmdshell issue.
But anyway, I am perfectly happy with your suggested solution ! So, no hurry.
Cheers.
Garry
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply