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/rbafzmstch1table.htm

444 lines
31 KiB
HTML
Raw Permalink Normal View History

Add readme and dist folders
2024-04-02 14:02:31 +00:00
<?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="definition, table, column, base table,
result table, system table name, system column name, nodegroup, distributed,
distributed tables, partitioning key, composite, key, unique, composite key,
unique key, constraints, unique constraint, referential constraint,
check constraint, primary, primary key, primary index, unique index, foreign key,
foreign, parent, parent table, parent key, dependent table, descendent table,
referential cycle, referential integrity, dependent row, descendent row,
parent row, self-referencing row, self-referencing table, descendent, dependent,
self-referencing, row, insert rule with referential constraint, description,
RESTRICT delete rule, CASCADE delete rule, SET NULL delete rule,
SET DEFAULT delete rule, keyword SET NULL delete rule, NULL, delete rules,
delete-connected table, index, trigger" />
<title>Tables</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="ch1table"></a>
<h2 id="ch1table"><a href="rbafzmst02.htm#ToC_27">Tables</a></h2><a id="idx28" name="idx28"></a><a id="idx29" name="idx29"></a>
<p><span class="italic">Tables</span> are logical structures maintained by the database manager.
Tables are made up of columns and rows. There is no inherent order of the
rows within a table. At the intersection of every column and row is a specific
data item called a <span class="italic">value</span>. A <span class="italic">column</span> is a set of values of the same type. A <span class="italic">row</span> is
a sequence of values such that the <var class="pv">n</var>th value is a value of the <var class="pv">n</var>th column of the table.</p><a id="idx30" name="idx30"></a><a id="idx31" name="idx31"></a>
<p>There are three types of tables:</p>
<ul>
<li>A <span class="italic">base table</span> is created with the CREATE TABLE
statement and is used to hold persistent user data. For more information see <a href="rbafzmsthctabl.htm#hctabl">CREATE TABLE</a>.<a id="idx32" name="idx32"></a><a id="idx33" name="idx33"></a>
<p>A base
table has a name and may have a different system name. The system name is
the name used by i5/OS. Either name is acceptable wherever a <span class="italic">table-name</span> is specified in SQL statements.</p><a id="idx34" name="idx34"></a><a id="idx35" name="idx35"></a>
<p>A column of a base table has a name and may have
a different system column name. The system column name is the name used by i5/OS.
Either name is acceptable wherever <span class="italic">column-name</span> is
specified in SQL statements. For more information see <a href="rbafzmsthctabl.htm#hctabl">CREATE TABLE</a>.</p><a id="idx36" name="idx36"></a><a id="idx37" name="idx37"></a><a id="idx38" name="idx38"></a><a id="idx39" name="idx39"></a>
<p>A <span class="italic">materialized query table</span> is used to
contain materialized data that is derived from one or more source tables specified
by a <span class="italic">select-statement</span>. A source table is a base table,
view, table expression, or user-defined table function. The <span class="italic">select-statement</span> specifies the query that is used to refresh the data
in the materialized query table.</p>
<p>A <span class="italic">partitioned table</span> is a table whose data is contained in one or more local partitions (members).
There are two mechanisms that can be specified to determine into which partition
a specific row will be inserted. Range partitioning allows a user to specify
different ranges of values for each partition. When a row is inserted, the
values specified in the row are compared to the specified ranges to determine
which partition is appropriate. Hash partitioning allows a user to specify
a partitioning key on which a hash algorithm is used to determine which partition
is appropriate. The <span class="italic">partitioning key</span> is a set of one
or more columns in a partitioned table that are used to determine in which
partition a row belongs.</p>
<p>A <span class="italic">distributed table</span> is
a table whose data is partitioned across a nodegroup. A <span class="italic">nodegroup</span> is an object that provides a logical grouping of a set of two
or more systems. The <span class="italic">partitioning key</span> is a set of
one or more columns in a distributed table that are used to determine on which
system a row belongs. For more information about distributed tables, see the <a href="../dbmult/rzaf3kickoff.htm">DB2&reg; Multisystem</a> book.</p></li>
<li>A <span class="italic">result table</span> is a set of rows that the database manager
selects or generates, directly or indirectly, from one or more base tables.</li>
<li>A <span class="italic">declared temporary table</span> is created with a DECLARE
GLOBAL TEMPORARY TABLE statement and is used to hold temporary data on behalf
of a single application. This table is dropped implicitly when the application
disconnects from the database.</li></ul>
<a name="sqlkey"></a>
<h3 id="sqlkey"><a href="rbafzmst02.htm#ToC_28">Keys</a></h3><a id="idx40" name="idx40"></a><a id="idx41" name="idx41"></a><a id="idx42" name="idx42"></a><a id="idx43" name="idx43"></a>
<p>A <span class="italic">key</span> is one or more columns that are identified
as such in the description of an index, unique constraint, or a referential
constraint. The same column can be part of more than one key.</p>
<p>A <span class="italic">composite key</span> is an ordered set of columns of
the same base table. The ordering of the columns is not constrained by their
ordering within the base table. The term <span class="italic">value</span> when
used with respect to a composite key denotes a composite value. Thus, a rule
such as &quot;the value of the foreign key must be equal to the value of the
primary key&quot; means that each component of the value of the foreign key
must be equal to the corresponding component of the value of the primary key.</p>
<a name="wq8"></a>
<h3 id="wq8"><a href="rbafzmst02.htm#ToC_29">Constraints</a></h3><a id="idx44" name="idx44"></a><a id="idx45" name="idx45"></a><a id="idx46" name="idx46"></a><a id="idx47" name="idx47"></a><a id="idx48" name="idx48"></a><a id="idx49" name="idx49"></a><a id="idx50" name="idx50"></a>
<p>A <span class="italic">constraint</span> is a rule that the database manager
enforces. There are three types of constraints: </p>
<ul>
<li>A <span class="italic">unique constraint</span> is a rule that forbids duplicate
values in one or more columns within a table. Unique and primary keys are
the supported unique constraints. For example, a unique constraint can be
defined on the supplier identifier in the supplier table to ensure that the
same supplier identifier is not given to two suppliers.</li>
<li>A <span class="italic">referential constraint</span> is a logical rule about
values in one or more columns in one or more tables. For example, a set
of tables shares information about a corporation's suppliers. Occasionally,
a supplier's ID changes. You can define a referential constraint stating
that the ID of the supplier in a table must match a supplier ID in the supplier
information. This constraint prevents insert, update, or delete operations
that would otherwise result in missing supplier information.</li>
<li>A <span class="italic">check constraint</span> sets restrictions on data
added to a specific table. For example, a check constraint can ensure that
the salary level for an employee is at least $20,000 whenever salary data
is added or updated in a table containing personnel information.</li></ul>
<a name="pukeys"></a>
<h4 id="pukeys">Unique constraints</h4><a id="idx51" name="idx51"></a><a id="idx52" name="idx52"></a><a id="idx53" name="idx53"></a><a id="idx54" name="idx54"></a><a id="idx55" name="idx55"></a><a id="idx56" name="idx56"></a><a id="idx57" name="idx57"></a>
<p>A <span class="italic">unique constraint</span> is the rule that the values
of a key are valid only if they are unique. A key that is constrained to have
unique values is called a <span class="italic">unique key</span> and can be defined
by using the CREATE UNIQUE INDEX statement. The resulting unique index is
used by the database manager to enforce the uniqueness of the key during the
execution of INSERT and UPDATE statements. Alternatively: </p>
<ul>
<li>Unique keys can be defined as a primary key using a CREATE TABLE or ALTER
TABLE statement. A base table cannot have more than one primary key. A CHECK
constraint will be added implicitly to enforce the rule that the NULL value
is not allowed in the columns that make up the primary key. A unique index
on a primary key is called a <span class="italic">primary index</span>.</li>
<li>Unique keys can be defined using the UNIQUE clause of the CREATE TABLE
or ALTER TABLE statement. A base table can have more than one set of UNIQUE
keys.</li></ul>
<p>A unique key that is referenced by the foreign key of a referential constraint
is called the <span class="italic">parent key</span>. A parent key is either a
primary key or a UNIQUE key. When a base table is defined as a parent in a
referential constraint, the default parent key is its primary key.</p>
<p>For more information on defining unique constraints, see <a href="rbafzmstatabl.htm#atabl">ALTER TABLE</a> or <a href="rbafzmsthctabl.htm#hctabl">CREATE TABLE</a>.</p>
<a name="refintex"></a>
<h4 id="refintex">Referential constraints</h4><a id="idx58" name="idx58"></a><a id="idx59" name="idx59"></a><a id="idx60" name="idx60"></a><a id="idx61" name="idx61"></a>
<p><span class="italic">Referential integrity</span> is the state of a database
in which all values of all foreign keys are valid. A <span class="italic">foreign
key</span> is a key that is part of the definition of a referential constraint.
A <span class="italic">referential constraint</span> is the rule that the values
of the foreign key are valid only if: </p>
<ul>
<li>They appear as values of a parent key, or</li>
<li>Some component of the foreign key is null.</li></ul><a id="idx62" name="idx62"></a>
<p>The base table containing the parent key is called the <var class="pv">parent table</var> of the referential constraint, and the base table containing the foreign
key is said to be a <span class="italic">dependent</span> of that table.</p>
<p>Referential constraints are optional and can be defined in CREATE TABLE
statements and ALTER TABLE statements. Referential constraints are enforced
by the database manager during the execution of INSERT, UPDATE, and DELETE
statements. The enforcement is effectively performed at the completion of
the statement except for delete and update rules of RESTRICT which are enforced
as rows are processed.</p>
<p>Referential constraints with a delete or update rule of RESTRICT are always
enforced before any other referential constraints. Other referential constraints
are enforced in an order independent manner. That is, the order does not affect
the result of the operation. Within an SQL statement: </p>
<ul>
<li>A row can be marked for deletion by any number of referential constraints
with a delete rule of CASCADE.</li>
<li>A row can only be updated by one referential constraint with a delete
rule of SET NULL or SET DEFAULT.</li>
<li>A row that was updated by a referential constraint cannot also be marked
for deletion by another referential constraint with a delete rule of CASCADE.</li></ul><a id="idx63" name="idx63"></a><a id="idx64" name="idx64"></a><a id="idx65" name="idx65"></a><a id="idx66" name="idx66"></a><a id="idx67" name="idx67"></a><a id="idx68" name="idx68"></a><a id="idx69" name="idx69"></a><a id="idx70" name="idx70"></a><a id="idx71" name="idx71"></a><a id="idx72" name="idx72"></a><a id="idx73" name="idx73"></a><a id="idx74" name="idx74"></a><a id="idx75" name="idx75"></a><a id="idx76" name="idx76"></a><a id="idx77" name="idx77"></a><a id="idx78" name="idx78"></a><a id="idx79" name="idx79"></a><a id="idx80" name="idx80"></a><a id="idx81" name="idx81"></a><a id="idx82" name="idx82"></a><a id="idx83" name="idx83"></a><a id="idx84" name="idx84"></a><a id="idx85" name="idx85"></a><a id="idx86" name="idx86"></a><a id="idx87" name="idx87"></a><a id="idx88" name="idx88"></a><a id="idx89" name="idx89"></a><a id="idx90" name="idx90"></a>
<p>The rules of referential integrity involve the following concepts and terminology:</p>
<dl>
<dt class="bold">Parent key</dt>
<dd>A primary key or unique key of a referential constraint.
</dd>
<dt class="bold">Parent row</dt>
<dd>A row that has at least one dependent row.
</dd>
<dt class="bold">Parent table</dt>
<dd>A base table that is a parent in at least one referential constraint.
A base table can be defined as a parent in an arbitrary number of referential
constraints.
</dd>
<dt class="bold">Dependent table</dt>
<dd>A base table that is a dependent in at least one referential constraint.
A base table can be defined as a dependent in an arbitrary number of referential
constraints. A dependent table can also be a parent table.
</dd>
<dt class="bold">Descendent table</dt>
<dd>A base table is a descendent of base table T if it is a dependent of
T or a descendent of a dependent of T.
</dd>
<dt class="bold">Dependent row</dt>
<dd>A row that has at least one parent row.
</dd>
<dt class="bold">Descendent row</dt>
<dd>A row is a descendent of row p if it is a dependent of p or a descendent
of a dependent of p.
</dd>
<dt class="bold">Referential cycle</dt>
<dd>A set of referential constraints such that each table in the set is
a descendent of itself.
</dd>
<dt class="bold">Self-referencing row</dt>
<dd>A row that is a parent of itself.
</dd>
<dt class="bold">Self-referencing table</dt>
<dd>A base table that is a parent and a dependent in the same referential
constraint. The constraint is called a <span class="italic">self-referencing
constraint</span>.
</dd>
</dl>
<p>The insert rule of a referential constraint is that a nonnull insert value
of the foreign key must match some value of the parent key of the parent table.
The value of a composite foreign key is null if any component of the value
is null.</p><a id="idx91" name="idx91"></a>
<p>The update rule of a referential constraint is specified when the referential
constraint is defined. The choices are NO ACTION and RESTRICT. The update
rule applies when a row of the parent or dependent table is updated. The update
rule of a referential constraint is that a nonnull update value of a foreign
key must match some value of the parent key of the parent table. The value
of a composite foreign key is null if any component of the value is null.</p>
<p>The delete rule of a referential constraint is specified when the referential
constraint is defined. The choices are RESTRICT, NO ACTION, CASCADE, SET&trade; NULL or SET DEFAULT. SET NULL can be specified only if some column
of the foreign key allows null values.</p><a id="idx92" name="idx92"></a><a id="idx93" name="idx93"></a><a id="idx94" name="idx94"></a><a id="idx95" name="idx95"></a><a id="idx96" name="idx96"></a>
<p>The delete rule of a referential constraint applies when a row of the parent
table is deleted. More precisely, the rule applies when a row of the parent
table is the object of a delete or propagated delete operation (defined below)
and that row has dependents in the dependent table of the referential constraint.
Let P denote the parent table, let D denote the dependent table, and let p
denote a parent row that is the object of a delete or propagated delete operation.
If the delete rule is:</p>
<ul>
<li>RESTRICT or NO ACTION, an error is returned and no rows are deleted</li>
<li>CASCADE, the delete operation is propagated to the dependents of p in
D</li>
<li>SET NULL, each nullable column of the foreign key of each dependent of
p in D is set to null</li>
<li>SET DEFAULT, each column of the foreign key of each dependent of p in
D is set to its default value</li></ul>
<p>Each referential constraint in which a table is a parent has its own delete
rule, and all applicable delete rules are used to determine the result of
a delete operation. Thus, a row cannot be deleted if it has dependents in
a referential constraint with a delete rule of RESTRICT or NO ACTION, or if
the deletion cascades to any of its descendants that are dependents in a referential
constraint with the delete rule of RESTRICT or NO ACTION.</p>
<p>The deletion of a row from parent table P involves other tables and may
affect rows of these tables: </p>
<ul>
<li>If table D is a dependent of P and the delete rule is RESTRICT or NO ACTION,
D is involved in the operation but is not affected by the operation.</li>
<li>If D is a dependent of P and the delete rule is SET NULL, D is involved
in the operation, and rows of D may be updated during the operation.</li>
<li>If D is a dependent of P and the delete rule is SET DEFAULT, D is involved
in the operation, and rows of D may be updated during the operation.</li>
<li>If D is a dependent of P and the delete rule is CASCADE, D is involved
in the operation and rows of D may be deleted during the operation.
<p>If rows
of D are deleted, the delete operation on P is said to be propagated to D.
If D is also a parent table, the actions described in this list apply, in
turn, to the dependents of D.</p></li></ul><a id="idx97" name="idx97"></a><a id="idx98" name="idx98"></a>
<p>Any base table that may be involved in a delete operation on P is said
to be <span class="italic">delete-connected</span> to P. Thus, a base table is
delete-connected to base table P if it is a dependent of P or a dependent
of a base table to which delete operations from P cascade.</p>
<a name="chkconex"></a>
<h4 id="chkconex">Check constraints</h4><a id="idx99" name="idx99"></a>
<p>A <span class="italic">check constraint</span> is a rule that specifies which
values allowed in every row of a base table. The definition of a check constraint
contains a search condition that must not be FALSE for any row of the base
table. Each column referenced in the search condition of a check constraint
on a table T must identify a column of T. For more information on search conditions,
see <a href="rbafzmstch2srch.htm#ch2srch">Search conditions</a>.</p>
<p>A base table can have nore than one check constraint. Each check constraint
defined on a base table is enforced by the database manager when either of
the following occur: </p>
<ul>
<li>A row is inserted into that base table.</li>
<li>A row of that base table is updated.</li></ul>
<p>A check constraint is enforced by applying its search condition to each
row that is inserted or updated in that base table. An error is returned if
the result of the search condition is FALSE for any row.</p>
<p>For more information on defining check constraints, see <a href="rbafzmstatabl.htm#atabl">ALTER TABLE</a> or <a href="rbafzmsthctabl.htm#hctabl">CREATE TABLE</a>.</p>
<a name="ch1index"></a>
<h3 id="ch1index"><a href="rbafzmst02.htm#ToC_33">Indexes</a></h3><a id="idx100" name="idx100"></a>
<p>An <span class="italic">index</span> is a set of pointers to rows of a base
table. Each index is based on the values of data in one or more table columns.
An index is an object that is separate from the data in the table. When an
index is created, the database manager builds this structure and maintains
it automatically.</p>
<p>An index has a name and may have a different system name. The system name
is the name used by i5/OS. Either name is acceptable wherever an <span class="italic">index-name</span> is specified in SQL statements. For more information, see <a href="rbafzmstxcindx.htm#xcindx">CREATE INDEX</a>.</p>
<p>The database manager uses two types of indexes: </p>
<ul>
<li>Binary radix tree index
<p>Binary radix tree indexes provide a specific
order to the rows of a table. The database manager uses them to: </p>
<ul>
<li>Improve performance. In most cases, access to data is faster than without
an index.</li>
<li>Ensure uniqueness. A table with a unique index cannot have rows with identical
keys.</li></ul></li>
<li>Encoded vector index
<p>Encoded vector indexes do not provide a specific
order to the rows of a table. The database manager only uses these indexes
to improve performance.</p>
<p>An encoded vector access path works with the help of encoded vector indexes and provides access
to a database file by assigning codes to distinct key values and then representing
these values in an array. The elements of the array can be 1, 2, or 4 bytes
in length, depending on the number of distinct values that must be represented.
Because of their compact size and relative simplicity, encoded vector access
paths provide for faster scans that can be more easily processed in parallel.</p></li></ul>
<p>An <span class="italic">index</span> is created with the CREATE INDEX statement.
For more information about creating indexes, see <a href="rbafzmstxcindx.htm#xcindx">CREATE INDEX</a>.</p>
<p>For more information about <a href="http://www.ibm.com/servers/enable/site/education/abstracts/indxng_abs.html" target="_blank">accelerating
your queries with encoded vector indexes</a>
<img src="www.gif" alt="Link outside of Information Center" />, go
to the DB2 UDB for iSeries webpages.</p>
<a name="trigex"></a>
<h3 id="trigex"><a href="rbafzmst02.htm#ToC_34">Triggers</a></h3><a id="idx101" name="idx101"></a>
<p>A <span class="italic">trigger</span> defines a set of actions that
are executed automatically whenever a delete, insert, or update operation
occurs on a specified table or view. When such an SQL operation is executed,
the trigger is said to be activated.<sup class="fn"><a id="wq9" name="wq9" href="rbafzmstch1table.htm#wq10">2</a></sup></p>
<p>The set of actions can include almost any operation allowed on the system.
A few operations are not allowed, such as: </p>
<ul>
<li>Commit or rollback (if the same commitment definition is used for the
trigger actions and the triggering event)</li>
<li>CONNECT, SET CONNECTION, DISCONNECT, and RELEASE statements</li>
<li>SET SESSION AUTHORIZATION</li></ul>
<p>For a complete list of restrictions, see <a href="rbafzmsthctrigger.htm#hctrigger">CREATE TRIGGER</a> and the <a href="../dbp/rbafokickoff.htm">Database Programming</a> book.</p>
<p>Triggers can be used along with referential constraints and check constraints
to enforce data integrity rules. Triggers are more powerful than constraints
because they can also be used to cause updates to other tables, automatically
generate or transform values for inserted or updated rows, or invoke functions
that perform operations both inside and outside of the database manager. For example, instead
of preventing an update to a column if the new value exceeds a certain amount,
a trigger can substitute a valid value and send a notice to an administrator
about the invalid update.</p>
<p>Triggers are a useful mechanism to define and enforce transitional business
rules that involve different states of the data (for example, salary cannot
be increased by more than 10 percent). Such a limit requires comparing the
value of a salary before and after an increase. For rules that do not involve
more than one state of the data, consider using referential and check constraints.</p>
<p>Triggers also move the application logic that is required to enforce business
rules into the database, which can result in faster application development
and easier maintenance because the business rule is no longer repeated in
several applications, but one version is centralized to the trigger. With
the logic in the database, for example, the previously mentioned limit on
increases to the salary column of a table, the database manager checks the validity of
the changes that any application makes to the salary column. In addition,
the application programs do not need to be changed when the logic changes.</p>
<p>For more information about creating triggers, see <a href="rbafzmsthctrigger.htm#hctrigger">CREATE TRIGGER</a>.</p>
<p> Triggers are optional and are defined using the CREATE TRIGGER statement
or the ADDPFTRG (Add Physical File Trigger) CL command. Triggers are dropped
using the DROP TRIGGER statement or the RMVPFTRG (Remove Physical File Trigger)
CL command. For more information about creating triggers, see the CREATE TRIGGER
statement. For more information about triggers in general, see the <a href="rbafzmsthctrigger.htm#hctrigger">CREATE TRIGGER</a> statement
or the <a href="../dbp/rbafokickoff.htm">SQL Programming</a> and the <a href="../dbp/rbafokickoff.htm">Database
Programming</a> books.</p>
<p>There are a number of criteria that are defined when creating a trigger
which are used to determine when a trigger should be activated.</p>
<ul>
<li>The <span class="italic">subject table</span> defines the table
or view for which the trigger is defined.</li>
<li>The <span class="italic">trigger event</span> defines a specific SQL operation
that modifies the subject table. The operation could be delete, insert, or
update.</li>
<li>The <span class="italic">trigger activation time</span> defines whether the
trigger should be activated before or after the trigger event is performed
on the subject table.</li></ul>
<p>The statement that causes a trigger to be activated will include a <span class="italic">set of affected rows</span>. These are the rows of the subject
table that are being deleted, inserted or updated. The <span class="italic">trigger granularity</span> defines whether the actions of the trigger will be
performed once for the statement or once for each of the rows in the set of
affected rows.</p>
<p>The <span class="italic">trigger action</span> consists of an optional search
condition and a set of SQL statements that are executed whenever the trigger
is activated. The SQL statements are only executed if the search condition
evaluates to true.</p>
<p>The triggered action may refer to the values in the set of affected rows.
This is supported through the use of <span class="italic">transition variables</span>. Transition variables use the names of the columns in the subject table
qualified by a specified name that identifies whether the reference is to
the old value (prior to the update) or the new value (after the update). The
new value can also be changed using the SET transition-variable statement
in before update or insert triggers. Another means of referring to the values
in the set of affected rows is using <span class="italic">transition tables</span>. Transition tables also use the names of the columns of the subject
table but have a name specified that allows the complete set of affected rows
to be treated as a table. Transition tables can only be used in after triggers.
Separate transition tables can be defined for old and new values.</p>
<p>Multiple triggers can be specified for a combination of table, event, or
activation time. The order in which the triggers are activated is the same
as the order in which they were created. Thus, the most recently created trigger
will be the last trigger activated.</p>
<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.</p>
<p>The actions performed in the trigger are considered to be part of the operation
that caused the trigger to be executed. Thus, when the isolation level is
anything other than NC (No Commit) and the trigger actions are performed using
the same commitment definition as the trigger event: </p>
<ul>
<li>The database manager ensures that the operation and the triggers executed
as a result of that operation either all complete or are backed out. Operations
that occurred prior to the triggering operation are not affected.</li>
<li>The database manager effectively checks all constraints (except for a
constraint with a RESTRICT delete rule) after the operation and the associated
triggers have been executed.</li></ul>
<p>A trigger has an attribute that specifies whether it is allowed to delete
or update a row that has already been inserted or updated within the SQL statement
that caused the trigger to be executed. </p>
<ul>
<li>If ALWREPCHG(*YES) is specified when the trigger is defined, then within
an SQL statement:
<ul>
<li>The trigger is allowed to update or delete any row that was inserted or
already updated by that same SQL statement. This also includes any rows inserted
or updated by a trigger or referential constraint caused by the same SQL statement.</li></ul></li>
<li>If ALWREPCHG(*NO) is specified when the trigger is defined, then within
an SQL statement:
<ul>
<li>A row can be deleted by a trigger only if that row has not been inserted
or updated by that same SQL statement. If the isolation level is anything
other than NC (No Commit) and the trigger actions are performed using the
same commitment definition as the trigger event, this also includes any inserts
or updates by a trigger or referential constraint caused by the same SQL statement.</li>
<li>A row can be updated by a trigger only if that row has not already been
inserted or updated by that same SQL statement. If the isolation level is
anything other than NC (No Commit) and the trigger actions are performed using
the same commitment definition as the trigger event, this also includes any
inserts or updates by a trigger or referential constraint caused by the same
SQL statement.</li></ul></li></ul><p class="indatacontent">All triggers created by using the CREATE TRIGGER statement implicitly
have the ALWREPCHG(*YES) attribute.</p>
<hr /><div class="fnnum"><a id="wq10" name="wq10" href="rbafzmstch1table.htm#wq9">2</a>.</div>
<div class="fntext">The ADDPFTRG CL command also defines
a trigger that is activated on any read operation.</div>
<br />
<hr /><br />
[ <a href="#Top_Of_Page">Top of Page</a> | <a href="rbafzmstcollex.htm">Previous Page</a> | <a href="rbafzmstviewdef.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 Copy Permalink