Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2003
    Posts
    9

    Unanswered: Trigger inserted/deleted questions (simple, I think…)

    I'm probably being thick here, but here goes…

    For better or worse, I have to detect changes in two fields on two different tables, using update triggers.

    Table #1 is easy - it carries a unique serial number field, meaning I can do:

    select
    inserted.blahlahblah,
    deleted.blahlahblah
    from inserted
    inner join deleted on inserted.uniqueserialnumber = deleted.uniqueserialnumber

    and the job's a good'un…

    However, table #2 (as I see it) is more tricky because there is no serial field, and in fact the two fields I need to log changes to are the two fields which uniquely define the record.

    Hence I cannot link 'inserted' to 'deleted' on their two 'key' fields (I use the term loosely) - since they may not be the same 'before' and 'after' the update.

    *** My #1 question is, then: how do I link 'inserted' and 'deleted' in this instance? ***

    I presume I could just *assume* a single record in each table and just join them with no 'where' clause but this *feels* bad to me: *nominally* I would write triggers of this type with a cursor to cycle through 'inserted'/'deleted', to cater for bulk updates (despite their rarity on the ERP system I'm dealing with).

    Sooooo.....

    * Am I missing something crucial about 'inserted' / 'deleted'?

    * Am I doing anything 'wrong' (or inefficient) by assuming that there may be more than single records in these tables?

    * Assuming I am not wrong in catering for multiple updates, all I can think of is to manually add a unique id to each record read from each table, and go on the basis that the sequence of records in each is identical (thus *inferring* the link).

    * Is there anywhere I can read up on 'best practice' in this area -or- an explanation in more detail of how 'inserted' / 'deleted' actually work (particularly with regards to 'en masse' updates) since BOL seems pretty basic?

    Many, many thanks, o wise ones!

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    add a surrogate key and be done with it.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Feb 2003
    Posts
    9

    Surrogate key

    LOL. Thanks for the suggestion but changing the base tables is out of the question. It's hard enough to get any kind of system change approved here... (Oh, the changes I would make if it weren't so...).

    pmb

  4. #4
    Join Date
    Feb 2003
    Posts
    9

    Well, this seems to work... Anything wrong wilth it?

    Just been playing, and the following seems to work.
    Can I rely on using this kind of logic?
    pmb

    -------------------------------->

    -- Clean install

    if exists (select * from sysobjects where id = object_id('test'))
    drop table test
    go

    if exists (select * from sysobjects where id = object_id('test_log'))
    drop table test_log
    go

    if exists (select * from sysobjects where id = object_id('log_it') and OBJECTPROPERTY(id, 'IsTrigger') = 1)
    drop trigger log_it
    go

    -- Create table to trigger from

    create table test
    (
    a int,
    b int,
    c int
    )
    go

    -- Create table to log to

    create table test_log
    (
    a_old int,
    b_old int,
    a_new int,
    b_new int,
    logdate datetime default getdate()
    )
    go

    -- Create trigger to log changes to 'a' or 'b'

    create trigger log_it
    on test
    after update
    as
    begin

    declare @inserted_ser table -- serialise 'inserted'
    (
    log_serno_i int identity(1,1) not null,
    log_a_i int,
    log_b_i int
    )

    declare @deleted_ser table -- serialise 'deleted'
    (
    log_serno_d int identity(1,1) not null,
    log_a_d int,
    log_b_d int
    )

    insert into @inserted_ser -- copy 'inserted'
    (
    log_a_i,
    log_b_i
    )
    select a,b from inserted

    insert into @deleted_ser -- copy 'deleted'
    (
    log_a_d,
    log_b_d
    )
    select a,b from deleted

    insert into test_log -- link 'inserted' and 'deleted' via serial number
    ( -- and dump to 'log_test' where either/both of the 2 fields 'a'/'b' differ
    a_old,
    b_old,
    a_new,
    b_new
    )
    select log_a_d, log_b_d, log_a_i, log_b_i
    from @inserted_ser inner join @deleted_ser on log_serno_d = log_serno_i
    where ((log_a_d!= log_a_i) or (log_b_d != log_b_i))

    end

    return
    go

    -- Set up a few test values

    insert into test(a,b,c) values (1,1,0)
    insert into test(a,b,c) values (1,2,0)
    insert into test(a,b,c) values (2,1,0)
    insert into test(a,b,c) values (2,2,0)

    -- Test logging

    -- No update
    update test set a = 10 where a = 99 and b = 99
    select a_old, a_new, b_old, b_new, logdate from test_log

    -- Update but not to 'key' field
    update test set c = 10 where a = 1 and b = 1
    select a_old, a_new, b_old, b_new, logdate from test_log

    -- Single record update
    update test set a = 10 where a = 1 and b = 1
    select a_old, a_new, b_old, b_new, logdate from test_log

    -- Update 2 rows
    update test set a = 10 where a = 2
    select a_old, a_new, b_old, b_new, logdate from test_log

    -- No where clause, update everything
    update test set a = 20
    select a_old, a_new, b_old, b_new, logdate from test_log

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    you answer your own question. my only question is why you need the table variables.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  6. #6
    Join Date
    Feb 2003
    Posts
    9

    Why oh why

    Why, indeed... Basically to prove that our software isn't doing something the customer says it is...

    Quote Originally Posted by Thrasymachus View Post
    you answer your own question. my only question is why you need the table variables.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •