Post reply

How to set an insert value based whether a change was made to a field ?

  • November 20, 2008 at 8:11 pm

    #96727

    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

    #901400

    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

    #901402

    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

    #901424

    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