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

    Unanswered: Before update trigger: Trigger is defined with an unsupported triggered SQL statement

    Hi,
    on DB2 v8.2 FP9 on Windows one of applications corrupts data. I know this happens with update statemet. I would like to:
    1. write a trigger to get data before update and write data values to new table and
    2. write a trigger to get data after update and write data in new table

    For step 2. I have writtne trigger:
    Code:
    CREATE TRIGGER ADMIN.TRIG1
    AFTER UPDATE OF COL1, COL2 ON ADMIN.TAB
    REFERENCING NEW AS NEW
    FOR EACH ROW
    BEGIN ATOMIC
         INSERT INTO ADMIN.NEW_TABLE VALUES (NEW.COL1, NEW.COL2);
    END 
    @
    Above trigger works fine. Now I would like to write the same but 'before update' somethink like bellow:

    Code:
    CREATE TRIGGER ADMIN.TRIG1
    BEFORE UPDATE OF COL1, COL2 ON ADMIN.TAB
    REFERENCING NEW AS NEW
    FOR EACH ROW
    BEGIN ATOMIC
         INSERT INTO ADMIN.NEW_TABLE VALUES (NEW.COL1, NEW.COL2);
    END 
    @
    but I get error:
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0797N The trigger "ADMIN.TRIG1" is defined with an unsupported triggered SQL statement. LINE NUMBER=8. SQLSTATE=42987

    Any idea how can I write data to new table to get data from original table before update?

    Regards,
    Grofaty

  2. #2
    Join Date
    Oct 2004
    Location
    DELHI INDIA
    Posts
    338
    for the failure case, just curious, shouldn't it be "REFERENCING OLD AS OLD"

  3. #3
    Join Date
    Jan 2003
    Posts
    1,605
    @JAYANTA_DATTA, I forgot to write that I have also tried the following but getting the same error:
    Code:
    CREATE TRIGGER ADMIN.TRIG1
    BEFORE UPDATE OF COL1, COL2 ON ADMIN.TAB
    REFERENCING OLD AS OLD
    FOR EACH ROW
    BEGIN ATOMIC
         INSERT INTO ADMIN.NEW_TABLE VALUES (OLD.COL1, OLD.COL2);
    END 
    @

  4. #4
    Join Date
    Oct 2004
    Location
    DELHI INDIA
    Posts
    338
    Hi Grofaty,
    May be this note can have the answer:

    "A BEFORE trigger is run before the row is changed. It is typically used to change the values being entered (e.g. set a field to the current date), or to flag an error. It cannot be used to initiate changes in other tables."

  5. #5
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,
    thanks. That is exactly what I was wondering. It looks like this is some kind of DB2 trigger limitation.
    Thanks

  6. #6
    Join Date
    Dec 2005
    Posts
    273
    A "before" trigger can't modify tables.
    INSERT, DELETE and UPDATE are not allowed for an "before" trigger

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    That is not a limitation at all in your case. You can do the logging in the AFTER trigger. You have access to the before and after images via REFERENCES OLD AS ... NEW AS ...
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    Jan 2003
    Posts
    1,605
    @Stolze, thanks a lot. It after update trigger with references new and old is working as expected.

Posting Permissions

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