Derby
  1. Derby
  2. DERBY-6390

Document the WHEN clause in the CREATE TRIGGER statement

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.11.0.0
    • Fix Version/s: 10.11.0.0
    • Component/s: Documentation
    • Labels:
      None

      Description

      The documentation should be updated with information about the WHEN clause that was added to the CREATE TRIGGER statement in DERBY-534. The functional specification attached to that issue has a list of topics that need updating.

      1. DERBY-6390-2.zip
        28 kB
        Kim Haase
      2. DERBY-6390-2.diff
        21 kB
        Kim Haase
      3. DERBY-6390.zip
        28 kB
        Kim Haase
      4. DERBY-6390.stat
        0.4 kB
        Kim Haase
      5. DERBY-6390.diff
        21 kB
        Kim Haase

        Issue Links

          Activity

          Hide
          Kim Haase added a comment -

          Are the requirements for the WHEN clause's searchCondition similar to those defined for a CONSTRAINT clause in http://db.apache.org/derby/docs/10.10/ref/rrefsqlj13590.html#rrefsqlj13590__sqlj57077? I don't want to link back to that section, but I was wondering if I should reproduce that bullet list and its introductory paragraph.

          Show
          Kim Haase added a comment - Are the requirements for the WHEN clause's searchCondition similar to those defined for a CONSTRAINT clause in http://db.apache.org/derby/docs/10.10/ref/rrefsqlj13590.html#rrefsqlj13590__sqlj57077? I don't want to link back to that section, but I was wondering if I should reproduce that bullet list and its introductory paragraph.
          Hide
          Knut Anders Hatlen added a comment -

          The requirements for the searchCondition in a constraint are stricter than the requirements for the searchCondition in a WHEN clause. Only the dynamic parameters restriction in that list apply to the WHEN clause. (Note to self: We should add tests to verify that those restrictions don't apply.)

          Actually, I think the current placement of the searchCondition topic (under CONSTRAINT clause) is suboptimal. It would be better to have it in the SQL expressions section, and link to it from CONSTRAINT clause, WHERE clause, HAVING clause, CASE expression, and from the ON clause specifications in the INNER JOIN and LEFT/RIGHT OUTER JOIN topics. The searchCondition topic could again link to booleanExpression.

          In the SQL standard, the production rule for searchCondition is as simple as <search condition> ::= <boolean value expression>. So one possible short-cut we could take (one which the topics for WHERE, ON and CASE already take) is to link the WHEN clause specification directly to booleanExpression. (This still leaves the HAVING clause with a reference to searchCondition even though it doesn't have the restrictions of the CONSTRAINT clause, so in that case we might want to update the HAVING clause with a reference to booleanExpression too.)

          Essentially, the searchCondition in the WHEN clause is the same as in the WHERE clause, except that the restrictions already listed for the triggeredSQLStatement also apply to the WHEN clause.

          And by the way... Boolean expressions are defined two different places: here in a paragraph of the top-level SQL expressions topic, and here as an independent sub-topic of SQL expressions. The former links to the latter, but the latter does not link to the former. The latter is what other topics typically link to. Unfortunately, that topic does not mention that a boolean expression might also be a general expression that returns a boolean value. It would be good to have that information in that topic too, so that it doesn't give the impression that a boolean expression has to use one of the boolean operators listed in the table.

          Show
          Knut Anders Hatlen added a comment - The requirements for the searchCondition in a constraint are stricter than the requirements for the searchCondition in a WHEN clause. Only the dynamic parameters restriction in that list apply to the WHEN clause. (Note to self: We should add tests to verify that those restrictions don't apply.) Actually, I think the current placement of the searchCondition topic (under CONSTRAINT clause) is suboptimal. It would be better to have it in the SQL expressions section, and link to it from CONSTRAINT clause, WHERE clause, HAVING clause, CASE expression, and from the ON clause specifications in the INNER JOIN and LEFT/RIGHT OUTER JOIN topics. The searchCondition topic could again link to booleanExpression. In the SQL standard, the production rule for searchCondition is as simple as <search condition> ::= <boolean value expression> . So one possible short-cut we could take (one which the topics for WHERE, ON and CASE already take) is to link the WHEN clause specification directly to booleanExpression. (This still leaves the HAVING clause with a reference to searchCondition even though it doesn't have the restrictions of the CONSTRAINT clause, so in that case we might want to update the HAVING clause with a reference to booleanExpression too.) Essentially, the searchCondition in the WHEN clause is the same as in the WHERE clause, except that the restrictions already listed for the triggeredSQLStatement also apply to the WHEN clause. And by the way... Boolean expressions are defined two different places: here in a paragraph of the top-level SQL expressions topic, and here as an independent sub-topic of SQL expressions. The former links to the latter, but the latter does not link to the former. The latter is what other topics typically link to. Unfortunately, that topic does not mention that a boolean expression might also be a general expression that returns a boolean value. It would be good to have that information in that topic too, so that it doesn't give the impression that a boolean expression has to use one of the boolean operators listed in the table.
          Hide
          Kim Haase added a comment -

          Thanks, Knut, for all these excellent suggestions – looks as if we have a chance to make some more general improvements to the manual along with documenting this one clause. I will work on these.

          Show
          Kim Haase added a comment - Thanks, Knut, for all these excellent suggestions – looks as if we have a chance to make some more general improvements to the manual along with documenting this one clause. I will work on these.
          Hide
          Kim Haase added a comment -

          On further thought, I'm going to file a separate issue for the organizational improvements you suggest, since they are essentially independent of the WHEN clause documentation and could usefully be backported to 10.10.

          Show
          Kim Haase added a comment - On further thought, I'm going to file a separate issue for the organizational improvements you suggest, since they are essentially independent of the WHEN clause documentation and could usefully be backported to 10.10.
          Hide
          Kim Haase added a comment -

          Now that DERBY-6394 is completed, I'm attaching DERBY-6390.diff, DERBY-6390.stat, and DERBY-6390.zip, with these changes:

          M src/ref/rrefsqlj37836.dita
          M src/ref/rrefsistabs79888.dita
          M src/ref/refderby.ditamap
          M src/ref/rrefsqlj43125.dita
          A src/ref/rrefsqljwhenclause.dita
          M src/ref/rrefdeclaretemptable.dita
          M src/devguide/cdevspecial67260.dita
          M src/devguide/cdevspecial27163.dita
          M src/devguide/cdevspecial53165.dita
          M src/devguide/cdevspecial76763.dita
          M src/devguide/cdevspecial13670.dita

          I made some modifications to 5 topics in the Dev Guide. I noticed that there was almost nothing in the Reference Manual about referential integrity, so I modified a paragraph in "Performing referential actions".

          In addition to adding the new material, I changed "triggered-SQL-statement" to "triggeredSQLStatement" where it occurred in both manuals, for the sake of consistency.

          Please let me know what further changes are needed.

          Show
          Kim Haase added a comment - Now that DERBY-6394 is completed, I'm attaching DERBY-6390 .diff, DERBY-6390 .stat, and DERBY-6390 .zip, with these changes: M src/ref/rrefsqlj37836.dita M src/ref/rrefsistabs79888.dita M src/ref/refderby.ditamap M src/ref/rrefsqlj43125.dita A src/ref/rrefsqljwhenclause.dita M src/ref/rrefdeclaretemptable.dita M src/devguide/cdevspecial67260.dita M src/devguide/cdevspecial27163.dita M src/devguide/cdevspecial53165.dita M src/devguide/cdevspecial76763.dita M src/devguide/cdevspecial13670.dita I made some modifications to 5 topics in the Dev Guide. I noticed that there was almost nothing in the Reference Manual about referential integrity, so I modified a paragraph in "Performing referential actions". In addition to adding the new material, I changed "triggered-SQL-statement" to "triggeredSQLStatement" where it occurred in both manuals, for the sake of consistency. Please let me know what further changes are needed.
          Hide
          Knut Anders Hatlen added a comment -

          Thanks, Kim! I've started going through your changes, and it looks very good so far. I'll post more when I'm done.

          Just one initial note: The SYSTRIGGERS table topic says that the WHENCLAUSETEXT column is LONG VARCHAR and the length is 32700. Actually, the maximum length of this column is Integer.MAX_VALUE. Now, I'm not sure whether we should say that, since we don't allow users to create tables with LONG VARCHAR columns longer than 32700 characters. But maybe it's better to just tell the truth and say that it's Integer.MAX_VALUE.

          FWIW, I just now tested that I could create a trigger with a WHEN clause that caused WHENCLAUSETEXT to be more than 60000 characters long, and that seemed to work fine. (Note to self: Should probably add a test case for it.)

          The same is true for the TRIGGERDEFINITION column. It says 32700 in the manual, but it's actually Integer.MAX_VALUE.

          Show
          Knut Anders Hatlen added a comment - Thanks, Kim! I've started going through your changes, and it looks very good so far. I'll post more when I'm done. Just one initial note: The SYSTRIGGERS table topic says that the WHENCLAUSETEXT column is LONG VARCHAR and the length is 32700. Actually, the maximum length of this column is Integer.MAX_VALUE. Now, I'm not sure whether we should say that, since we don't allow users to create tables with LONG VARCHAR columns longer than 32700 characters. But maybe it's better to just tell the truth and say that it's Integer.MAX_VALUE. FWIW, I just now tested that I could create a trigger with a WHEN clause that caused WHENCLAUSETEXT to be more than 60000 characters long, and that seemed to work fine. (Note to self: Should probably add a test case for it.) The same is true for the TRIGGERDEFINITION column. It says 32700 in the manual, but it's actually Integer.MAX_VALUE.
          Hide
          Kim Haase added a comment -

          I noticed that length discrepancy when I ran a command line Rick suggested a while back to get the data type values. Currently the only columns for which we document the length as Integer.MAX_VALUE are the ones where the data type is not part of the public API (like REFERENCEDCOLUMNS). Anything we document as LONG VARCHAR is shown with 32,700 as the maximum length. So we should probably continue in that practice.

          Show
          Kim Haase added a comment - I noticed that length discrepancy when I ran a command line Rick suggested a while back to get the data type values. Currently the only columns for which we document the length as Integer.MAX_VALUE are the ones where the data type is not part of the public API (like REFERENCEDCOLUMNS). Anything we document as LONG VARCHAR is shown with 32,700 as the maximum length. So we should probably continue in that practice.
          Hide
          Knut Anders Hatlen added a comment -

          I've gone through the reference manual changes. My comments so far:

          • I'm not sure if the WHEN clause topic deserves such a prominent place in the SQL clauses chapter, or if it should just be a sub-topic of CREATE TRIGGER statement topic, like the referencingClause topic.
          • The WHEN clause topic says that the WHEN clause cannot reference global temporary tables and tables in the SESSION schema. Those restrictions also apply to triggeredSQLStatement. Since it already says that all restrictions that apply to triggeredSQLStatement also apply to the WHEN clause, it might be better to move that information to the triggeredSQLStatement section (it's not there currently, only in the DECLARE GLOBAL TEMPORARY TABLE statement topic). Also, all global temporary tables live in the SESSION schema, so it suffices to mention the SESSION schema restriction (although mentioning both is also fine).
          • Same with the restriction on referencing a generated column via the NEW transition variable in a BEFORE trigger. It's mentioned both directly and indirectly via the reference to the restrictions of triggeredSQLStatement.
          • The information about privileges is covered in the CREATE TRIGGER statement topic ("A trigger operates with the privileges of the owner of the trigger.") so it probably doesn't need to be repeated in the WHEN clause topic.
          • The description of what happens when a DROP operation is performed is the desired behaviour, but not the actual behaviour for many DROP operations because of DERBY-2041. I see that the triggeredSQLStatement section has gone to the other extreme and described the buggy DERBY-2041 behaviour as the expected behaviour, even though that doesn't fully explain what actually happens either, since some DROP operations in fact do the right thing. Maybe we should just leave this undocumented for now, since the actual behaviour is quite muddy. (And the various DROP operations already say how they're supposed to behave, so we could hide behind the fact that it is already covered indirectly.)
          Show
          Knut Anders Hatlen added a comment - I've gone through the reference manual changes. My comments so far: I'm not sure if the WHEN clause topic deserves such a prominent place in the SQL clauses chapter, or if it should just be a sub-topic of CREATE TRIGGER statement topic, like the referencingClause topic. The WHEN clause topic says that the WHEN clause cannot reference global temporary tables and tables in the SESSION schema. Those restrictions also apply to triggeredSQLStatement. Since it already says that all restrictions that apply to triggeredSQLStatement also apply to the WHEN clause, it might be better to move that information to the triggeredSQLStatement section (it's not there currently, only in the DECLARE GLOBAL TEMPORARY TABLE statement topic). Also, all global temporary tables live in the SESSION schema, so it suffices to mention the SESSION schema restriction (although mentioning both is also fine). Same with the restriction on referencing a generated column via the NEW transition variable in a BEFORE trigger. It's mentioned both directly and indirectly via the reference to the restrictions of triggeredSQLStatement. The information about privileges is covered in the CREATE TRIGGER statement topic ("A trigger operates with the privileges of the owner of the trigger.") so it probably doesn't need to be repeated in the WHEN clause topic. The description of what happens when a DROP operation is performed is the desired behaviour, but not the actual behaviour for many DROP operations because of DERBY-2041 . I see that the triggeredSQLStatement section has gone to the other extreme and described the buggy DERBY-2041 behaviour as the expected behaviour, even though that doesn't fully explain what actually happens either, since some DROP operations in fact do the right thing. Maybe we should just leave this undocumented for now, since the actual behaviour is quite muddy. (And the various DROP operations already say how they're supposed to behave, so we could hide behind the fact that it is already covered indirectly.)
          Hide
          Kim Haase added a comment -

          Thanks, Knut, for the great comments – you found a lot of duplication.

          I have made the changes in the reference manual but won't file a second patch till you have some Developer's Guide comments.

          Show
          Kim Haase added a comment - Thanks, Knut, for the great comments – you found a lot of duplication. I have made the changes in the reference manual but won't file a second patch till you have some Developer's Guide comments.
          Hide
          Knut Anders Hatlen added a comment -

          Thank you, Kim!

          The devguide changes look good to me. Just some minor nits:

          • The example of a trigger without a WHEN clause is terminated with a semicolon, whereas the example with a WHEN clause isn't.
          • In the old example (without a WHEN clause) we could probably remove the DB2ism "MODE DB2SQL" now (it is only needed in 10.2 and earlier).
          • It would be good to clarify in the description of the new example that the update of the FlightAvailability table only happens if the triggering update actually changed the value of FLIGHTS.FLIGHT_ID. (Maybe that was a silly example I came up with, by the way, since the same could be achieved just as easily without a WHEN clause by declaring the trigger as AFTER UPDATE OF flight_id ON flights. But it does show the syntax and what you can put into the clause, so it's probably good enough.)
          Show
          Knut Anders Hatlen added a comment - Thank you, Kim! The devguide changes look good to me. Just some minor nits: The example of a trigger without a WHEN clause is terminated with a semicolon, whereas the example with a WHEN clause isn't. In the old example (without a WHEN clause) we could probably remove the DB2ism "MODE DB2SQL" now (it is only needed in 10.2 and earlier). It would be good to clarify in the description of the new example that the update of the FlightAvailability table only happens if the triggering update actually changed the value of FLIGHTS.FLIGHT_ID. (Maybe that was a silly example I came up with, by the way, since the same could be achieved just as easily without a WHEN clause by declaring the trigger as AFTER UPDATE OF flight_id ON flights. But it does show the syntax and what you can put into the clause, so it's probably good enough.)
          Hide
          Kim Haase added a comment -

          Thanks, Knut! I'll file an updated patch. I gather we should leave MODE DB2SQL in the Reference Manual syntax for the sake of compatibility, but we shouldn't use it in current examples.

          Since you suggested leaving the buggy DROP behavior undocumented for now, I have removed it from the CREATE TRIGGER topic for 10.11 – it will still be in the 10.10 docs, though, as described in DERBY-2041. Is that what you meant? Or should I put the description back in?

          Show
          Kim Haase added a comment - Thanks, Knut! I'll file an updated patch. I gather we should leave MODE DB2SQL in the Reference Manual syntax for the sake of compatibility, but we shouldn't use it in current examples. Since you suggested leaving the buggy DROP behavior undocumented for now, I have removed it from the CREATE TRIGGER topic for 10.11 – it will still be in the 10.10 docs, though, as described in DERBY-2041 . Is that what you meant? Or should I put the description back in?
          Hide
          Kim Haase added a comment -

          Attaching DERBY-6390-2.diff and DERBY-6390-2.zip, with (I hope) the suggested revisions to some of the Dev Guide and Reference Manual topics.

          Show
          Kim Haase added a comment - Attaching DERBY-6390 -2.diff and DERBY-6390 -2.zip, with (I hope) the suggested revisions to some of the Dev Guide and Reference Manual topics.
          Hide
          Knut Anders Hatlen added a comment -

          I think it's fine to take that part out of CREATE TRIGGER statement. What is says now, is correct at least for DROP TABLE. But not for DROP COLUMN. (Haven't checked other DROP statements.) And the way it describes it is not how we would want the DROP operations to behave. So once DERBY-2041 is fixed, we could add correct information to the CREATE TRIGGER topic. That correct information would look like the text you initially proposed for the WHEN clause.

          Or we could simply not mention it at all in CREATE TRIGGER, even after the bug is fixed, since the behaviour then should be as documented in the various DROP statements. I think the main reason why it is currently mentioned in CREATE TRIGGER, is that DROP statements currently don't work as documented on dependent triggers. Once the behaviour is consistent for all dependent objects, we might not need a special case for triggers in the docs.

          And yes, +1 to keeping MODE DB2SQL in the syntax description, and to leaving it out of the examples (for simplicity and for portability).

          Show
          Knut Anders Hatlen added a comment - I think it's fine to take that part out of CREATE TRIGGER statement. What is says now, is correct at least for DROP TABLE. But not for DROP COLUMN. (Haven't checked other DROP statements.) And the way it describes it is not how we would want the DROP operations to behave. So once DERBY-2041 is fixed, we could add correct information to the CREATE TRIGGER topic. That correct information would look like the text you initially proposed for the WHEN clause. Or we could simply not mention it at all in CREATE TRIGGER, even after the bug is fixed, since the behaviour then should be as documented in the various DROP statements. I think the main reason why it is currently mentioned in CREATE TRIGGER, is that DROP statements currently don't work as documented on dependent triggers. Once the behaviour is consistent for all dependent objects, we might not need a special case for triggers in the docs. And yes, +1 to keeping MODE DB2SQL in the syntax description, and to leaving it out of the examples (for simplicity and for portability).
          Hide
          Knut Anders Hatlen added a comment -

          Thanks for the updated patch, Kim. It looks great. +1 to commit.

          Show
          Knut Anders Hatlen added a comment - Thanks for the updated patch, Kim. It looks great. +1 to commit.
          Hide
          ASF subversion and git services added a comment -

          Commit 1538640 from Kim Haase in branch 'docs/trunk'
          [ https://svn.apache.org/r1538640 ]

          DERBY-6390 Document the WHEN clause in the CREATE TRIGGER statement

          Added a Reference Manual topic and modified 4 others and the map; modified 5
          Developer's Guide topics.

          Patch: DERBY-6390-2.diff

          Show
          ASF subversion and git services added a comment - Commit 1538640 from Kim Haase in branch 'docs/trunk' [ https://svn.apache.org/r1538640 ] DERBY-6390 Document the WHEN clause in the CREATE TRIGGER statement Added a Reference Manual topic and modified 4 others and the map; modified 5 Developer's Guide topics. Patch: DERBY-6390 -2.diff
          Hide
          ASF subversion and git services added a comment -

          Commit 1538640 from Kim Haase in branch 'docs/trunk'
          [ https://svn.apache.org/r1538640 ]

          DERBY-6390 Document the WHEN clause in the CREATE TRIGGER statement

          Added a Reference Manual topic and modified 4 others and the map; modified 5
          Developer's Guide topics.

          Patch: DERBY-6390-2.diff

          Show
          ASF subversion and git services added a comment - Commit 1538640 from Kim Haase in branch 'docs/trunk' [ https://svn.apache.org/r1538640 ] DERBY-6390 Document the WHEN clause in the CREATE TRIGGER statement Added a Reference Manual topic and modified 4 others and the map; modified 5 Developer's Guide topics. Patch: DERBY-6390 -2.diff
          Hide
          Kim Haase added a comment -

          Thanks, Knut!

          Committed patch DERBY-6390-2.diff to documentation trunk at revision 1538640.

          Show
          Kim Haase added a comment - Thanks, Knut! Committed patch DERBY-6390 -2.diff to documentation trunk at revision 1538640.

            People

            • Assignee:
              Kim Haase
              Reporter:
              Knut Anders Hatlen
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development