Re: Raising a user defined exception in a trigger

[John English]

On 31/07/2013 14:57, John English wrote:
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();

Or to be more precise: if you want a row trigger, make it a function rather than 
a procedure. (This solution assumes that your columns "contains_haz_waste" and 
"contains_rad_waste" are defined in Derby as BOOLEAN.)

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

In your DB:

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

  CREATE TRIGGER insert_waste_container
     NO CASCADE BEFORE INSERT ON waste_container
     REFERENCING NEW AS new_waste_container
     FOR EACH ROW MODE DB2SQL
     UPDATE waste_container
     SET new_waste_container.contains_haz_waste = go_bang()
     WHERE (new_waste_container.contains_haz_waste
            AND new_waste_container.contains_rad_waste)
--
John English