continue handler question

Bill Smith

I cannot get a 'continue' handler to work within a 'before insert'
trigger....The line
set nnn.name='Bill';
never gets executed. If I do an insert on the table, I get the 99001 error
I also tried a
DECLARE CONTINUE HANDLER FOR SQLSTATE '99001'

Any ideas? ( I the code here in manually, sorry for any simple syntax
errors...)

----------------------------
--#Set delimiter !
drop trigger test1!
drop table tbl1!
create table tbl1(name as char(10) not null)!

create trigger test1
no cascade before insert on tbl1
referencing new as nnn
for each row mode db2sql
BEGIN atomic
DECLARE SQLCODE INTEGER DEFAULT 0 ;
DECLARE errorCode integer;
DECLARE x INTEGER;

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND
SET errorCode = SQLCODE;

signal sqlstate '99001' set message_text = 'test';
set nnn.name='Bill';
END!

insert into tbl1 values('xxx')!
select * from tbl1!

Nov 12 '05 #1

Serge Rielau

Bill,

inline SQL PL does not support any handlers.
What you can do in V8.2 is CALL out to a stored procedure.
There you can do all the nasty things you want.
Note that the before trigger enforced READS SQL DATA though :-)

Cheers
Serge

Nov 12 '05 #2

Bill Smith

Thanks, Bill
"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:2t*************@uni-berlin.de...

Bill,

inline SQL PL does not support any handlers.
What you can do in V8.2 is CALL out to a stored procedure.
There you can do all the nasty things you want.
Note that the before trigger enforced READS SQL DATA though :-)

Cheers
Serge

Nov 12 '05 #3