db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From John English <john.fore...@gmail.com>
Subject Re: Raising a user defined exception in a trigger
Date Wed, 31 Jul 2013 12:13:42 GMT
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

Mime
View raw message