Re: NOOP in SQL procedure? And other questions

Hi Dan

Very nice - this SQL language just gets better and better.

I like being able to scope the handlers - that will be handy to make a compound statement around a DECLARE GLOBAL TEMPORARY TABLE that will let me have a handler when the table exists - 42710 or some such SQLSTATE.

BTW, do you have my acoustic coupler at the office? I'm coming over for the QUSER meeting.

Later
Vern

----- Original Message -----
Vern,

You can use nested compound statements. Here's one I did. Notice the labeled begin and end of "DELETE_SECTION". I believe in some of the later versions you can also label the LEAVE and ITERATE. So you can have an ITERATE within a nested compound that references a higher level compound.

BTW: This one just deletes all the records that match the id in new_row and replace them with an inserted record built from values in new_row.

Mostly the delete section is made its own compound statement just so I can have a separate handler declared within the delete section. If there's no matching records to delete, the process just continues on to the insert. You'll also see an example of a do-nothing statement in the handler.

-- Generate SQL
-- Version: V5R3M0 040528
-- Generated on: 04/16/13 10:11:10
-- Relational Database: S106E02E
-- Standards Option: DB2 UDB iSeries
CREATE TRIGGER RJSFLOW.WORKPIECECURRENTSTEP
AFTER INSERT ON RJSFLOW.WORKPIECEHISTORY
REFERENCING NEW AS NEW_ROW
FOR EACH ROW
MODE DB2ROW
BEGIN ATOMIC
DECLARE LOCKWAIT_TIMEOUT CONDITION FOR SQLSTATE '57033' ;
DECLARE EXIT HANDLER FOR LOCKWAIT_TIMEOUT INSERT INTO RJSFLOW . TRIGGERLOG ( MESSAGE ) VALUES ( QSYS2 . CONCAT ( 'LOCKWAIT ERROR ' , NEW_ROW . ID ) )
;
DELETE_SECTION :BEGIN
DECLARE AT_END INTEGER DEFAULT 0 ;
DECLARE NOT_FOUND CONDITION FOR SQLSTATE '02000' ;
DECLARE CONTINUE HANDLER FOR NOT_FOUND SET DELETE_SECTION . AT_END = 1 ;
DELETE FROM RJSFLOW . WORKPIECECURRENTSTEP WHERE RJSFLOW . WORKPIECECURRENTSTEP . ID = NEW_ROW . ID ;
END DELETE_SECTION
;
INSERT INTO RJSFLOW . WORKPIECECURRENTSTEP ( ID , CREATEDDATE , CREATEDBY , EVENTINFO , NEXTSTEPID , NEXTSTEPTYPE , NEXTSTEPNAME , PRIORITY )
VALUES ( NEW_ROW . ID , NEW_ROW . CREATEDDATE , NEW_ROW . CREATEDBY , NEW_ROW . EVENTINFO , NEW_ROW . NEXTSTEPID , NEW_ROW . NEXTSTEPTYPE , NEW_ROW . NEXTSTEPNAME , NEW_ROW . PRIORITY ) ;
END ;

Dan Kimmel