September 11, 2007 at 12:47 pm
Hello,
What I'm trying to do is this: Create an INSERT trigger that, for every column in every row inserted, creates a row in a table I'll just call 'audit'. Example:
Suppose I have a table called 'my_table' with the columns ('id', 'value', 'created_when'). I have a table called 'audit' with the columns ('id', 'source_id', 'column_name', 'value'), and I run the following command:
INSERT my_table SELECT 1, 'value1', '1/1/2007' UNION ALL SELECT 2, 'value2', '1/2/2007'
Supposing that 'id' columns are identities, I want my INSERT trigger to subsequently insert the following rows my 'audit' table:
1, 1, 'id', 1
2, 1, 'value', 'value1'
3, 1, 'created_when', '1/1/2007'
4, 2, 'id', 2
5, 2, 'value', 'value2'
6, 2, 'created_when', '1/2/2007'
In other words, for every field I insert into 'my_table', I want a row in my 'audit' table that lists the name of the field inserted along with that field's new value.
How can I do this without a cursor or dynamic SQL? My opinion is that I need a way to transpose the column names into rows, and I don't see a clean way to do this. I looked into PIVOT but didn't find a good solution there either.
Please don't be concerned that i'm mixing identities with strings and dates; assume I have another way to handle the conversions.
Thanks.
September 11, 2007 at 1:39 pm
Hi Steve,
have you looked at the UNPIVOT operator?
Regards,
Andras
September 12, 2007 at 6:39 am
Here is a version that shows the information you need, you just need to dump the output to your audit table:
CREATE TRIGGER auditTrigger_my_table ON my_table
AFTER INSERT
AS
BEGIN
SELECT originalId
, column_name
, column_value
FROM ( SELECT id AS originalId
, CAST(id AS VARCHAR(100)) AS id
, CAST(value AS VARCHAR(100)) AS value
, CAST(created_when AS VARCHAR(100)) AS created_when
FROM inserted
) p UNPIVOT ( column_name FOR column_value IN ( id, value, created_when ) )AS unpvt
END
The result is
originalId column_name column_value ----------- ---------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- 2 2 id 2 value2 value 2 Jan 2 2007 12:00AM created_when 1 1 id 1 value1 value 1 Jan 1 2007 12:00AM
Regards,
Andras
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply