db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From George Baklarz <bakl...@ca.ibm.com>
Subject Re: [jira] Commented: (DERBY-172) Trigger activation
Date Fri, 11 Mar 2005 16:20:44 GMT
Thanks for the update. I was curious why it was supporting the syntax
(well, close to it). The SYSTRIGGERS catalog table obviously had a slot for
this type of trigger. I would have preferred the ability to change column
values before having the statement execute though. Is there anything I can
do from a logic perspective in a BEFORE trigger like a WHEN clause? Or does
it always execute the statement:

CREATE TRIGGER XXX NO CASCADE BEFORE INSERT ON A
  REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW
 ... SELECT or VALUES...

A SET NEW.column = xxx would have been nice.

George
_______________________________________________________________________________________________________

George Baklarz | Senior Manager, Worldwide DB2 Sales Support | (+1)
905-413-2798 |  T/L 969-2798 | baklarz@ca.ibm.com


                                                                           
             "Daniel John                                                  
             Debrunner (JIRA)"                                             
             <derby-dev@db.apa                                          To 
             che.org>                  George Baklarz/Toronto/IBM@IBMCA    
                                                                        cc 
             03/10/2005 09:11                                              
             PM                                                    Subject 
                                       [jira] Commented: (DERBY-172)       
                                       Trigger activation                  
                                                                           
                                                                           
                                                                           
                                                                           
                                                                           
                                                                           




     [
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




Mime
View raw message