Professional Web Applications Themes

Firing a Trigger once for a transaction rather than per statement? - IBM DB2

Hi, This seems like a very fundamental requirement to me, so I guess others have come up against it. We would like some triggers to behave in a transactional context. i.e after an insert into tableA we would like to do some checks of tableB (and vice versa). However, we need to do these checks after tableB has been updated in the same transaction. So, we would like the trigger action to be 'deferred' to the end of the transaction, as we do not know in which order the transaction will update tableA and tableB. Is there any way to ...

  1. #1

    Default Firing a Trigger once for a transaction rather than per statement?

    Hi,

    This seems like a very fundamental requirement to me, so I guess
    others have
    come up against it.

    We would like some triggers to behave in a transactional context.

    i.e
    after an insert into tableA we would like to do some checks of tableB
    (and vice versa). However, we need to do these checks after tableB has
    been updated in the same transaction. So, we would like the trigger
    action to be 'deferred' to the end of the transaction, as we do not
    know in which order the transaction will update tableA and tableB.

    Is there any way to do this?
    - WITHOUT getting into a roll-your-own trigger/transaction
    infrastructure.

    Thanks.

    Paul.
    Paul Reddin Guest

  2. #2

    Default Re: Firing a Trigger once for a transaction rather than per statement?

    Paul,

    were you considering INSTEAD OF triggers? Stored Procedures?
    AK Guest

  3. #3

    Default Re: Firing a Trigger once for a transaction rather than per statement?

    Hi,

    No, I really don't want to get into such infrastructures if it can at
    all be avoided.

    I just want the simlpest way to fire a trigger once and at the end of
    a transaction.
    i.e a per transaction trigger rather than a per row or per
    statement.

    Here's hoping...

    Paul.

    [email]ak_tiredofspam[/email] (AK) wrote in message news:<46e627da.0309150525.2532021dposting.google. com>...
    > Paul,
    >
    > were you considering INSTEAD OF triggers? Stored Procedures?
    Paul Reddin Guest

  4. #4

    Default Re: Firing a Trigger once for a transaction rather than per statement?


    Do you have any correlation like 'last_update_tx_id' in the tables?

    Is this a scenario like :
    If A has been updated, check B.
    If B has been updated, check A.

    when exists() in the trigger?

    Merge statement?

    PM


    "Paul Reddin" <paulabacus.co.uk> a écrit dans le message de
    news:1fd2a603.0309150251.788f8191posting.google.c om...
    > Hi,
    >
    > This seems like a very fundamental requirement to me, so I guess
    > others have
    > come up against it.
    >
    > We would like some triggers to behave in a transactional context.
    >
    > i.e
    > after an insert into tableA we would like to do some checks of tableB
    > (and vice versa). However, we need to do these checks after tableB has
    > been updated in the same transaction. So, we would like the trigger
    > action to be 'deferred' to the end of the transaction, as we do not
    > know in which order the transaction will update tableA and tableB.
    >
    > Is there any way to do this?
    > - WITHOUT getting into a roll-your-own trigger/transaction
    > infrastructure.
    >
    > Thanks.
    >
    > Paul.

    PM \(pm3iinc-nospam\) Guest

  5. #5

    Default Re: Firing a Trigger once for a transaction rather than per statement?

    "PM \(pm3iinc-nospam\)" <Pm3iinc.NoSpamsympatico.ca> wrote in message news:<QSH9b.4483$hF3.614386news20.bellglobal.com> ...
    > Do you have any correlation like 'last_update_tx_id' in the tables?
    No, we don't, but we would definitely like to have an easy way to have this.
    >
    > Is this a scenario like :
    > If A has been updated, check B.
    > If B has been updated, check A.
    Yes/sort of, If A has been updated, check B, but B might yet
    to be updated in the next statement in the transaction.
    >
    > when exists() in the trigger?
    >
    > Merge statement?
    >
    > PM
    >
    >
    Paul Reddin Guest

  6. #6

    Default Re: Firing a Trigger once for a transaction rather than per statement?

    Hi Paul,

    the problem with triggers is they are fired always and immediately.
    The always part you can get around with when and such... not the time
    of execution. Can you in anyway compare the old/new value of table a
    to old/new value of table b ?

    Then you could solve it..
    something down the line of (and this is not exact code):

    create trigger trig1
    after insert/update/delete on tablea
    referencing new as newtba
    for each statement/row
    mode db2sql
    when (exists (select colb
    from tableb
    where colb = newtba.cola)
    begin atomic
    x ;
    yyyy ;
    end

    this way you could check if the changes have allready occurred to the
    other table, if they did execute the trigger (with sql statements x
    and yyyy), if they didn't you don't.

    however remember : it either has to be the value in the table or the
    one that was replaced. You can't work with hostvariables in triggers.

    I hope this helps, or maybe I mis understood the question...let me
    know if I can help.

    best regards,

    Kurt Struyf
    Kurt Struyf Guest

Similar Threads

  1. disable trigger from transaction
    By Postgres General in forum PostgreSQL / PGSQL
    Replies: 5
    Last Post: January 25th, 11:19 AM
  2. WS-Transaction
    By moko in forum ASP.NET Web Services
    Replies: 1
    Last Post: August 13th, 12:25 AM
  3. Transaction
    By basidati in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: August 8th, 03:41 PM
  4. Transaction id and transaction isolation
    By Alex in forum Informix
    Replies: 3
    Last Post: July 23rd, 09:33 PM
  5. advanced: Transaction-logging Trigger
    By vsiat in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: March 18th, 11:34 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139