November 20, 2008 at 8:11 pm
As another part to creating my audit triggers for many tables going into one audit table, is it possible to set a variable based on the base table field being changed.
eg.
if Update(columnname) and update(columnname2)
Insert into dbo.table
( SITE_NAME,
SITE_ID,
RESOLUTION_ID,
PUBLISHED_BY)
select (if update(SITE_NAME) inserted.SITE_NAME else NULL),
(if update(SITE_ID) inserted.SITE_ID else NULL),
(if update(RESOLUTION_ID) inserted.RESOLUTION_ID else NULL),
inserted.Last_updated_by
from inserted .........
where ......
November 20, 2008 at 8:16 pm
First, again, all the code is better than part of the code. In this case, however, I have a question, is this particular question based on a trigger fired only for an UPDATE?
November 20, 2008 at 8:23 pm
Based on what you provided, however, this is where I would start:
insert into dbo.table (
SITE_NAME,
SITE_ID,
RESOLUTION_ID,
PUBLISHED_BY)
select
case when inserted.SITE_NAME <> deleted.SITE_NAME then inserted.SITE_NAME else null end,
case when inserted.SITE_ID <> deleted.SITE_ID then inserted.SITE_ID else null end,
case when inserted.RESOLUTION_ID <> deleted.RESOLUTION_ID then inserted.RESOLUTION_ID else null end,
inserted.Last_updated_by
from
inserted
inner join deleted
on (...)
where ...
November 20, 2008 at 9:29 pm
Thanks Again Lynn
I was going to create one trigger for both inserts and updates, but I think I need to seperate them out due to different audit needs. I'll let you know how i go when I get back to that particular client next week.
Regards Adonia
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply