Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2012
    Posts
    5

    Unanswered: Need help with DB2 trigger

    Hi,

    Could anyone please tell me how to do an inner join from the table that is the subject of a trigger.

    The Sybase equivalent is:

    CREATE TRIGGER upd_table on TRIGGERTABLE
    FOR UPDATE AS BEGIN

    update UPDATEDTABLE
    set column1 = new.column1
    , column2 = leftjointable.column2
    where inserted new
    , LEFTJOINTABLE leftjointable
    , UPDATEDTABLE updatedtable
    where new.id = updatedtable.id
    and new.id *= leftjointable.id

    END
    go.

    However, since there is no concept of inserted in DB2, the trigger has only a "left outer join LEFTJOINTABLE" with no from clause and does not compile.

    So how would the above statement translate into an AFTER UPDATE trigger in DB2?

    Many thanks.
    Last edited by redridingfraggle; 03-16-12 at 07:20. Reason: syntax error

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Is this what you want?
    Code:
    create trigger t 
    after update on TRIGGERTABLE
    referencing new as new
    for each row
    begin atomic
     update UPDATEDTABLE 
     set column1 = new.column1
     , column2 = (
         select leftjointable.column2 
         from LEFTJOINTABLE leftjointable 
         where  leftjointable.id = new.id
       )
    where new.id = updatedtable.id;
    end;
    (not tested)

  3. #3
    Join Date
    Mar 2012
    Posts
    5
    Thanks, that compiles fine, but results in a FULL join to the LEFTJOINTABLE. I'm looking for a left outer join which returns nulls even when there is no match.

    The problem comes in the nested select statement that assigns column2. It would work to say from TRIGGERTABLE left outer join LEFTJOINTABLE on id = id, but the TRIGGERTABLE isn't referred to in the trigger.

    Hence the compile error in the column2 assignment below:

    Code:
    create trigger t 
    after update on TRIGGERTABLE
    referencing new as new
    for each row
    begin atomic
     update UPDATEDTABLE 
     set column1 = new.column1
     , column2 = (
         select leftjointable.column2 
         left outer join    LEFTJOINTABLE leftjointable 
         on                   leftjointable.id = new.id
       )
    where new.id = updatedtable.id;
    end;

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    The subselect will return null if there's no match by ID, no?

    Consider also reading the manual; join requires two tables, not one as you wrote it.

  5. #5
    Join Date
    Mar 2012
    Posts
    5
    Yes left join requires both tables, that's the problem. But one of those tables is the subject of the trigger (after update on TRIGGERTABLE) and not normally specified inside the trigger. So can the trigger table (with the same alias) be written INTO the trigger?

    And yes, if the subselect does not match, it will return a row with a null for the outer joined table. With 2 tables:

    |id|
    |1|
    |2|
    |5|

    |id|
    |1|
    |3|

    A full join (where leftjoin.id = new.id) will return:

    |id|id|
    |1|1|

    Whereas a left outer join on the left most table (TRIGGERTABLE new left outer join LEFTJOINTABLE leftjoin on leftjoin.id = new.id) will return:

    |id|id|
    |1|1|
    |2|null|
    |5|null|

    The left most table however is not normally specified inside the trigger.
    Last edited by redridingfraggle; 03-16-12 at 13:27. Reason: syntax error

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by redridingfraggle View Post

    A full join (where leftjoin.id = new.id) will return:
    This is not a full join - this is an inner join.

    Quote Originally Posted by redridingfraggle View Post
    The problem is that the trigger table is not normally specified in the trigger but aliased as new, so how to write the left outer join query?
    Like I said, you don't need a join in the subselect; it will return null if the ID is not found.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    redridingfraggle,

    If you were still not shure the sample code in #2,
    please see Scalar fullselect near the end of...
    Expressions - IBM DB2 9.7 for Linux, UNIX, and Windows

    Note: Scalar fullselect includes Scalar subselect.

Tags for this Thread

Posting Permissions

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