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

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...
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.

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?

@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 .