Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Unanswered: Two triggers fired out with two actions and executing one SQL statement

    Hi,

    I would like to write two triggers which are identical but are triggered with two actions. First one is triggered after insert action and secound one is after insert.

    Can I write secound trigger with somekind of shortcut which is pointing to the SQL from the first trigger? Secound trigger uses the same SQL UPDATE statement (see sample bellow). I would like to have only one SQL statement and two triggers, to have the code of SQL in one place. Can this be done with triggers?

    Sample - trigger 1:
    create trigger db2admin.test_insert after insert on db2admin.test for each statement mode db2sql UPDATE ...

    Sample - trigger 2:
    create trigger db2admin.test_update after update of valueX on db2admin.test REFERENCING OLD AS o FOR EACH ROW mode db2sql UPDATE ...

    Is it posible: create trigger db2admin.test_update after update .... run trigger db2adminl.test_insert?

    My system:
    DB2 v8 FP5 on Windows XP

    Thanks,
    Grofaty

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Grofaty,
    According to the manual, no you cannot do that. This is what is allowed:
    ---------------------------------
    SQL-procedure-statement
    The SQL-procedure-statement can contain a dynamic compound statement or any of the SQL control statements listed in "Compound SQL (Dynamic)".

    If the trigger is a BEFORE trigger, an SQL-procedure-statement can also include one of the following:

    * a fullselect (A common-table-expression may precede a fullselect.)
    * a SET variable statement.

    If the trigger is an AFTER trigger or an INSTEAD OF trigger, an SQL-procedure-statement can also include one of the following:

    * an INSERT SQL statement (not using nicknames)
    * a searched UPDATE SQL statement (not using nicknames)
    * a searched DELETE SQL statement (not using nicknames)
    * 2a MERGE statement
    * a SET variable statement
    * a fullselect (A common-table-expression may precede a fullselect.)

    The SQL-procedure-statement must not contain a statement that is not supported (SQLSTATE 42987).

    The SQL-procedure-statement cannot reference an undefined transition variable (SQLSTATE 42703), a federated object (SQLSTATE 42997), or a declared temporary table (SQLSTATE 42995).

    The SQL-procedure-statement in a BEFORE trigger cannot reference a materialized query table defined with REFRESH IMMEDIATE (SQLSTATE 42997).

    The SQL-procedure-statement in a BEFORE trigger cannot reference a generated column, other than the identity column, in the new transition variable (SQLSTATE 42989).
    -----------------------------

    It would be nice to be able to do something like that from a code maintenance viewpoint. But alas, you will have to have the code in both triggers.

    Andy

  3. #3
    Join Date
    Jan 2003
    Posts
    1,605
    ARWinner,

    Thank you!

    Grofaty

Posting Permissions

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