db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Satheesh Bandaram <sathe...@Sourcery.Org>
Subject Re: [jira] Commented: (DERBY-172) Trigger activation
Date Fri, 11 Mar 2005 18:11:40 GMT
I personally think able to change values in BEFORE triggers would be
great to have. A SET new.column = xxx would be nice, but that falls
under SQL Stored Procedure language that Derby doesn't have. Commercial
databases like DB2 has DB2 SQL Procedure language, Oracle has PL/SQL and
Informix has support for SPL. It would be nice to add that capability to
Derby.

You can code your trigger logic in Derby Java functions, but Derby only
supports read-only operations in BEFORE triggers.

Satheesh

George Baklarz wrote:

>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