Results 1 to 12 of 12
  1. #1
    Join Date
    Oct 2011
    Posts
    6

    Unanswered: Need help with a Trigger

    Hi Fellas..I am writing a trigger to insert values before update on one table to log the old records in a history table but the trigger is not getting created
    The trigger statement is given below
    CREATE TRIGGER InsertBefore
    BEFORE UPDATE ON DB2INST1.STATUS
    REFERENCING
    OLD AS o NEW AS n
    FOR EACH ROW MODE DB2SQL
    BEGIN
    IF (o.STATUS <> n.STATUS OR o.TARGET <> n.TARGET) THEN
    Insert into DB2INST1.STATUSHISTORY
    (HISTORYID,
    STATUS,
    TARGET,
    CREATEDDATE)
    VALUES
    (
    SELECT MAX(HISTORYID)+1 FROM DB2INST1.STATUSHISTORY),
    o.STATUS,
    o.TARGET,
    CURRENT TIMESTAMP)
    ELSEIF (O.SUBTARGET <> N.SUBTARGET OR O.SUBSTATUS <> N.SUBSTATUS) THEN
    Insert into DB2INST1.STATUSHISTORY
    (HISTORYID,
    STATUS,
    TARGET,
    CREATEDDATE)
    VALUES
    (
    SELECT MAX(HISTORYID)+1 FROM DB2INST1.STATUSHISTORY),
    o.STATUS,
    o.TARGET,
    CURRENT TIMESTAMP)

    END IF;

    END

    Not sure where I am going wrong. Please help. I am using db29.7 on aix 5.3

    Thanks

    CitiDibi

  2. #2
    Join Date
    Oct 2011
    Posts
    6
    Ok I got to a point where I can create an AFTER TRIGGER but as soon as I change it to BEFORE it throws me an error. Not sure what I am thinking is correct approach. Will wait for you all to recommend. Thanks a lot for any suggestion

    Thanks

    Citidibi

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What error message(s)(including error code and full message text) did you got?

  4. #4
    Join Date
    Oct 2011
    Posts
    6
    Hi there Tonkuma. Thanks for replying to my post
    Below is what I was getting
    SQL0797N The trigger "DB2INST1.INSERTBEFORE" is defined with an unsupported triggered SQL statement. SQLSTATE=42987

    And thats when I tried with AFTER which worked. Do you think I have a workaround?

    Thanks

    Citidibi

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    No semicolons for Insert statements.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I can create an AFTER TRIGGER
    I tried with AFTER which worked
    Please show us complete create statement and the message which showed successful.

  7. #7
    Join Date
    Oct 2011
    Posts
    6
    SQL0104N this is what I got with the semicolon

  8. #8
    Join Date
    Oct 2011
    Posts
    6
    wrote a simple trigger on a test table with when which worked

    CREATE TRIGGER INSERTAFTER
    AFTER UPDATE ON DB2INST1.STATUS
    REFERENCING OLD as O NEW AS N
    FOR EACH ROW
    when (O.TARGET <> N.TARGET )
    Insert into DB2inst1.TEST_HISTORY
    (
    CREATEDDATE)
    VALUES
    (
    CURRENT TIMESTAMP);

  9. #9
    Join Date
    Oct 2011
    Posts
    6
    when the same simple logic is raN with the BEFORE statement, I get this


    CREATE TRIGGER BEFOREINSERT
    BEFORE UPDATE ON DB2INST1.STATUS
    REFERENCING OLD as O NEW AS N
    FOR EACH ROW
    when (O.TARGET <> N.TARGET )
    Insert into DB2inst1.TEST_HISTORY
    (
    CREATEDDATE)
    VALUES
    (
    CURRENT TIMESTAMP);


    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 "DB2INST1.BEFOREINSERT" is defined with an unsupported
    triggered SQL statement. LINE NUMBER=5. SQLSTATE=42987

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

    A BEFORE trigger ... using an SQL compound (compiled) statement can also ...

    SQL0797N

    SQL0797N

    The trigger trigger-name is defined with an unsupported triggered SQL statement.

    Explanation

    The trigger must be defined with a triggered SQL statement that can only include statements from the following lists.

    A trigger can include the following control statements:

    •Compound SQL (compiled) statement
    •Compound SQL (inlined) statement
    •FOR statement
    •GET DIAGNOSTICS statement
    •IF statement
    •ITERATE statement
    •LEAVE statement
    •SIGNAL statement
    •WHILE statement

    ...
    ...

    A BEFORE trigger that is defined using an SQL compound (compiled) statement can also include the following triggered SQL statements:

    •an INSERT statement
    •a searched UPDATE statement
    •a searched DELETE statement
    •a MERGE statement
    •a CALL statement
    •a fullselect
    •an assignment statement
    See this statement.
    A BEFORE trigger...using an SQL compound (compiled) statement can also include ...
    ...
    So, try with adding BEGIN, END(that makes an SQL compound (compiled) statement), and statement-termination-character("@"), like...

    CREATE TRIGGER BEFOREINSERT
    BEFORE UPDATE ON DB2INST1.STATUS
    REFERENCING OLD as O NEW AS N
    FOR EACH ROW
    when (O.TARGET <> N.TARGET )
    BEGIN
    Insert into DB2inst1.TEST_HISTORY
    (
    CREATEDDATE)
    VALUES
    (
    CURRENT TIMESTAMP);
    END@
    Last edited by tonkuma; 10-11-11 at 10:40. Reason: Add notes for "SQL compound (compiled) statement"

  11. #11
    Join Date
    Oct 2011
    Posts
    2
    hi,

    send the value for existing record and new .

  12. #12
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    What fixpack are you at?

Posting Permissions

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