Hope someone can help me out here. I have extracted the DDL via Quest Central and it contains many types of triggers, but when I try to execute the BEFORE UPDATE Trigger portion of the DDL, it fails with an SQL0104N An Unexpected Token "END" was found following "'U' END);". Expected tokens may include : "SIGNAL". SQLSTATE=42601.
When I run only one Create Trigger statement it runs fine, but I have hundreds of these to do, so this is extremely time consuming. I tried changing the delimiter, but that didn't seem to help with this Trigger.
I am running Personal Edition 8.2(FP15).
Sample DDL follows:
Code:
SET SCHEMA MARSR;
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","DB2ADMIN";
CREATE TRIGGER "MARSR"."BU_ACR"
NO CASCADE BEFORE
UPDATE
ON "MARSR"."ACCOUNT_PRORATION"
REFERENCING
OLD AS "O"
NEW AS "N"
FOR EACH ROW
MODE DB2SQL
WHEN (O.LST_UPDT_TS = N.LST_UPDT_TS
AND USER <> 'DB2ADMIN'
AND USER <> 'PRODUSER'
AND USER <> 'NOTRIG')
BEGIN ATOMIC
SET (N.LST_UPDT_EMPL_ID, N.LST_UPDT_TS, N.POST_FLG) = (USER, CURRENT
TIMESTAMP,
CASE
WHEN O.POST_FLG IN (' ', 'U')
THEN
CASE N.POST_FLG
WHEN 'D'
THEN 'D'
ELSE 'U'
END
WHEN O.POST_FLG = 'I'
THEN
CASE N.POST_FLG
WHEN 'D'
THEN 'D'
ELSE 'I'
END
WHEN O.POST_FLG = 'D'
THEN
CASE N.POST_FLG
WHEN 'U'
THEN 'U'
ELSE 'D'
END
ELSE 'U'
END ); <===== Fails here
END;
SET SCHEMA MARSR;
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","DB2ADMIN";
CREATE TRIGGER "MARSR"."BU_ACS"
NO CASCADE BEFORE
UPDATE
ON "MARSR"."ACCOUNT_SALE"
REFERENCING
OLD AS "O"
NEW AS "N"
FOR EACH ROW
MODE DB2SQL
WHEN (O.LST_UPDT_TS = N.LST_UPDT_TS
AND USER <> 'DB2ADMIN'
AND USER <> 'PRODUSER'
AND USER <> 'NOTRIG')
BEGIN ATOMIC
SET (N.LST_UPDT_EMPL_ID, N.LST_UPDT_TS, N.POST_FLG) = (USER, CURRENT
TIMESTAMP,
CASE
WHEN O.POST_FLG IN (' ', 'U')
THEN
CASE N.POST_FLG
WHEN 'D'
THEN 'D'
ELSE 'U'
END
WHEN O.POST_FLG = 'I'
THEN
CASE N.POST_FLG
WHEN 'D'
THEN 'D'
ELSE 'I'
END
WHEN O.POST_FLG = 'D'
THEN
CASE N.POST_FLG
WHEN 'U'
THEN 'U'
ELSE 'D'
END
ELSE 'U'
END );
END;
SET SCHEMA MARSR;
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","DB2ADMIN";
CREATE TRIGGER "MARSR"."BU_ACSD"
NO CASCADE BEFORE
UPDATE
ON "MARSR"."ACCT_SPEC_DIST"
REFERENCING
OLD AS "O"
NEW AS "N"
FOR EACH ROW
MODE DB2SQL
WHEN (O.LST_UPD_TS = N.LST_UPD_TS)
BEGIN ATOMIC
SET (N.LST_UPDT_EMPL_ID, N.LST_UPD_tS, N.POST_FLG) = (USER, CURRENT
TIMESTAMP,
CASE
WHEN O.POST_FLG IN (' ', 'U')
THEN
CASE N.POST_FLG
WHEN 'D'
THEN 'D'
ELSE 'U'
END
WHEN O.POST_FLG = 'I'
THEN
CASE N.POST_FLG
WHEN 'D'
THEN 'D'
ELSE 'I'
END
WHEN O.POST_FLG = 'D'
THEN
CASE N.POST_FLG
WHEN 'U'
THEN 'U'
ELSE 'D'
END
ELSE 'U'
END );
END;
Thanks so much for any suggestions!
dBforums
>
Error Creating Multiple Triggers in One Time Jobstream
08-21-08, 13:59
Linear Mode
