dynamic audit trigger

Hi. I'm trying to create a dynamic audit trigger for a rather large datbase.
the trigger should be able to dynamically read the field names form the
deleted tabel and check each column for updates to store them to a table (i
only want to store fieldnames and values that changed)
the spilified table looks like:
date --the date of the update
field --the updated column
value --the inserted value
I can read column names, from deleted and check for updates by comparing to
inserted. the problem i have is the retrieval of the columns value.
How do i fetch the value of one single column from the deleted table if i
have stored the columns name in a variable as i cannot access deleted via
sp_execute?
this does not work:
select @.fieldName = name from syscolumns where colid=@.field and...
declare @.sql nvarchar(4000);
declare @.params nvarchar(4000);
set @.sql = 'select @.val = cast('+@.fieldname+' as nvarchar(3000)) from
deleted';
set @.params = '@.val nvarchar(3000) OUTPUT';
exec sp_executesql @.sql, @.params, @.val=@.value OUTPUT;
anyone any idea'
thanks so farhow did you check for the updates by comparing with the inserted table.
Can you paste the script for that?|||Hi I think i solved the problem storing the values of deleted to a temporary
table. not quite what i wanted but it works.
here's the script i use anyway
CREATE TRIGGER dbo.mytable_trigger ON dbo... FOR UPDATE
AS
DECLARE @.bit INT,
@.field INT,
@.char INT,
@.fieldName NVARCHAR(50),
@.modificationDate DATETIME,
@.contextId BIGINT,
@.value NVARCHAR(3000),
@.value2 NVARCHAR(3000),
@.maxCols INT
SET @.field = 0;
--the modification date is used to indicate date and time the update occured
SET @.modificationDate = GETDATE();
--DROP THE TEMPORARY TABLE WHICH STORES THE VALUES FROM deleted IF IT EXISTS
IF EXISTS(SELECT id FROM sysobjects WHERE NAME='tmpDel') DROP TABLE tmpDel;
--STORE VALUES FROM updated COLS in a temporary table
SELECT * INTO tmpDel FROM deleted;
--iterate throug all columns of the source table and check the values
against the deleted table to
--find the cols updated
--store the cols to a history table using a stored procedure
WHILE @.field < (SELECT MAX(colid) FROM syscolumns WHERE id = (SELECT id FROM
sysobjects WHERE name = 'mytable')) BEGIN
--DEBUG OUTPUT
--print 'field: '+cast(@.field as nvarchar(50));
--calculate the field id's (for details refer to:
http://msdn2.microsoft.com/de-de/library/ms186329.aspx)
SET @.field=@.field+1;
SET @.bit = (@.field-1)%8+1;
SET @.bit = power(2, @.bit-1);
SET @.char = ((@.field - 1) / 8) + 1
--check if a column has been updated and perform the compare operation here
IF substring(COLUMNS_UPDATED(),@.char, 1) & @.bit > 0 BEGIN
SELECT @.field, name FROM syscolumns WHERE colid=@.field AND id = (SELECT id
FROM sysobjects WHERE name = 'mytable')
-- select the ucrrent fieldName
select @.fieldName = name FROM syscolumns WHERE colid=@.field AND id =
(SELECT id FROM sysobjects WHERE name = 'mytable');
--DEBUG OUTPUT
--print 'fieldname: '+@.fieldName
SELECT @.contextId = id FROM deleted;
--DEBUG OUTPUT
--print 'contextid: "'+cast(@.contextId as nvarchar(10));
--as a variable cannot be used to select a col by its name stored in the
variable
--use dynamic sql here.
--the field to select is written by a variable to an sqlString
--within the sql string a variable called @.val is definded as an output
parameter
--to avoid type problems all selected values are casted to nvarchar
--via sp_executesql the statement is executed and the variable assigned.
DECLARE @.sql NVARCHAR(4000);
DECLARE @.params NVARCHAR(4000);
--select the actual value for the field
SET @.sql = 'select @.val = cast('+@.fieldname+' as nvarchar(3000)) from
mytable';
SET @.params = '@.val nvarchar(3000) OUTPUT';
EXEC sp_executesql @.sql, @.params, @.val=@.value OUTPUT;
--select the fields old value
SET @.sql = 'select @.val = cast('+@.fieldname+' as nvarchar(3000)) from
tmpDel';
SET @.params = '@.val nvarchar(3000) OUTPUT';
EXEC sp_executesql @.sql, @.params, @.val=@.value2 OUTPUT;
--if the values do not match the column has been updated
IF(@.value <> @.value2) BEGIN
--calll stored proc to save values to history
EXEC dbo.HistorySave4Table 'dbo.[mytable-History]', @.modificationDate,
@.contextId, null, @.fieldName, @.value2;
END
end
end
--DROP THE TEMPORARY TABLE WHICH STORES THE VALUES FROM deleted IF IT EXISTS
IF EXISTS(SELECT id from sysobjects where name='tmpDel') drop table tmpDel;
"Omnibuzz" wrote:

> how did you check for the updates by comparing with the inserted table.
> Can you paste the script for that?
>