Re: Raising a user defined exception in a trigger

[John English]

On 31/07/2013 00:44, tim.wickett wrote:
I have searched the forum and although I can find something similar it didn't
really seem to solve my problem.
CREATE TRIGGER insert_waste_container
     NO CASCADE BEFORE INSERT ON waste_container
     REFERENCING NEW AS new_waste_container
     FOR EACH ROW MODE DB2SQL
     WHEN (new_waste_container.contains_haz_waste IS TRUE
         AND new_waste_container.contains_rad_waste IS TRUE)
         BEGIN ATOMIC
             DECLARE invalid_container_row
                 EXCEPTION FOR SQLSTATE 38000;
             SIGNAL invalid_container_row;
         END;

I have copied this trigger from another DB and tried to correct but I cannot
get it to work. From the reference docs i'm not even sure if I can do this?

The simplest solution IMHO is to have the trigger call a user-defined procedure 
which throws a custom exception. In your Java code:

  public static final void goBang () throws MyException {
    throw new MyException();
  }

In your DB:

CREATE PROCEDURE go_bang()
  PARAMETER STYLE Java
  LANGUAGE Java DETERMINISTIC NO SQL
  EXTERNAL NAME 'myfunctions.goBang';

CREATE TRIGGER insert_waste_container
  NO CASCADE BEFORE INSERT ON waste_container
  CALL go_bang();

--
John English