Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2012
    Posts
    24

    Unanswered: Cannot access deleted rows in an update trigger

    Hi,

    I am using DB2 9.7 enterprise edition on Windows 7.

    I have created an update trigger and referencing inserted and deleted table rows. When trying to access the deleted tables, I get error:

    "...includes an invalid use of correlation name or transition table name "DELETED". Reason code="4".. SQLCODE=-696, SQLSTATE=42898, DRIVER=3.62.80"

    Example trigger:

    CREATE TRIGGER TRG_TABLE_UPDATE
    AFTER UPDATE ON TABLE
    REFERENCING OLD TABLE AS DELETED NEW TABLE AS INSERTED
    FOR EACH STATEMENT

    BEGIN

    declaring a lot of variables
    ...

    IF EXISTS (SELECT * FROM DELETED) THEN
    SET V_VARIABLE = "something";
    END IF;

    END!
    ...

    I presume the line "REFERENCING OLD TABLE AS DELETED NEW TABLE AS INSERTED" works the same way as MS SQL Server 2008 references to "inserted" and "deleted", although they don't have to be defined anywhere. Any ideas how to correct my problem above?

  2. #2
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Wrong trigger. You shoot yourself in the foot with this one (sorry, could not resist). In db2 you have separate "UPDATE"/"DELETE" triggers and you need to code a "DELETE" trigger.
    BUT, when you are on fixpack 4 or higher you can combine your triggers for a table in one source. See https://www.ibm.com/developerworks/m...7_4376?lang=en
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

  3. #3
    Join Date
    Aug 2012
    Posts
    24
    Quote Originally Posted by dr_te_z View Post
    Wrong trigger. You shoot yourself in the foot with this one (sorry, could not resist). In db2 you have separate "UPDATE"/"DELETE" triggers and you need to code a "DELETE" trigger.
    BUT, when you are on fixpack 4 or higher you can combine your triggers for a table in one source. See https://www.ibm.com/developerworks/m...7_4376?lang=en
    Are you sure about that? I am pretty sure when creating an UPDATE trigger you can reference to both inserted and deleted tables. This is not true for INSERT and DELETE triggers, however. Here is an example from an official DB2 document:
    CREATE TRIGGER tr_upd_brand
    AFTER UPDATE ON redbooks
    REFERENCING OLD_TABLE AS deleted NEW_TABLE AS inserted
    FOR EACH STATEMENT
    BEGIN ATOMIC
    ...

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by SoftwareEngineer3 View Post
    Hi,

    I am using DB2 9.7 enterprise edition on Windows 7.

    I have created an update trigger and referencing inserted and deleted table rows. When trying to access the deleted tables, I get error:

    "...includes an invalid use of correlation name or transition table name "DELETED". Reason code="4".. SQLCODE=-696, SQLSTATE=42898, DRIVER=3.62.80"
    SQL0696N
    SQL0696N

    The definition of trigger trigger-name includes an invalid use of correlation name or transition table name name. Reason code=reason-code.

    Explanation

    The trigger definition included an invalid use of name. The value of reason-code identifies the roblem:

    ...
    ...

    4
    OLD TABLE name and NEW TABLE name are not allowed in a trigger if the trigger is defined using a compound SQL (compiled) statement.

    The statement cannot be processed.
    Remove compound SQL (compiled) statement.
    You might want to use Compound SQL (inlined) instead of compound SQL (compiled).

    Compound SQL (inlined) - IBM DB2 9.7 for Linux, UNIX, and Windows

  5. #5
    Join Date
    Aug 2012
    Posts
    24
    Quote Originally Posted by tonkuma View Post
    SQL0696N

    Remove compound SQL (compiled) statement.
    You might want to use Compound SQL (inlined) instead of compound SQL (compiled).

    Compound SQL (inlined) - IBM DB2 9.7 for Linux, UNIX, and Windows
    I could create the sql statement as part of a string. That could solve the problem... thanks!

  6. #6
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by SoftwareEngineer3 View Post
    Are you sure about that? I am pretty sure when creating an UPDATE trigger you can reference to both inserted and deleted tables. This is not true for INSERT and DELETE triggers, however. Here is an example from an official DB2 document:
    CREATE TRIGGER tr_upd_brand
    AFTER UPDATE ON redbooks
    REFERENCING OLD_TABLE AS deleted NEW_TABLE AS inserted
    FOR EACH STATEMENT
    BEGIN ATOMIC
    ...
    Can you give me the link? This lookes like code designed to confuse you

    To answer your question: yes I am sure. The "old_table / new_table" is there to answer your other question in this forum. It gives you the old & new column values so you can see which columns are updated.
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

Posting Permissions

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