db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dag H. Wanvik" <dag.wan...@oracle.com>
Subject Re: Raising a user defined exception in a trigger
Date Thu, 01 Aug 2013 09:41:19 GMT
Derby doesn't support SQL/PL style trigger actions. You can package 
custom actions in a Java stored procedure or function, though, and
call it in the trigger action. Cf.

http://db.apache.org/derby/docs/10.10/ref/rrefsqlj43125.html#rrefsqlj43125__i1149821

which has an example of  trigger action calling a stored function:

*CREATE TRIGGER t1 NO CASCADE BEFORE UPDATE ON x
   FOR EACH ROW MODE DB2SQL
   values app.notifyEmail('Jerry', 'Table x is about to be updated');*

You could let your routine do the check and then throw an SQL exception.

Hope this helps,
Dag


On 31/07/13 07: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?
>
> Thanks
>
> Tim
>
>
>
> --
> View this message in context: http://apache-database.10148.n7.nabble.com/Raising-a-user-defined-exception-in-a-trigger-tp132927.html
> Sent from the Apache Derby Users mailing list archive at Nabble.com.


Mime
View raw message