trigger exceptions

[dsener]

Hi All,

I am a very new beginner to db2 and trying to convert an oracle trigger to a db2 one.

My issue is with the exception used in oracle, "EXCEPTION WHEN OTHERS WHEN". How do I convert or in other terms use the db2 way of handling any exceptions?

This is my code I have in DB2 so far

CREATE TRIGGER LOGS_TRG NO CASCADE 
BEFORE  INSERT  ON xxx_log  
REFERENCING NEW AS N  
FOR EACH ROW  MODE DB2SQL 
BEGIN ATOMIC
	IF(N.ID is null)
	then
	SET N.ID = nextval for xxx_log_id_seq;  
--SIGNAL SQLSTATE '-20000' SET MESSAGE_TEXT='INTERNAL TRIGGER ERROR';
end if;
end!

Any help would be greatly appreciated.

[sakumar9]

You can use EXIT HANDLERS to implement error handling in DB2 stored procedures. For example,

  -- in case of no data found  
  DECLARE EXIT HANDLER FOR NOT FOUND
    SIGNAL SQLSTATE value '38200' SET MESSAGE_TEXT= '100: NO DATA FOUND'; 

  -- in case of SQL error
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
    SIGNAL SQLSTATE value SQLSTATE SET MESSAGE_TEXT = errorLabel;

You can define exit handlers for all error codes that you want to catch. Also you can use generic handlers like the second one in above example.

For more examples, I would suggest you to check the samples at: SQLLIB/samples/sqlproc. You might want to look at spserver.db2

Regards
-- Sanjay

[dsener]

Thank you Sanjay

Will try tis and look at the samples