You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "George Baklarz (JIRA)" <de...@db.apache.org> on 2005/03/11 01:10:55 UTC

[jira] Created: (DERBY-172) Trigger activation

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

[jira] Updated: (DERBY-172) Improve documentation for BEFORE triggers

Posted by "Kathey Marsden (JIRA)" <ji...@apache.org>.
     [ https://issues.apache.org/jira/browse/DERBY-172?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Kathey Marsden updated DERBY-172:
---------------------------------

    Summary: Improve documentation for BEFORE triggers  (was: Trigger activation)

Changing title to better reflect the user request for better documentation for BEFORE triggers.


> Improve documentation for BEFORE triggers
> -----------------------------------------
>
>                 Key: DERBY-172
>                 URL: https://issues.apache.org/jira/browse/DERBY-172
>             Project: Derby
>          Issue Type: Bug
>          Components: Documentation
>    Affects 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.
-
You can reply to this email to add a comment to the issue online.

[jira] Commented: (DERBY-172) Trigger activation

Posted by "Daniel John Debrunner (JIRA)" <de...@db.apache.org>.
     [ 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

[jira] Commented: (DERBY-172) Trigger activation

Posted by "George Baklarz (JIRA)" <de...@db.apache.org>.
     [ http://issues.apache.org/jira/browse/DERBY-172?page=comments#action_60664 ]
     
George Baklarz commented on DERBY-172:
--------------------------------------

Do you happen to know the syntax for the BEFORE trigger? I'd like to see if I can use it in an application. 

> 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