|
View previous topic :: View next topic
|
| Author |
Message |
David Joyce
New User
Joined: 08 Aug 2008
Posts: 17
Location: Boulder Colorado
|
|
|
|
The trigger I am attempting to write checks a WHEN condition, if true I want to perform a SELECT statement and then interrogate the SQLSTATE of the SELECT statement performing either an INSERT or UPDATE based upon the SQLSTATE. Here is error I receive when I try to create the trigger in SPUFI:
| Code: |
DSNT408I SQLCODE = -20100, ERROR: AN ERROR OCCURRED WHEN BINDING A TRIGGERED
SQL STATEMENT. INFORMATION RETURNED: SECTION NUMBER : 3 SQLCODE
-104, SQLSTATE 42601, AND MESSAGE TOKENS CMSDBT01,ELSE CONCAT || /
MICROSECONDS MICROSECOND SECONDS SECOND |
Here is the trigger DDL:
| Code: |
--#SET TERMINATOR ?
SET CURRENT SQLID = 'DB2DBA'?
CREATE TRIGGER CMSDBT01.TESTTBL1_DELETE
AFTER DELETE ON CMSDBT01.TESTTBL1
REFERENCING OLD AS O
FOR EACH ROW
MODE DB2SQL
WHEN (O.COL3 > CURRENT TIMESTAMP - 1 MONTH)
BEGIN ATOMIC
SELECT 1
FROM CMSDBT01.TESTTBL2
WHERE COL1 = O.COL1;
VALUES CASE
WHEN SQLSTATE = '00000' THEN
UPDATE CMSDBT01.TESTTBL2
SET TRIGGER_TS = CURRENT TIMESTAMP
WHERE COL1 = O.COL1
WHEN SQLSTATE = '02000' THEN
INSERT INTO CMSDBT01.TESTTBL2
VALUES (O.COL1, CURRENT TIMESTAMP)
ELSE
SIGNAL SQLSTATE 'Z0001'
SET MESSAGE_TEXT = 'PROBLEM WITH CMSTB467'
END;
END? |
Code'd
Any help is greatly appreciated. Thanks. |
|
| Back to top |
|
 |
David Joyce
New User
Joined: 08 Aug 2008
Posts: 17
Location: Boulder Colorado
|
|
|
|
Could not figure out how to interrogate SQLSTATE from previous SQL SELECT statement so I changed the trigger to:
| Code: |
--#SET TERMINATOR ?
SET CURRENT SQLID = 'DB2DBA'?
--DROP TRIGGER CMSDBT01.TESTTBL1_DELETE;
--COMMIT;
CREATE TRIGGER CMSDBT01.TESTTBL1_DELETE
AFTER DELETE ON CMSDBT01.TESTTBL1
REFERENCING OLD AS O
FOR EACH ROW
MODE DB2SQL
WHEN (O.COL3 > CURRENT TIMESTAMP - 1 MONTH)
BEGIN ATOMIC
DELETE
FROM CMSDBT01.TESTTBL2
WHERE COL1 = O.COL1;
INSERT INTO CMSDBT01.TESTTBL2
VALUES (O.COL1, CURRENT TIMESTAMP);
END?
--#SET TERMINATOR ; |
Code'd |
|
| Back to top |
|
|
gylbharat
Active Member
.jpg)
Joined: 31 Jul 2009
Posts: 565
Location: Bangalore
|
|
|
|
| I think you can not do this in Values clause.... You can call a stored procedure from the trigger and there you can do all the checks. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
You need to use the Code tag when posting code, jcl, data, etc. Doing a copy/paste and applung the Code tag will preserve alignment and improve readability.
There is a Preview function so you can wee your post as it will appear to the forum. When the post appeara as you wish, Submit. |
|
| Back to top |
|
|
|
|
