BEFORE triggers missing from CREATE TRIGGER documentation

• Key: DERBY-115
• Type: Bug
• Status: Closed
• Resolution: Fixed
• Priority: Minor
• Assignee: Unassigned
• Reporter: Daniel John Debrunner
• Component/s: Documentation
• Affects Version/s: 10.0.2.1
• Fix Version/s: 10.1.1.0

Description

Derby does support before triggers, syntax is the same as after triggers,
but replace AFTER with NO CASCADE BEFORE. Eg.

create trigger t1
NO CASCADE BEFORE insert on x
referencing new_table as newrowtab
for each statement mode db2sql
insert into y select x from newrowtab;

Comments

[Jeff Levitt]

Hi Dan, I'd be happy to make this change. However, it seems to me that the CREATE TRIGGERS syntax has another mistake that I'd like to fix at the same time. See:

http://incubator.apache.org/derby/docs/ref/rrefsqlj43125.html

On that page, the syntax seems to be missing a closing curly brace "}" to the one that is opened after the "AFTER".
  
CREATE TRIGGER TriggerName
AFTER
{ INSERT | DELETE | UPDATE [ OF column-Name [, column-Name]* ]
ON table-Name
[ ReferencingClause ]
FOR EACH { ROW | STATEMENT } MODE DB2SQL
Triggered-SQL-statement

Where should the closing curly brace go?

Also, for NO CASCADE BEFORE, should I just modify this syntax to look like this?:

CREATE TRIGGER TriggerName
NO CASCADE BEFORE | AFTER
{ INSERT | DELETE | UPDATE [ OF column-Name [, column-Name]* ]
ON table-Name
[ ReferencingClause ]
FOR EACH { ROW | STATEMENT } MODE DB2SQL
Triggered-SQL-statement

[Daniel John Debrunner]

I think this is correct:

- braces around the NO CASCADE BEFORE | AFTER
- missing brace put back in after the UPDATE


CREATE TRIGGER TriggerName
{ NO CASCADE BEFORE | AFTER }
{ INSERT | DELETE | UPDATE } [ OF column-Name [, column-Name]* ]
ON table-Name
[ ReferencingClause ]
FOR EACH { ROW | STATEMENT } MODE DB2SQL
Triggered-SQL-statement

Attached patch fixes the syntax, adds NO CASCADE BEFORE, and mentions Before triggers in two additional places in the topic where it used to just mention After triggers. Please see attached output in html for review, and provide either feedback or a thumbs up so we can get the changes commited. Thanks!

[Jean T. Anderson]

The NO CASCADE BEFORE example in the patch won't work because BEFORE triggers don't allow INSERTs. A BEFORE trigger can execute a SQL function, so here's a suggestion:
 
CREATE TRIGGER t1 NO CASCADE BEFORE UPDATE ON x
FOR EACH ROW MODE DB2SQL
  values app.notifyEmail('Jerry', 'Table x is about to be updated');

[Jeff Levitt]

Attached patch uses Jean's example instead of the one previously there. Please review! Thanks!

[Daniel John Debrunner]

In the Triggered-SQL-statement section, an additiona limitation is that before triggers can not have INSERT, UPDATE or DELETE statements as their action.

[Jeff Levitt]

Attached patch adds the line Dan requested...please review!

[Daniel John Debrunner]

looks good!

[Jean T. Anderson]

Committed, revision 201702, Jeff Levitt's patch that documents BEFORE triggers. Changed files:
$ svn status
M src/ref/rrefsqlj43125.dita