friedkiwi/ibm-information-center
1
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
master
ibm-information-center/dist/eclipse/plugins/i5OS.ic.db2_5.4.0.1/rbafzmsthctrigger.htm

806 lines
49 KiB
HTML
Raw Permalink Blame History

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="en-US" xml:lang="en-us">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta name="dc.language" scheme="rfc1766" content="en-us" />
<!-- All rights reserved. Licensed Materials Property of IBM -->
<!-- US Government Users Restricted Rights -->
<!-- Use, duplication or disclosure restricted by -->
<!-- GSA ADP Schedule Contract with IBM Corp. -->
<meta name="dc.date" scheme="iso8601" content="2005-09-19" />
<meta name="copyright" content="(C) Copyright IBM Corporation 1998, 2006" />
<meta name="security" content="public" />
<meta name="Robots" content="index,follow"/>
<meta http-equiv="PICS-Label" content='(PICS-1.1 "http://www.icra.org/ratingsv02.html" l gen true r (cz 1 lz 1 nz 1 oz 1 vz 1) "http://www.rsac.org/ratingsv01.html" l gen true r (n 0 s 0 v 0 l 0) "http://www.classify.org/safesurf/" l gen true r (SS~~000 1))' />
<meta name="keywords" content="CREATE TRIGGER statement, CREATE TRIGGER,
SQL statements, creating, trigger, transition variable, transition table" />
<title>CREATE TRIGGER</title>
<link rel="stylesheet" type="text/css" href="ibmidwb.css" />
<link rel="stylesheet" type="text/css" href="ic.css" />
</head>
<body>
<a id="Top_Of_Page" name="Top_Of_Page"></a><!-- Java sync-link -->
<script language = "Javascript" src = "../rzahg/synch.js" type="text/javascript"></script>
<a name="hctrigger"></a>
<h2 id="hctrigger"><a href="rbafzmst02.htm#ToC_949">CREATE TRIGGER</a></h2>
<p><a id="idx2167" name="idx2167"></a></p><a id="idx2168" name="idx2168"></a><a id="idx2169" name="idx2169"></a>
<p>The CREATE TRIGGER statement defines a trigger at the current server.</p>
<a name="itrig"></a>
<h3 id="itrig"><a href="rbafzmst02.htm#ToC_950">Invocation</a></h3>
<p>This statement can be embedded in an application program or issued interactively.
It is an executable statement that can be dynamically prepared.</p>
<a name="wq1346"></a>
<h3 id="wq1346"><a href="rbafzmst02.htm#ToC_951">Authorization</a></h3>
<p>The privileges held by the authorization ID of the statement must include
at least one of the following:</p>
<ul>
<li>The privilege to create in the schema. For more information, see <a href="rbafzmstauthown.htm#createin">Privileges necessary to create in a schema</a>.</li>
<li>Administrative authority</li></ul>
<p>The privileges held by the authorization ID of the statement must include
at least one of the following:</p>
<ul>
<li>Each of the following:
<ul>
<li>The ALTER privilege on the table or view on which the trigger
is defined,</li>
<li>The SELECT privilege on the table or view on which the trigger
is defined,</li>
<li>The SELECT privilege on any table or view referenced in the <span class="italic">search-condition</span> in the <span class="italic">trigger-action</span>,</li>
<li>The UPDATE privilege on the table on which the trigger is defined, if
the BEFORE UPDATE trigger contains a SET statement that modifies the NEW correlation
variable,</li>
<li>The privileges required to execute each <span class="italic">triggered-SQL-statement</span>, and</li>
<li>The system authority *EXECUTE on the library containing the
table or view on which the trigger is defined.</li></ul></li>
<li>Administrative authority</li></ul>
<p>In addition, the privileges held by the authorization ID of the statement
must include at least one of the following:</p>
<ul>
<li>The following system authorities:
<ul>
<li>*USE on the Add Physical File Trigger (ADDPFTRG) command,</li>
<li>*USE on the Create Program (CRTPGM) command</li></ul></li>
<li>Administrative authority</li></ul>
<p>If SQL names are specified, and a user profile exists that has the same
name as the library into which the trigger is created, and the name is different
from the authorization ID of the statement, then the privileges held by the
authorization ID of the statement must include at least one of the following:</p>
<ul>
<li>*ALLOBJ and *SECADM special authority</li>
<li>Administrative authority</li></ul>
<p>For information on the system authorities corresponding to SQL privileges,
see <a href="rbafzmstgnt.htm#eqtablet">Corresponding System Authorities When Checking Privileges to a Table or View</a>.</p>
<a name="wq1347"></a>
<h3 id="wq1347"><a href="rbafzmst02.htm#ToC_952">Syntax</a></h3>
<a href="rbafzmsthctrigger.htm#synscrttrig"><img src="c.gif" alt="Click to skip syntax diagram" /></a>
<a name="wq1348"></a>
<div class="fignone" id="wq1348">
<pre class="cgraphic"><span><img src="c.gif" alt="Read syntax diagram" longdesc="rbafzmstsyn311.htm"
border="0" /></span><a href="#skipsyn-310"><img src="c.gif" alt="Skip visual syntax diagram"
border="0" /></a> .-NO CASCADE-.
>>-CREATE TRIGGER--<span class="italic">trigger-name</span>--+-+------------+--BEFORE-+----->
+-AFTER------------------+
'-<span>INSTEAD OF</span>-------------'
>--+-INSERT------------------------------+--ON--+-<span><span class="italic">table-name</span></span>-+-->
+-DELETE------------------------------+ '-<span><span class="italic">view-name</span></span>--'
'-UPDATE--+-------------------------+-'
| .-,---------------. |
| V | |
'-OF------<span class="italic">column-name</span>---+-'
>--+-------------------------------------------------------------------+-->
| .--------------------------------------------------. |
| V .-ROW-. .-AS-. (1) | |
'-REFERENCING----+-OLD--+-----+--+----+--<span class="italic">correlation-name</span>--+------+-'
| .-ROW-. .-AS-. |
+-NEW--+-----+--+----+--<span class="italic">correlation-name</span>--+
| .-AS-. |
+-+-OLD TABLE-+--+----+--<span class="italic">table-identifier</span>-+
| '-OLD_TABLE-' |
| .-AS-. |
'-+-NEW TABLE-+--+----+--<span class="italic">table-identifier</span>-'
'-NEW_TABLE-'
.-FOR EACH STATEMENT-. .-MODE DB2SQL-.
>--+--------------------+--+-------------+--<span class="italic">triggered-action</span>--->&lt;
'-FOR EACH ROW-------' '-MODE DB2ROW-'
</pre>
<a name="skipsyn-310" id="skipsyn-310"></a>
<a name="wq1349"></a>
<div class="notelisttitle" id="wq1349">Notes:</div>
<ol type="1">
<li>The same clause must not be specified more than once.</li>
</ol></div>
<a name="wq1351"></a>
<div class="fignone" id="wq1351">
<pre class="cgraphic"><span><img src="c.gif" alt="Read syntax diagram" longdesc="rbafzmstsyn312.htm"
border="0" /></span><a href="#skipsyn-311"><img src="c.gif" alt="Skip visual syntax diagram"
border="0" /></a>triggered-action:
|--+----------------------+--+------------------------------+----<span class="italic">SQL-trigger-body</span>----|
'-<span class="italic">SET OPTION-statement</span>-' '-WHEN--(--<span class="italic">search-condition</span>--)-'
SQL-trigger-body:
|--+-<span class="italic">SQL-control-statement</span>-----------------------+--------------|
+-<span class="italic">fullselect</span>----------------------------------+
+-<span><span class="italic">ALLOCATE DESCRIPTOR-statement</span></span>---------------+
+-<span class="italic">ALTER PROCEDURE (External)-statement</span>--------+
+-<span class="italic">ALTER SEQUENCE-statement</span>--------------------+
+-<span class="italic">ALTER TABLE-statement</span>-----------------------+
+-<span class="italic">COMMENT statement</span>---------------------------+
+-<span class="italic">CREATE ALIAS-statement</span>----------------------+
+-<span class="italic">CREATE DISTINCT TYPE-statement</span>--------------+
+-<span class="italic">CREATE FUNCTION (External Scalar)-statement</span>-+
+-<span class="italic">CREATE FUNCTION (External Table)-statement</span>--+
+-<span class="italic">CREATE INDEX-statement</span>----------------------+
+-<span class="italic">CREATE PROCEDURE (External)-statement</span>-------+
+-<span class="italic">CREATE SCHEMA-statement</span>---------------------+
+-<span class="italic">CREATE SEQUENCE-statement</span>-------------------+
+-<span class="italic">CREATE TABLE-statement</span>----------------------+
+-<span class="italic">CREATE VIEW-statement</span>-----------------------+
+-<span><span class="italic">DEALLOCATE DESCRIPTOR-statement</span></span>-------------+
+-<span class="italic">DECLARE GLOBAL TEMPORARY TABLE-statement</span>----+
+-<span class="italic">DELETE-statement</span>----------------------------+
+-<span><span class="italic">DESCRIBE-statement</span></span>--------------------------+
+-<span><span class="italic">DESCRIBE INPUT-statement</span></span>--------------------+
+-<span><span class="italic">DESCRIBE TABLE-statement</span></span>--------------------+
+-<span class="italic">DROP-statement</span>------------------------------+
+-<span class="italic">EXECUTE IMMEDIATE-statement</span>-----------------+
+-<span><span class="italic">GET DESCRIPTOR-statement</span></span>--------------------+
+-<span class="italic">GRANT-statement</span>-----------------------------+
+-<span class="italic">INSERT-statement</span>----------------------------+
+-<span class="italic">LABEL-statement</span>-----------------------------+
+-<span class="italic">LOCK TABLE-statement</span>------------------------+
+-<span class="italic">REFRESH TABLE-statement</span>---------------------+
+-<span class="italic">RELEASE-statement</span>---------------------------+
+-<span class="italic">RELEASE SAVEPOINT-statement</span>-----------------+
+-<span class="italic">RENAME-statement</span>----------------------------+
+-<span class="italic">REVOKE-statement</span>----------------------------+
+-<span class="italic">SAVEPOINT-statement</span>-------------------------+
+-<span class="italic">SELECT INTO-statement</span>-----------------------+
+-<span><span class="italic">SET CURRENT DEBUG MODE-statement</span></span>------------+
+-<span><span class="italic">SET CURRENT DEGREE-statement</span></span>----------------+
+-<span><span class="italic">SET DESCRIPTOR-statement</span></span>--------------------+
+-<span class="italic">SET ENCRYPTION PASSWORD-statement</span>-----------+
+-<span class="italic">SET PATH-statement</span>--------------------------+
+-<span class="italic">SET SCHEMA-statement</span>------------------------+
+-<span class="italic">SET TRANSACTION-statement</span>-------------------+
'-<span class="italic">UPDATE-statement</span>----------------------------'
</pre>
<a name="skipsyn-311" id="skipsyn-311"></a></div>
<a name="synscrttrig"></a>
<h3 id="synscrttrig"><a href="rbafzmst02.htm#ToC_953">Description</a></h3>
<dl class="parml">
<dt class="bold"><var class="pv">trigger-name</var></dt>
<dd>
<p>Names the trigger. The name, including the implicit or explicit
qualifier, must not be the same as a trigger that already exists at the current
server. QTEMP cannot be used as the <span class="italic">trigger-name</span> schema
qualifier.</p>
<p>If SQL names were specified, the trigger will be created
in the schema specified by the implicit or explicit qualifier.</p>
<p> If system
names were specified, the trigger will be created in the schema that is specified
by the qualifier. If not qualified, the trigger will be created in the same
schema as the subject table.</p>
<p>If the trigger name is not a valid system
name, or if a program with the same name already exists, the database manager will generate
a system name. For information on the rules for generating a name, see <a href="rbafzmsthctabl.htm#namrul">Rules for Table Name Generation</a>.</p>
</dd>
<dt class="bold">NO CASCADE</dt>
<dd>NO CASCADE is allowed for compatibility with other products and is not
used by DB2 UDB for iSeries.
</dd>
<dt class="bold">BEFORE</dt>
<dd>Specifies that the trigger is a <span class="italic">before</span> trigger. The database manager executes
the <span class="italic">triggered-action</span> before it applies any changes
caused by an insert, delete, or update operation on the subject table. It
also specifies that the <span class="italic">triggered-action</span> does not
activate other triggers because the <span class="italic">triggered-action</span> of
a before trigger cannot contain any updates.
</dd>
<dt class="bold">AFTER</dt>
<dd>Specifies that the trigger is an <span class="italic">after</span> trigger. The database manager executes the <span class="italic">triggered-action</span> after it applies any changes caused by an insert, delete, or update
operation on the subject table.
</dd>
<dt class="bold">INSTEAD OF</dt>
<dd>Specifies that the trigger is an instead of trigger. The
associated triggered action replaces the action against the subject view.
Only one INSTEAD OF trigger is allowed for each kind of operation on a given
subject view. The database manager executes the <span class="italic">triggered-action</span> instead of the insert, delete, or update operation on the subject view.
</dd>
<dt class="bold">INSERT</dt>
<dd>Specifies that the trigger is an insert trigger. The database manager executes
the <span class="italic">triggered-action</span> whenever there is an insert operation
on the subject table.
</dd>
<dt class="bold">DELETE</dt>
<dd>Specifies that the trigger is a delete trigger. The database manager executes
the<span class="italic"> triggered-action</span> whenever there is a delete operation
on the subject table.
<p>A DELETE trigger cannot be added to a table with a
referential constraint of ON DELETE CASCADE.</p>
</dd>
<dt class="bold">UPDATE</dt>
<dd>Specifies that the trigger is an update trigger. The database manager executes
the<span class="italic"> triggered-action</span> whenever there is an update operation
on the subject table.
<p>An UPDATE trigger event cannot be added to a table
with a referential constraint of ON DELETE SET NULL.</p>
<p> If an explicit <span class="italic">column-name</span> list is not specified, an update operation
on any column of the subject table, including columns that are subsequently
added with the ALTER TABLE statement, activates the <span class="italic">triggered-action.</span></p>
<dl class="parml">
<dt class="bold">OF <span class="italic">column-name</span>, ... </dt>
<dd> Each <span class="italic">column-name </span> specified must
be a column of the subject table, and must appear in the list only once.
An update operation on any of the listed columns activates the <span class="italic">triggered-action</span>. This clause cannot be specified for an INSTEAD OF trigger.
</dd>
</dl>
</dd>
<dt class="bold">ON <span class="italic">table-name</span></dt>
<dd>Identifies the subject table of a BEFORE or AFTER trigger
definition. The name must identify a base table that exists at the current
server, but must not identify a catalog table, a table in QTEMP, or a global
temporary table.
</dd>
<dt class="bold">ON <span class="italic">view-name</span></dt>
<dd>Identifies the subject view of an INSTEAD OF trigger definition. The
name must identify a view that exists at the current server, but must not
identify a catalog view, or a view in QTEMP. The name must not specify a view
that is defined using WITH CHECK OPTION, or a view on which a WITH CHECK OPTION
view has been defined, directly or indirectly
</dd>
<dt class="bold">REFERENCING</dt><a id="idx2170" name="idx2170"></a><a id="idx2171" name="idx2171"></a>
<dd>Specifies the correlation names for the transition tables and the table
names for the transition tables. <span class="italic">Correlation-names</span> identify
a specific row in the set of rows affected by the triggering SQL operation. <span class="italic">Table-identifiers</span> identify the complete set of affected
rows.
<p>Each row affected by the triggering SQL operation is available to
the <span class="italic">triggered-action</span> by qualifying columns with <span class="italic">correlation-names</span> specified as follows:</p>
<dl class="parml">
<dt class="bold">OLD ROW AS <span class="italic">correlation-name</span></dt>
<dd>Specifies a correlation name that identifies the values in the row prior
to the triggering SQL operation.
</dd>
<dt class="bold">NEW ROW AS <span class="italic">correlation-name</span></dt>
<dd>Specifies a correlation name which identifies the values in the row
as modified by the triggering SQL operation and any SET statement in a before
trigger that has already executed.
</dd>
</dl>The complete set of rows affected by the triggering SQL operation
is available to the <span class="italic">triggered-action</span> by using a temporary
table name specified as follows:
<dl class="parml">
<dt class="bold">OLD TABLE AS<span class="italic"> table-identifier</span></dt>
<dd>Specifies the name of a temporary table that identifies the values in
the complete set of affected rows prior to the triggering SQL operation.
The OLD TABLE includes the rows that were affected by the trigger if the current
activation of the trigger was caused by statements in the <span class="italic">SQL-trigger-body</span> of a trigger.
</dd>
<dt class="bold">NEW TABLE AS<span class="italic"> table-identifier</span></dt>
<dd>Specifies the name of a temporary table that identifies the state of
the complete set of affected rows as modified by the triggering SQL operation
and by any SET statement in a before trigger that has already been executed.
</dd>
</dl>
<p>Only one OLD and one NEW <span class="italic">correlation-name</span> may
be specified for a trigger. Only one OLD_TABLE and one NEW_TABLE <span class="italic">table-identifier</span> may be specified for a trigger. All of the <span class="italic">correlation-names</span> and <span class="italic">table-identifier</span>s must
be unique from one another.</p>
<p>The OLD <span class="italic">correlation-name</span> and the OLD_TABLE <span class="italic">table-identifier</span> are valid
only if the triggering event is either a delete operation or an update operation.
For a delete operation, the OLD <span class="italic">correlation-name</span> captures
the values of the columns in the deleted row, and the OLD_TABLE <span class="italic">table-identifier</span> captures the values in the set of deleted rows. For
an update operation, OLD <span class="italic">correlation-name</span> captures
the values of the columns of a row before the update operation, and the OLD_TABLE <span class="italic">table-identifier</span> captures the values in the set of updated
rows.</p>
<p>The NEW ROW <span class="italic">correlation-name</span> and the NEW
TABLE <span class="italic">table-identifier</span> are valid only if the triggering
event is either an INSERT operation or an UPDATE operation. For both operations,
the NEW ROW <span class="italic">correlation-name</span> captures the values of
the columns in the inserted or updated row, and the NEW TABLE <span class="italic">table-identifier</span> captures the values in the set of inserted or updated
rows. For before triggers, the values of the updated rows include the changes
from any SET statements in the <span class="italic">triggered-action</span> of
before triggers.</p>
<p>The OLD ROW and NEW ROW <span class="italic">correlation-name</span> variables cannot be modified in an AFTER trigger or
INSTEAD OF trigger.</p>
<p>The tables below summarizes the allowable combinations
of correlation variables and transition tables.</p>
<p>Granularity: <span class="bold">FOR EACH ROW</span></p>
<a name="wq1352"></a>
<table id="wq1352" width="100%" summary="" border="1" frame="border" rules="all">
<thead valign="bottom">
<tr valign="bottom">
<th id="wq1353" width="18%" align="left">MODE</th>
<th id="wq1354" width="17%" align="left">Activation Time</th>
<th id="wq1355" width="17%" align="left">Triggering Operation</th>
<th id="wq1356" width="19%" align="left">Correlation Variables Allowed</th>
<th id="wq1357" width="26%" align="left">Transition Tables Allowed</th>
</tr>
</thead>
<tbody valign="top">
<tr>
<td rowspan="6" headers="wq1353">DB2ROW</td>
<td rowspan="3" headers="wq1354">BEFORE</td>
<td headers="wq1355">DELETE</td>
<td headers="wq1356">OLD</td>
<td rowspan="9" headers="wq1357">NONE</td>
</tr>
<tr>
<td headers="wq1355">INSERT</td>
<td headers="wq1356">NEW</td>
</tr>
<tr>
<td headers="wq1355">UPDATE</td>
<td headers="wq1356">OLD, NEW</td>
</tr>
<tr>
<td rowspan="3" headers="wq1354">AFTER or INSTEAD OF</td>
<td headers="wq1355">DELETE</td>
<td headers="wq1356">OLD</td>
</tr>
<tr>
<td headers="wq1355">INSERT</td>
<td headers="wq1356">NEW</td>
</tr>
<tr>
<td headers="wq1355">UPDATE</td>
<td headers="wq1356">OLD, NEW</td>
</tr>
<tr>
<td rowspan="6" headers="wq1353">DB2SQL</td>
<td rowspan="3" headers="wq1354">BEFORE</td>
<td headers="wq1355">DELETE</td>
<td headers="wq1356">OLD</td>
</tr>
<tr>
<td headers="wq1355">INSERT</td>
<td headers="wq1356">NEW</td>
</tr>
<tr>
<td headers="wq1355">UPDATE</td>
<td headers="wq1356">OLD, NEW</td>
</tr>
<tr>
<td rowspan="3" headers="wq1354">AFTER or INSTEAD OF</td>
<td headers="wq1355">DELETE</td>
<td headers="wq1356">OLD</td>
<td headers="wq1357">OLD TABLE</td>
</tr>
<tr>
<td headers="wq1355">INSERT</td>
<td headers="wq1356">NEW</td>
<td headers="wq1357">NEW TABLE</td>
</tr>
<tr>
<td headers="wq1355">UPDATE</td>
<td headers="wq1356">OLD, NEW</td>
<td headers="wq1357">OLD TABLE, NEW TABLE</td>
</tr>
</tbody>
</table>
<p>Granularity: <span class="bold">FOR EACH STATEMENT</span></p>
<a name="wq1358"></a>
<table id="wq1358" width="100%" summary="" border="1" frame="border" rules="all">
<thead valign="bottom">
<tr valign="bottom">
<th id="wq1359" align="left">MODE</th>
<th id="wq1360" align="left">Activation Time</th>
<th id="wq1361" align="left">Triggering Operation</th>
<th id="wq1362" align="left">Correlation Variables Allowed</th>
<th id="wq1363" align="left">Transition Tables Allowed</th>
</tr>
</thead>
<tbody valign="top">
<tr>
<td rowspan="3" headers="wq1359">DB2SQL</td>
<td rowspan="3" headers="wq1360">AFTER</td>
<td headers="wq1361">DELETE</td>
<td rowspan="3" headers="wq1362">NONE</td>
<td headers="wq1363">OLD TABLE</td>
</tr>
<tr>
<td headers="wq1361">INSERT</td>
<td headers="wq1363">NEW TABLE</td>
</tr>
<tr>
<td headers="wq1361">UPDATE</td>
<td headers="wq1363">OLD TABLE, NEW TABLE</td>
</tr>
</tbody>
</table>
<p>A transition variable that has a character data type inherits
the CCSID of the column of the subject table. During the execution of the <span class="italic">triggered-action</span>, the transition variables are treated
like variables. Therefore, character conversion might occur.</p>
<p>The temporary
transition tables are read-only. They cannot be modified.</p>
<p>The scope
of each <span class="italic">correlation-name</span> and each <span class="italic">table-identifier</span> is the entire trigger definition.</p>
</dd>
<dt class="bold">FOR EACH ROW</dt>
<dd>Specifies that the database manager executes the <span class="italic">triggered-action</span> for each row of the subject table that the triggering operation modifies.
If the triggering operation does not modify any rows, the <span class="italic">triggered-action</span> is not executed.
</dd>
<dt class="bold">FOR EACH STATEMENT</dt>
<dd>Specifies that the database manager executes the <span class="italic">triggered-action</span> only once for the triggering operation. Even if the triggering operation
does not modify or delete any rows, the triggered action is still executed
once.
<p>FOR EACH STATEMENT cannot be specified for a BEFORE or
INSTEAD OF trigger.</p>
<p>FOR EACH STATEMENT cannot be specified for a MODE
DB2ROW trigger.</p>
</dd>
<dt class="bold">MODE DB2SQL</dt>
<dd>MODE DB2SQL triggers are activated after all of the row operations have
occurred.
</dd>
<dt class="bold">MODE DB2ROW</dt>
<dd>MODE DB2ROW triggers are activated on each row operation.
<p> MODE DB2ROW
is valid for both the BEFORE and AFTER activation time.</p>
</dd>
<dt class="bold"><span class="italic">triggered-action</span></dt>
<dd>Specifies the action to be performed when a trigger is activated. The <span class="italic">triggered-action</span> is composed of one or more SQL statements
and by an optional condition that controls whether the statements are executed.
<dl class="parml">
<dt class="bold"><span class="italic">SET OPTION-statement</span></dt>
<dd>Specifies the options that will be used to create the trigger. For example,
to create a debuggable trigger, the following statement could be included:
<pre class="xmp"><span class="bold">SET OPTION DBGVIEW = *SOURCE</span> </pre>For more information,
see <a href="rbafzmstsoption.htm#soption">SET OPTION</a>.
<p>The options CLOSQLCSR, CNULRQD, COMPILEOPT,
NAMING, and SQLCA are not allowed in the CREATE TRIGGER statement.</p>
<p>The options DATFMT, DATSEP, TIMFMT, and TIMSEP cannot be used if OLD ROW or
NEW ROW is specified.</p>
</dd>
<dt class="bold">WHEN (<span class="italic">search-condition</span>)</dt>
<dd>Specifies a condition that evaluates to true, false, or unknown.
The triggered SQL statements are executed only if the <span class="italic">search-condition</span> evaluates to true. If the WHEN clause is omitted, the
associated SQL statements are always executed. A WHEN clause must not be specified
with an INSTEAD OF trigger.
</dd>
<dt class="bold"><span class="italic">SQL-trigger-body</span></dt>
<dd>Specifies a single SQL statement, including a compound statement. See <a href="rbafzmstsqlcontstmts.htm#sqlcontstmts">SQL control statements</a> for more information about defining SQL triggers.
<p>A call to a procedure that issues a CONNECT, SET CONNECTION, RELEASE, DISCONNECT,
COMMIT, ROLLBACK, SET TRANSACTION, and SET RESULT SETS statement is not allowed
in the <span class="italic">triggered-action</span> of a trigger.</p>
<p>If the trigger is a before trigger, then the <span class="italic">SQL-trigger-body</span> must not contain an INSERT, UPDATE, DELETE, ALTER TABLE, COMMENT, any
CREATE statement, DECLARE GLOBAL TEMPORARY TABLE, DROP, any GRANT statement,
LABEL, REFRESH TABLE, RENAME, or any REVOKE statement. It must not contain
a reference to a procedure or function that modifies SQL data.</p>
<p>An UNDO
handler is not allowed in a trigger.</p>
<p>All tables, views, aliases, distinct types,
user-defined functions, and procedures referenced in the <span class="italic">triggered-action</span> must exist at the current server when the trigger is
created. The table or view that an alias refers to must also exist when the
trigger is created. This includes objects in library QTEMP. While objects
in QTEMP can be referenced in the <span class="italic">triggered-action</span>,
dropping those objects in QTEMP will not cause the trigger to be dropped.</p>
<p>The statements in the <span class="italic">triggered-action</span> can
invoke a procedure or a user-defined function that can access a server other
than the current server if the procedure or user-defined function runs in
a different activation group.</p>
</dd>
</dl>
</dd>
</dl>
<a name="dtrig"></a>
<h3 id="dtrig"><a href="rbafzmst02.htm#ToC_954">Notes</a></h3>
<p><span class="bold">Trigger ownership:</span> If SQL names were specified:</p>
<ul>
<li>If a user profile with the same name as the schema into which the trigger
is created exists, the <span class="italic">owner</span> of the trigger is that
user profile.</li>
<li>Otherwise, the <span class="italic">owner</span> of the trigger is the user
profile or group user profile of the job executing the statement.</li></ul>
<p>If system names were specified, the <span class="italic">owner</span> of the
trigger is the user profile or group user profile of the job executing the
statement.</p>
<p><span class="bold">Trigger authority:</span> The trigger program object authorities
are:</p>
<ul>
<li>When SQL naming is in effect, the trigger program will be created with
the public authority of *EXCLUDE, and adopt authority from the schema qualifier
of the trigger-name if a user profile with that name exists. If a user profile
for the schema qualifier does exist, then the owner of the trigger program
will be the user profile for the schema qualifier. Note that the special
authorities *ALLOBJ and *SECADM are required to create the trigger program
object in the schema qualifier library if a user profile exists that has the
same name as the schema qualifier, and the name is different from the authorization
ID of the statement. If a user profile for the schema qualifier does not
exist, then the owner of the trigger program will be the user profile or group
user profile of the job executing the SQL CREATE TRIGGER statement. The group
user profile will be the owner of the trigger program object, only if OWNER(*GRPPRF)
was specified on the user's profile who is executing the statement. If the
owner of the trigger program is a member of a group profile, and if OWNER(*GRPPRF)
was specified on the user's profile, the program will run with the adopted
authority of the group profile.</li>
<li>When System naming is in effect, the trigger program will be created with
public authority of *EXCLUDE, and adopt authority from the user or group user
profile of the job executing the SQL CREATE TRIGGER statement.</li></ul>
<p><span class="bold">Activating a trigger:</span> Only insert, delete, or update
operations can activate a trigger. A delete operation that occurs as a result
of a referential constraint will not activate a trigger. Hence, </p>
<ul>
<li>A trigger with a DELETE trigger event cannot be added to a table with
a referential constraint of ON DELETE CASCADE.</li>
<li>A trigger with an UPDATE trigger event cannot be added to a table with
a referential constraint of ON DELETE SET NULL or ON DELETE SET DEFAULT.</li></ul>
<p>The activation of a trigger may cause <span class="italic">trigger cascading</span>. This is the result of the activation of one trigger that executes SQL
statements that cause the activation of other triggers or even the same trigger
again. The triggered actions may also cause updates as a result of the original
modification, which may result in the activation of additional triggers. With
trigger cascading, a significant chain of triggers may be activated causing
significant change to the database as a result of a single delete, insert
or update statement. The number of levels of cascading is limited to 200 or
the maximum amount of storage allowed in the job or process, whichever comes
first.</p>
<p><span class="bold">Adding triggers to enforce constraints:</span> Adding a
trigger to a table that already has rows in it will not cause the triggered
actions to be executed. Thus, if the trigger is designed to enforce constraints
on the data in the table, the data in the existing rows might not satisfy
those constraints.</p>
<p><span class="bold">Multiple triggers:</span> Multiple triggers that
have the same triggering SQL operation and activation time can be defined
on a table. The triggers are activated based on the mode and the order in
which they were created:</p>
<ul>
<li>MODE DB2ROW triggers (and native triggers created via the ADDPFTRG CL
command) are fired first in the order in which they were created</li>
<li>MODE DB2SQL triggers are fired next in the order in which they were created</li></ul><p class="indatacontent"> For example, a MODE DB2ROW trigger that was created first is executed
first, the MODE DB2ROW trigger that was created second is executed second.</p>
<p>A maximum of 300 triggers can be added to any given source table.</p>
<p><span class="bold">Adding columns to a subject table or a table referenced
in the triggered action:</span> If a column is added to the subject table after
triggers have been defined, the following rules apply:</p>
<ul>
<li>If the trigger is an UPDATE trigger that was defined without an explicit
column list, then an update to the new column will cause the activation of
the trigger.</li>
<li>If the SQL statements in the<span class="italic"> triggered-action</span> refer
to the triggering table, the new column is not accessible to the SQL statements
until the trigger is recreated.</li>
<li>The OLD_TABLE and NEW_TABLE transition tables will contain the new column,
but the column cannot be referenced unless the trigger is recreated.</li></ul>
<p>If a column is added to any table referenced by the SQL statements in the
triggered-action, the new column is not accessible to the SQL statements until
the trigger is recreated.</p>
<p><span class="bold">Dropping or revoking privileges on a table referenced
in the triggered action:</span> If an object such as a table, view or alias,
referenced in the <span class="italic">triggered-action</span> is dropped, the
access plans of the statements that reference the object will be rebuilt when
the trigger is fired. If the object does not exist at that time, the corresponding
INSERT, UPDATE or DELETE operation on the subject table will fail.</p>
<p>If a privilege that the creator of the trigger is required to have for
the trigger to execute is revoked, the access plans of the statements that
reference the object will be rebuilt when the trigger is fired. If the appropriate
privilege does not exist at that time, the corresponding INSERT, UPDATE or
DELETE operation on the subject table will fail.</p>
<p><span class="bold">Errors executing triggers:</span> Errors that occur during
the execution of <span class="italic">SQL-trigger-body</span> statements are returned
using SQLSTATE 09000 and SQLCODE -723.</p>
<p><span class="bold">Special registers in triggers:</span> The values of the
special registers are saved before a trigger is activated and are restored
on return from the trigger. The values of the special registers are inherited
from the triggering SQL operation.</p>
<p><span class="bold">Performance considerations:</span> Create the trigger under
the isolation level that will most often by used by the application programs
that cause the trigger to fire. The SET OPTION statement can be used to explicitly
choose the isolation level.</p>
<p>ROW triggers (especially MODE DB2ROW triggers) perform much better than
TABLE level triggers.</p>
<p><span class="bold">Transaction isolation:</span> All triggers, when they are
activated, perform a SET TRANSACTION statement unless the isolation level
of the application program invoking the trigger is the same as the default
isolation level of the trigger program. This is necessary so that all of the
operations by the trigger are performed with the same isolation level as the
application program that caused the trigger to be run. The user may put their
own SET TRANSACTION statements in an <span class="italic">SQL-control-statement</span> in the <span class="italic">SQL-trigger-body</span> of the trigger. If
the user places a SET TRANSACTION statement within the <span class="italic">SQL-trigger-body</span> of the trigger, then the trigger will run with the isolation
level specified in the SET TRANSACTION statement, instead of the isolation
level of the application program that caused the trigger to be run.</p>
<p>If the application program that caused a trigger to be activated, is running
with an isolation level other than No Commit (COMMIT(*NONE) or COMMIT(*NC)),
the operations within the trigger will be run under commitment control and
will not be committed or rolled back until the application commits its current
unit of work. If ATOMIC is specified in the <span class="italic">SQL-trigger-body</span> of the trigger, and the application program that caused the ATOMIC trigger
to be activated is running with an isolation level of No Commit (COMMIT(*NONE)
or COMMIT(*NC)), the operations within the trigger will not be run under commitment
control. If the application that caused the trigger to be activated is running
with an isolation level of No Commit (COMMIT(*NONE) or COMMIT(*NC)), then
the operations of a trigger are written to the database immediately, and cannot
be rolled back.</p>
<p> If both system triggers defined by the Add Physical File Trigger (ADDPFTRG)
CL command and SQL triggers defined by the CREATE TRIGGER statement are defined
for a table, it is recommended that the system triggers perform a SET TRANSACTION
statement so that they are run with the same isolation level as the original
application that caused the triggers to be activated. It is also recommended
that the system triggers run in the Activation Group of the calling application.
If system triggers run in a separate Activation Group (ACTGRP(*NEW)), then
those system triggers will not participate in the unit of the work for the
calling application, nor in the unit of work for any SQL triggers. System
triggers that run in a separate Activation Group are responsible for committing
or rolling back any database operations they perform under commitment control.
Note that SQL triggers defined by the CREATE TRIGGER statement always run
in the caller's Activation Group.</p>
<p>If the triggering application is running with commitment control, the operations
of an SQL trigger, and any cascaded SQL triggers, will be captured into a
sub-unit of work. If the operations of the trigger and any cascaded triggers
are successful, the operations captured in the sub-unit of work will be committed
or rolled back when the triggering application commits or rolls back its current
unit of work. Any system triggers that run in the same Activation Group as
the caller, and perform a SET TRANSACTION to the isolation level of the caller,
will also participate in the sub-unit of work. If the triggering application
is running without commit control, then the operations of the SQL triggers
will also be run without commitment control.</p>
<p>If an application that causes a trigger to be activated, is running with
an isolation level of No Commit (COMMIT(*NONE) or COMMIT(*NC)), and it issues
an INSERT, UPDATE, or DELETE statement that encounters an error during the
execution of the statement, no other the system and SQL triggers will still
be activated following the error for that operation. However, some number
of changes will already have been performed. If the triggering application
is running with commitment control, the operations of any triggers that are
captured in a sub-unit of work will be rolled back when the first error is
encountered, and no additional triggers will be activated for the current
INSERT, UPDATE, or DELETE statement.</p>
<p><span class="bold">Transition variable values and INSTEAD OF triggers:</span> The initial values for new transition variables or new transition table
columns visible in an INSTEAD OF INSERT trigger are set as follows:</p>
<ul>
<li>If a value is explicitly specified for a column in the INSERT statement,
the corresponding new transition variable or new transition table column is
that explicitly specified value.</li>
<li>If a value is not explicitly specified for a column in the INSERT statement
or the DEFAULT keyword is specified, the corresponding new transition variable
or new transition table column is:
<ul>
<li>the default value of the underlying table column if the view column is
updatable (without the INSTEAD OF trigger) and not based on a generated column
(identity column or ROWID),</li>
<li>otherwise, the null value.</li></ul></li></ul>
<p>The initial values for new transition variables or new transition
table columns visible in an INSTEAD OF UPDATE trigger are set as follows:</p>
<ul>
<li>If a value is explicitly specified for a column in the UPDATE statement,
the corresponding new transition variable or new transition table column is
that explicitly specified value.</li>
<li>If the DEFAULT keyword is explicitly specified for a column in the UPDATE
statement, the corresponding new transition variable or new transition table
column is:
<ul>
<li>the default value of the underlying table column if the view column is
updatable (without the INSTEAD OF trigger) and not based on a generated column
(identity column or ROWID),</li>
<li>otherwise, the null value.</li></ul></li>
<li>Otherwise, the corresponding new transition variable or new transition
table column is the existing value of the column in the row.</li></ul>
<p><span class="bold">Triggered actions in the catalog:</span> At the
time the trigger is created, the <span class="italic">triggered-action</span> is
modified as a result of the CREATE TRIGGER statement:</p>
<ul>
<li>Naming mode is switched to SQL naming.</li>
<li>All unqualified object references are explicitly qualified</li>
<li>All implicit column lists (e.g. SELECT *, INSERT with no column list,
UPDATE SET ROW) are expanded to be the list of actual column names.</li></ul><p class="indatacontent"> The modified <span class="italic">triggered-action</span> is stored in the
catalog.</p>
<p><span class="bold">Renaming or moving a table referenced in the triggered
action:</span> Any table (including the subject table) referenced in a <span class="italic">triggered-action</span> can be moved or renamed. However, the <span class="italic">triggered-action</span> will continue to reference the old name or schema. An
error will occur if the referenced table is not found when the <span class="italic">triggered-action</span> is executed. Hence, you should drop the trigger and
then re-create the trigger so that it refers to the renamed or moved table.</p>
<p><span class="bold">Datetime considerations:</span> If OLD ROW or NEW ROW is
specified, the date or time constants and the string representation of dates
and times in variables that are used in SQL statements in the <span class="italic">triggered-action</span> must have a format of ISO, EUR, JIS, USA, or must match
the date and time formats specified when the table was created if it was created
using DDS and the CRTPF CL command. If the DDS specifications contain multiple
different date or time formats, the trigger cannot be created.</p>
<p><span class="bold">Operations that invalidate triggers:</span> An <span class="italic">inoperative trigger</span> is a trigger that is no longer available
to be activated. If a trigger becomes invalid, no INSERT, UPDATE, or DELETE
operations will be allowed on the subject table or view. A trigger becomes
invalid if: </p>
<ul>
<li>The SQL statements in the <span class="italic">triggered-action</span> reference
the subject table or view, the trigger is a self-referencing trigger, and
the table or view is duplicated using the system CRTDUPOBJ CL command, or</li>
<li>The SQL statements in the <span class="italic">triggered-action</span> reference
tables or views in the from library and the objects are not found in the new
library when the table or view is duplicated using the system CRTDUPOBJ CL
command, or</li>
<li>The table or view is restored to a new library using the system RSTOBJ
or RSTLIB CL commands, and the <span class="italic">triggered-action</span> references
the subject table or subject view, the trigger is a self-referencing trigger.</li></ul><p class="indatacontent">An invalid trigger must first be dropped before it can be recreated by
issuing a CREATE TRIGGER statement. Note that dropping and recreating a trigger
will affect the activation order of a trigger if multiple triggers for the
same triggering operation and activation time are defined for the subject
table.</p>
<p><span class="bold">Trigger program object:</span> When a trigger is created,
SQL creates a temporary source file that will contain C source code with embedded
SQL statements. A program object is then created using the CRTPGM command.
The SQL options used to create the program are the options that are in effect
at the time the CREATE TRIGGER statement is executed. The program is created
with ACTGRP(*CALLER).</p>
<p>The program is created with STGMDL(*SNGLVL). If the trigger
runs on behalf of an application that uses STGMDL(*TERASPACE) and also uses
commitment control, the entire application will need to run under a job scoped
commitment definition (STRCMTCTL CMTSCOPE(*JOB)).</p>
<p>The trigger will execute with the adopted authority of the <span class="italic">owner</span> of the trigger.</p>
<a name="wq1364"></a>
<h3 id="wq1364"><a href="rbafzmst02.htm#ToC_955">Examples</a></h3>
<p><span class="italic">Example 1:</span> Create two triggers that track the number
of employees that a company manages. The triggering table is the EMPLOYEE
table, and the triggers increment and decrement a column with the total number
of employees in the COMPANY_STATS table. The COMPANY_STATS table has the following
properties: </p>
<pre class="xmp"> <span class="bold">CREATE TABLE</span> COMPANY_STATS
<span class="bold">(</span>NBEMP <span class="bold">INTEGER,</span>
NBPRODUCT <span class="bold">INTEGER,</span>
REVENUE <span class="bold">DECIMAL(</span>15,0<span class="bold">))</span></pre><p class="indatacontent">This
example uses row triggers to maintain summary data in another table.</p>
<p>Create the first trigger, NEW_HIRE, so that it increments the number of
employees each time a new person is hired; that is, each time a new row is
inserted into the EMPLOYEE table, increase the value of column NBEMP in table
COMPANY_STATS by 1.</p>
<pre class="xmp"> <span class="bold">CREATE TRIGGER</span> NEW_HIRE
<span class="bold">AFTER INSERT ON</span> EMPLOYEE
<span class="bold">FOR EACH ROW MODE DB2SQL</span>
<span class="bold">UPDATE</span> COMPANY_STATS <span class="bold">SET</span> NBEMP = NBEMP + 1 </pre>
<p>Create the second trigger, FORM_EMP, so that it decrements the number of
employees each time an employee leaves the company; that is, each time a row
is deleted from the table EMPLOYEE, decrease the value of column NBEMP in
table COMPANY_STATS by 1.</p>
<pre class="xmp"> <span class="bold">CREATE TRIGGER</span> FORM_EMP
<span class="bold">AFTER DELETE ON</span> EMPLOYEE
<span class="bold">FOR EACH ROW MODE DB2SQL</span>
<span class="bold">BEGIN ATOMIC</span>
<span class="bold">UPDATE</span> COMPANY_STATS <span class="bold">SET</span> NBEMP = NBEMP - 1;
<span class="bold">END</span> </pre>
<p><span class="italic">Example 2:</span> Create a trigger, REORDER, that invokes
user-defined function ISSUE_SHIP_REQUEST to issue a shipping request whenever
a parts record is updated and the on-hand quantity for the affected part is
less than 10% of its maximum stocked quantity. User-defined function ISSUE_SHIP_REQUEST
orders a quantity of the part that is equal to the part's maximum stocked
quantity minus its on-hand quantity. The function eliminates any duplicate
requests to order the same PARTNO and sends the unique order to the appropriate
supplier.</p>
<p>This example also shows how to define the trigger as a statement trigger
instead of a row trigger. For each row in the transition table that evaluates
to true for the WHERE clause, a shipping request is issued for the part.</p>
<pre class="xmp"> <span class="bold">CREATE TRIGGER</span> REORDER
<span class="bold">AFTER UPDATE OF</span> ON_HAND, MAX_STOCKED <span class="bold">ON</span> PARTS
<span class="bold">REFERENCING NEW_TABLE AS</span> NTABLE
<span class="bold">FOR EACH STATEMENT MODE DB2SQL</span>
<span class="bold">BEGIN ATOMIC</span>
<span class="bold">SELECT </span>ISSUE_SHIP_REQUEST<span class="bold">(</span>MAX_STOCKED - ON_HAND, PARTNO<span class="bold">)</span>
<span class="bold">FROM</span> NTABLE
<span class="bold">WHERE </span>ON_HAND &lt; 0.10 * MAX_STOCKED;
<span class="bold">END</span> </pre>
<p><span class="italic">Example 3:</span> Assume that table EMPLOYEE contains
column SALARY. Create a trigger, SAL_ADJ, that prevents an update to an employee's
salary that exceeds 20% and signals such an error. Have the error that is
returned with an SQLSTATE of 75001 and a description. This example shows that
the SIGNAL SQLSTATE statement is useful for restricting changes that violate
business rules.</p>
<pre class="xmp"> <span class="bold">CREATE TRIGGER</span> SAL_ADJ
<span class="bold">AFTER UPDATE OF</span> SALARY <span class="bold">ON</span> EMPLOYEE
<span class="bold">REFERENCING OLD AS</span> OLD_EMP
<span class="bold">NEW AS</span> NEW_EMP
<span class="bold">FOR EACH ROW MODE DB2SQL</span>
<span class="bold">WHEN (</span>NEW_EMP.SALARY > <span class="bold">(</span>OLD_EMP.SALARY *1.20<span class="bold">))</span>
<span class="bold">BEGIN ATOMIC</span>
<span class="bold">SIGNAL SQLSTATE</span> '75001'<span class="bold">(</span>'Invalid Salary Increase - Exceeds 20%'<span class="bold">);</span>
<span class="bold">END</span> </pre>
<hr /><br />
[ <a href="#Top_Of_Page">Top of Page</a> | <a href="rbafzmsthctabl.htm">Previous Page</a> | <a href="rbafzmsthcview.htm">Next Page</a> | <a href="rbafzmst02.htm#wq1">Contents</a> |
<a href="rbafzmstindex.htm#index">Index</a> ]
<a id="Bot_Of_Page" name="Bot_Of_Page"></a>
</body>
</html>
Reference in New Issue View Git Blame Copy Permalink