Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2010
    Posts
    2

    Unhappy Unanswered: Trigger with multiple case and condition

    Hi all,

    I am trying to create a trigger that has 2 or more cases and within each case performs 2 actions insert and delete. I know I have the syntax wrong please help. This is the script that I have created but it gives a syntax 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:
    SQL0104N An unexpected token "VALUES(NEW_INSERT.R_ID,NEW_INS" was found following "TO CONN_HIS". Expected tokens may include:
    "<old_table>". LINE NUMBER=7. SQLSTATE=42601

    TRIGGER IS:

    CREATE TRIGGER CONN_INS_RECORD
    AFTER INSERT ON ID_COL
    REFERENCING NEW AS NEW_INSERT
    FOR EACH ROW MODE DB2SQL
    BEGIN ATOMIC
    SELECT CASE

    WHEN NEW_INSERT.GROUP ='DAD'
    THEN
    ( INSERT INTO CONN_TORY VALUES(NEW_INSERT.RID,NEW_INSERT.CID,NEW_INSERT.AI D,NEW_INSERT.HA_MOD,NEW_INSERT.CONN_TYPE, (select timestamp(date('1970-01-01'),time('00:00:00'))+((NEW_INSERT.P_E_TIME)/1000)second NTMS from sysibm.sysdummy1),CURRENT TIMESTAMP)
    ,

    (DELETE FROM CONN_TORY AS T1 WHERE T1.CID= NEW_INSERT.CID AND T1.P_TYPE =NEW_INSERT.CONN_TYPE AND (SELECT COUNT(*) FROM CONN_TORY WHERE CID =NEW_INSERT.CID and P_TYPE = NEW_INSERT.CONN_TYPE) >(select value from CONN_STAT_FIG_TAB where CONN_TYPE = NEW_INSERT.CONN_TYPE)
    AND T1.PO_TIME =(SELECT MIN(PO_TIME) FROM CONN_TORY as T2 WHERE T2.CID = NEW_INSERT.CID and T2.PO_TYPE= NEW_INSERT.CONN_TYPE GROUP BY CID,PO_TYPE )))

    WHEN NEW_INSERT.GROUP = 'ADD' AND NEW_INSERT.S_SHORT=0

    THEN
    ( INSERT INTO CONN_TORY VALUES(NEW_INSERT.RID,NEW_INSERT.CID,NEW_INSERT.AI D,NEW_INSERT.HA_MOD,NEW_INSERT.CONN_TYPE, (select timestamp(date('1970-01-01'),time('00:00:00'))+((NEW_INSERT.P_E_TIME)/1000)second NTMS from sysibm.sysdummy1),CURRENT TIMESTAMP)
    ,

    (DELETE FROM CONN_TORY AS T1 WHERE T1.CID= NEW_INSERT.CID AND T1.P_TYPE =NEW_INSERT.CONN_TYPE AND (SELECT COUNT(*) FROM CONN_TORY WHERE CID =NEW_INSERT.CID and P_TYPE = NEW_INSERT.CONN_TYPE) >(select value from CONN_STAT_FIG_TAB where CONN_TYPE = NEW_INSERT.CONN_TYPE)
    AND T1.PO_TIME =(SELECT MIN(PO_TIME) FROM CONN_TORY as T2 WHERE T2.CID = NEW_INSERT.CID and T2.PO_TYPE= NEW_INSERT.CONN_TYPE GROUP BY CID,PO_TYPE )))
    END

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    1) What version/release of DB2 and platform are you using?


    2) Did you showed exactly error messages and trigger?
    I couldn't see "TO CONN_HIS"(in the error message) in your trigger.


    3) You should read more carefully the syntax of DB2 SQL.

    For example, some syntax flaws I found were...
    (I saw "DB2 9.7 for LUW SQL Reference Volume 2".)

    3-1) "SELECT CASE ... " has no FROM clause.

    3-2) No (INSERT/DELETE) statement is allowed inside (CASE) expression, even if they are surrounded by parentheses.

    3-3) Looking into syntax diagram of triggered-action...
    triggered-action:
    [WHEN ( search-condition )] [label:] SQL-procedure-statement
    No multiple "WHEN ( search-condition ) SQL-procedure-statement" nor multiple "SQL-procedure-statements" are allowed as triggered-action.
    But, "Compound SQL" is included in "SQL-procedure-statement".
    So, you might be able to specify multiple statements as an "SQL-procedure-statement" by surrounding then with BEGIN and END.
    Last edited by tonkuma; 03-20-10 at 01:26.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Why you need to use multiple "WHEN ... THEN (INSERT ... , (DELETE ... ) )"?
    (Syntax of "WHEN ... THEN (INSERT ... , (DELETE ... ) )" was wrong.)

    It seems both of INSERT/DELETE in two WHEN clause are same, by briefly looking your code.

    How about the following code?
    Code:
    WHEN (NEW_INSERT.GROUP = 'DAD'
          OR
          NEW_INSERT.GROUP = 'ADD' AND NEW_INSERT.S_SHORT=0)
    BEGIN
       INSERT ...;
       DELETE ...;
    END

  4. #4
    Join Date
    Mar 2010
    Posts
    2

    Cool It works, Viola!

    @tonKuma

    Yes I am new to DB2 so my syntax skills are not up to the mark. Thanks for your suggestions.

    I used the "When Clause ",That is exactly what I did. Yes it works!! Thanks a lot for the reply.

    It took me a whole day to figure it out, only if you could have a replied earlier .

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
  •