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 11:57:34 GMT
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

Mime
View raw message