You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by jt...@apache.org on 2005/06/25 02:30:08 UTC
svn commit: r201702 - /incubator/derby/docs/trunk/src/ref/rrefsqlj43125.dita
Author: jta
Date: Fri Jun 24 17:30:07 2005
New Revision: 201702
URL: http://svn.apache.org/viewcvs?rev=201702&view=rev
Log:
DERBY-115 Committed Jeff Levitt's patch that documents BEFORE triggers.
Modified:
incubator/derby/docs/trunk/src/ref/rrefsqlj43125.dita
Modified: incubator/derby/docs/trunk/src/ref/rrefsqlj43125.dita
URL: http://svn.apache.org/viewcvs/incubator/derby/docs/trunk/src/ref/rrefsqlj43125.dita?rev=201702&r1=201701&r2=201702&view=diff
==============================================================================
--- incubator/derby/docs/trunk/src/ref/rrefsqlj43125.dita (original)
+++ incubator/derby/docs/trunk/src/ref/rrefsqlj43125.dita Fri Jun 24 17:30:07 2005
@@ -1,4 +1,6 @@
<?xml version="1.0" encoding="utf-8"?>
+<!DOCTYPE reference PUBLIC "-//IBM//DTD DITA Reference//EN"
+ "../dtd/reference.dtd">
<!--
Copyright 1997, 2004 The Apache Software Foundation or its licensors, as applicable.
@@ -14,13 +16,17 @@
See the License for the specific language governing permissions and
limitations under the License.
-->
-
-<!DOCTYPE reference PUBLIC "-//IBM//DTD DITA Reference//EN"
- "../dtd/reference.dtd">
<reference id="rrefsqlj43125" xml:lang="en-us">
<title>CREATE TRIGGER statement</title>
<prolog><metadata>
<keywords><indexterm>CREATE TRIGGER statement</indexterm><indexterm>Triggers<indexterm>creating</indexterm></indexterm>
+<indexterm>Triggers<indexterm>defining when they fire</indexterm></indexterm>
+<indexterm>Triggers<indexterm>and database events</indexterm></indexterm>
+<indexterm>Triggers<indexterm>and the referencing clause</indexterm><indexterm>referencing
+old and new values</indexterm></indexterm><indexterm>Triggers<indexterm>statement
+versus row triggers</indexterm></indexterm><indexterm>Triggers<indexterm>the
+triggered-SQL-statement</indexterm></indexterm><indexterm>Triggers<indexterm>order
+of execution</indexterm></indexterm><indexterm>Triggers<indexterm>and recursion</indexterm></indexterm>
</keywords>
</metadata></prolog>
<refbody>
@@ -39,32 +45,35 @@
the schema name cannot begin with <i>SYS</i>.</p></section>
<refsyn id="syn001"><title>Syntax</title> <codeblock><b>CREATE TRIGGER <i><xref
href="rreftriggername.dita#rreftriggername"></xref></i>
-AFTER
-{ INSERT | DELETE | UPDATE [ OF <i>column-Name</i> [, <i><xref href="rrefcolumnname.dita#rrefcolumnname"></xref></i>]* ]
+{ AFTER | NO CASCADE BEFORE }
+{ INSERT | DELETE | UPDATE } [ OF <i>column-Name</i> [, <i><xref href="rrefcolumnname.dita#rrefcolumnname"></xref></i>]* ]
ON <i><xref href="rreftablename.dita#rreftablename"></xref></i>
[ <i><xref href="rrefsqlj89752.dita#rrefsqlj89752"></xref></i> ]
FOR EACH { ROW | STATEMENT } MODE DB2SQL
<i><xref href="rrefsqlj43125.dita#rrefsqlj43125/i1149821">Triggered-SQL-statement</xref></i></b></codeblock> </refsyn>
-<section><title>Before or after: when triggers fire</title> <p><indexterm>Triggers<indexterm>defining
-when they fire</indexterm></indexterm>Triggers fire after all constraints
-have been satisfied and after the changes have been applied to the target
-table. Also called <i>After</i> triggers, they can be either row or statement
-triggers (see <xref href="rrefsqlj43125.dita#rrefsqlj43125/sqlj54276"></xref>).</p> </section>
-<section><title>Insert, delete, or update: what causes the trigger to fire</title> <p><indexterm>Triggers<indexterm>and
-database events</indexterm></indexterm>A trigger is fired by one of the following
-database events, depending on how you define it (in <xref href="rrefsqlj43125.dita#rrefsqlj43125/syn001"></xref> above,
-see the third line): <ul>
+<section><title>Before or after: when triggers fire</title> <p>Triggers are
+defined as either <i>Before</i> or <i>After</i> triggers. <ul>
+<li><i>Before</i> triggers fire before the statement's changes are applied
+and before any constraints have been applied. Before triggers can be either
+row or statement triggers (see <xref href="rrefsqlj43125.dita#rrefsqlj43125/sqlj54276"></xref>).</li>
+<li><i>After</i> triggers fire after all constraints have been satisfied and
+after the changes have been applied to the target table. <i>After</i> triggers
+can be either row or statement triggers (see <xref href="rrefsqlj43125.dita#rrefsqlj43125/sqlj54276"></xref>).</li>
+</ul></p> </section>
+<section><title>Insert, delete, or update: what causes the trigger to fire</title> <p>A
+trigger is fired by one of the following database events, depending on how
+you define it (see <xref href="rrefsqlj43125.dita#rrefsqlj43125/syn001"></xref> above):
+ <ul>
<li>INSERT</li>
<li>UPDATE</li>
<li>DELETE</li>
</ul></p> <p>You can define any number of triggers for a given event on a
given table. For update, you can specify columns.</p> </section>
<section id="sqlj67748"><title>Referencing old and new values: the referencing
-clause</title> <p><indexterm>Triggers<indexterm>and the referencing clause</indexterm><indexterm>referencing
-old and new values</indexterm></indexterm>Many triggered-SQL-statements need
-to refer to data that is currently being changed by the database event that
-caused them to fire. The triggered-SQL-statement might need to refer to the
-new (post-change or "after") values. </p> <p><ph conref="refconrefs.dita#prod/productshortname"></ph> provides
+clause</title> <p>Many triggered-SQL-statements need to refer to data that
+is currently being changed by the database event that caused them to fire.
+The triggered-SQL-statement might need to refer to the new (post-change or
+"after") values. </p> <p><ph conref="refconrefs.dita#prod/productshortname"></ph> provides
you with a number of ways to refer to data that is currently being changed
by the database event that caused the trigger to fire. The easiest way to
refer to the changed data in the triggered-SQL-statement is use the <i>transition
@@ -87,16 +96,16 @@
(SELECT hotel_id FROM DeletedHotels)</b></codeblock></p> <p>The old and
new transition tables map to a <i>java.sql.ResultSet</i> with cardinality
equivalent to the number of rows affected by the triggering event. <note>Only
-statement triggers (see <xref href="rrefsqlj43125.dita#rrefsqlj43125/sqlj54276"></xref>) can use
-the transition tables. INSERT statement triggers cannot reference an OLD table.
-DELETE statement triggers cannot reference a NEW table.</note></p> <p>The
+statement triggers (see <xref href="rrefsqlj43125.dita#rrefsqlj43125/sqlj54276"></xref>)
+can use the transition tables. INSERT statement triggers cannot reference
+an OLD table. DELETE statement triggers cannot reference a NEW table.</note></p> <p>The
referencing clause can designate only one new correlation or identifier and
only one old correlation or identifier. Row triggers cannot designate an identifier
for a transition table and statement triggers cannot designate a correlation
for transition variables. </p> </section>
-<section id="sqlj54276"><title>Statement versus row triggers</title> <p><indexterm>Triggers<indexterm>statement
-versus row triggers</indexterm></indexterm>You must specify whether a trigger
-is a <i>statement trigger</i> or a <i>row trigger</i>: <ul>
+<section id="sqlj54276"><title>Statement versus row triggers</title> <p>You
+must specify whether a trigger is a <i>statement trigger</i> or a <i>row trigger</i>:
+ <ul>
<li><i>statement triggers</i> <p>A statement trigger fires once per triggering
event and regardless of whether any rows are modified by the insert, update,
or delete event.</p></li>
@@ -106,9 +115,8 @@
contained (for example, UPDATE T SET C = C) causes a row trigger to fire,
even though the value of the column is the same as it was prior to the triggering
event.</note></p> </section>
-<section id="i1149821"><title>Triggered-SQL-statement</title> <p><indexterm>Triggers<indexterm>the
-triggered-SQL-statement</indexterm></indexterm>The action defined by the trigger
-is called the triggered-SQL-statement (in <xref href="rrefsqlj43125.dita#rrefsqlj43125/syn001"></xref> above,
+<section id="i1149821"><title>Triggered-SQL-statement</title> <p>The action
+defined by the trigger is called the triggered-SQL-statement (in <xref href="rrefsqlj43125.dita#rrefsqlj43125/syn001"></xref> above,
see the last line). It has the following limitations: <ul>
<li>It must not contain any dynamic parameters (?).</li>
<li>It must not create, alter, or drop the table upon which the trigger is
@@ -120,45 +128,49 @@
<li>It must not commit or roll back the current transaction or change the
isolation level.</li>
<li>It must not execute a CALL statement.</li>
+<li>Before triggers cannot have INSERT, UPDATE or DELETE statements as their
+action.</li>
</ul></p> <p>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.</p><p>For more information
on triggered-SQL-statements, see the <cite><ph conref="refconrefs.dita#pub/citdevelop"></ph></cite>.</p> </section>
-<section><title>Order of execution</title> <p><indexterm>Triggers<indexterm>order
-of execution</indexterm></indexterm>When a database event occurs that fires
-a trigger, <ph conref="refconrefs.dita#prod/productshortname"></ph> performs
+<section><title>Order of execution</title> <p>When a database event occurs
+that fires a trigger, <ph conref="refconrefs.dita#prod/productshortname"></ph> performs
actions in this order: <ul>
+<li>It fires <i>Before</i> triggers.</li>
<li>It performs constraint checking (primary key, unique key, foreign key,
check).</li>
<li>It performs the insert, update, or delete.</li>
-<li>It fires <i>after</i> triggers.</li>
+<li>It fires <i>After</i> triggers.</li>
</ul></p> <p>When multiple triggers are defined for the same database event
for the same table for the same trigger time (before or after), triggers are
fired in the order in which they were created.</p> </section>
-<example> <codeblock><b><ph>-- Statements and after triggers:
+<example> <codeblock><b><ph>-- Statements and triggers:
</ph>
+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');
+
CREATE TRIGGER FLIGHTSDELETE
-AFTER DELETE ON FLIGHTS
-REFERENCING OLD_TABLE AS DELETEDFLIGHTS
-FOR EACH STATEMENT MODE DB2SQL
-DELETE FROM FLIGHTAVAILABILITY WHERE FLIGHT_ID IN
-(SELECT FLIGHT_ID FROM DELETEDFLIGHTS);
+ AFTER DELETE ON FLIGHTS
+ REFERENCING OLD_TABLE AS DELETEDFLIGHTS
+ FOR EACH STATEMENT MODE DB2SQL
+ DELETE FROM FLIGHTAVAILABILITY WHERE FLIGHT_ID IN
+ (SELECT FLIGHT_ID FROM DELETEDFLIGHTS);
CREATE TRIGGER FLIGHTSDELETE3
-AFTER DELETE ON FLIGHTS
-REFERENCING OLD AS OLD
-FOR EACH ROW MODE DB2SQL
-DELETE FROM FLIGHTAVAILABILITY WHERE FLIGHT_ID = OLD.FLIGHT_ID;
+ AFTER DELETE ON FLIGHTS
+ REFERENCING OLD AS OLD
+ FOR EACH ROW MODE DB2SQL
+ DELETE FROM FLIGHTAVAILABILITY WHERE FLIGHT_ID = OLD.FLIGHT_ID;
</b></codeblock> <note>You can find more examples in the <cite><ph conref="refconrefs.dita#pub/citdevelop"></ph></cite>.</note> </example>
-<section><title>Trigger recursion</title> <p><indexterm>Triggers<indexterm>and
-recursion</indexterm></indexterm></p> <p>The maximum trigger recursion depth
-is 16.</p> </section>
-<section><title>Related information</title> <p>Special system
-functions that return information about the current time or current user are
-evaluated when the trigger fires, not when it is created. Such functions include:
- <ul>
+<section><title>Trigger recursion</title> <p>The maximum trigger recursion
+depth is 16.</p> </section>
+<section><title>Related information</title> <p>Special system functions that
+return information about the current time or current user are evaluated when
+the trigger fires, not when it is created. Such functions include: <ul>
<li><xref href="rrefsqlj34177.dita#rrefsqlj34177"></xref></li>
<li><xref href="rrefsqlj33772.dita#rrefsqlj33772"></xref></li>
<li><xref href="rrefsqlj15866.dita#rrefsqlj15866"></xref></li>
@@ -168,4 +180,3 @@
</ul></p> </section>
</refbody>
</reference>
-