[ http://issues.apache.org/jira/browse/DERBY-172?page=comments#action_60634 ] Daniel John Debrunner commented on DERBY-172: ---------------------------------------------
BEFORE triggers do work but they have the restriction that the trigger action must be read-only, that is a SELECT statement or a VALUES claues. The action statement can call a user defined Java function that could send e-mail or perform validation. Enabling the before triggers was last minute item before the code was contributed. > Trigger activation > ------------------ > > Key: DERBY-172 > URL: http://issues.apache.org/jira/browse/DERBY-172 > Project: Derby > Type: Bug > Components: Documentation > Versions: 10.0.2.0 > Environment: Windows XP SP1 Windows > Reporter: George Baklarz > Priority: Minor > > I image that this is a documentation problem. The "words" around triggers > seem to imply the ability to do actions "BEFORE" the INSERT/UPDATE/DELETE. > For instance: > "Along with constraints, triggers can help enforce data integrity rules with > actions such as cascading deletes or updates. Triggers can also perform a > variety of functions such as issuing alerts, updating other tables, sending > e-mail, and other useful actions." > Well, the CREATE TRIGGER command only allows AFTER as an option (even though > the SYSTRIGGERS catalog uses "B" for before and "A" for after) and it won't > allow a CALL statement to be processed. Seems to me that there aren't any > ways to send an e-mail, issue alerts (no SIGNAL function either), or enforce > integrity rules. How can you enforce integrity rules if invalid data comes > in? I can't force a SIGNAL or error with the trigger, so this is useless for > validating changes to the data. > In addition, there appears to be some logic regarding BEFORE triggers in > Derby, but its not currently working. > CREATE TRIGGER CB NO CASCADE BEFORE > INSERT ON BONUSES > REFERENCING NEW AS n > FOR EACH ROW MODE DB2SQL > UPDATE BONUSES > SET BONUS = > CASE > WHEN (n.BONUS = 0) THEN 100 > ELSE n.BONUS > END; > ERROR 42Z9D: 'UPDATE' statements are not allowed in 'BEFORE' triggers. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - If you want more information on JIRA, or have a bug to report see: http://www.atlassian.com/software/jira
