[
https://issues.apache.org/jira/browse/DERBY-534?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13818823#comment-13818823
]
Knut Anders Hatlen commented on DERBY-534:
------------------------------------------
Thanks for testing the new syntax, Rick.
I think the weird behaviour you're seeing with DROP FUNCTION also affects other
DROP statements. For example, a similar problem with DROP TABLE is recorded in
DERBY-2041. Hopefully, a fix for that issue will also handle the DROP FUNCTION
case. It should be fixed, but since it is a general trigger problem, I don't
intend to fix it as part of this JIRA issue.
Note also that the weird behaviour is actually documented behaviour up to
version 10.10. [CREATE TRIGGER
statement|http://db.apache.org/derby/docs/10.10/ref/rrefsqlj43125.html] says:
{quote}
The triggered-SQL-statement can reference database objects other than the table
upon which the trigger is declared. If any of these database objects is
dropped, the trigger is invalidated. If the trigger cannot be successfully
recompiled upon the next execution, the invocation throws an exception and the
statement that caused it to fire will be rolled back.
{quote}
DERBY-6390 removed that paragraph from the docs on trunk so that application
writers aren't led to believe that the buggy behaviour is the correct one. (It
was also removed because it's not always true. For example, DROP COLUMN CASCADE
does cause dependent triggers to be dropped. DROP COLUMN's handling of
dependencies is a hack, though. It rebinds the trigger actions of the triggers
it believe might be dependent, and cascades if the rebinding fails with one of
four specific SQLStates. I hope DERBY-2041 will be fixed by making use of the
dependency system in a more direct way, similar to what we do for VIEWs.)
> Support use of the WHEN clause in CREATE TRIGGER statements
> -----------------------------------------------------------
>
> Key: DERBY-534
> URL: https://issues.apache.org/jira/browse/DERBY-534
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Reporter: Kristian Waagan
> Assignee: Knut Anders Hatlen
> Priority: Minor
> Labels: derby_triage10_11
> Attachments: WhenClause.html, WhenClause.html, WhenClause.html,
> WhenClause.html, WhenClause.html, derby-534-01-a-syntax.diff,
> derby-534-02-a-refactor.diff, derby-534-03-a-npe-testcase.diff,
> derby-534-04-a-referencing.diff, derby-534-05-a-generated-cols.diff,
> derby-534-06-a-temptables.diff, derby-534-07-a-more-restrictions.diff,
> derby-534-08-a-test-invalidation.diff, derby-534-09-a-drop-column-deps.diff,
> derby-534-09-b-drop-column-deps.diff, derby-534-10-a-get-sps.diff,
> derby-534-11-a-more-tests.diff, derby-534-12-a-subquery-npe.diff,
> derby-534-13-a-dblook.diff, derby-534-14-a-remove-formatable-upgrade.diff,
> parser.diff
>
>
> Support use of the WHEN clause in CREATE TRIGGER statements. The clause is
> described in the SQL standard (2003) in chapter "11.39 <trigger definition>"
> under "<triggered action>".
> There are traces in the code that suggests some work has been done on this
> earlier. If anyone knows something about this, please add a comment to this
> issue.
--
This message was sent by Atlassian JIRA
(v6.1#6144)
