101 lines
6.7 KiB
HTML
101 lines
6.7 KiB
HTML
<?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 lang="en-us" xml:lang="en-us">
|
||
<head>
|
||
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
|
||
<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="DC.Type" content="reference" />
|
||
<meta name="DC.Title" content="AFTER SQL triggers" />
|
||
<meta name="abstract" content="The WHEN condition can be used in an SQL trigger to specify a condition. If the condition evaluates to true, then the SQL statements in the SQL trigger routine body are run. If the condition evaluates to false, the SQL statements in the SQL trigger routine body are not run, and control is returned to the database system. This type of trigger is called an AFTER trigger." />
|
||
<meta name="description" content="The WHEN condition can be used in an SQL trigger to specify a condition. If the condition evaluates to true, then the SQL statements in the SQL trigger routine body are run. If the condition evaluates to false, the SQL statements in the SQL trigger routine body are not run, and control is returned to the database system. This type of trigger is called an AFTER trigger." />
|
||
<meta name="DC.subject" content="trigger, AFTER trigger, example, CREATE TRIGGER statement, examples" />
|
||
<meta name="keywords" content="trigger, AFTER trigger, example, CREATE TRIGGER statement, examples" />
|
||
<meta name="DC.Relation" scheme="URI" content="rbafysqltrig.htm" />
|
||
<meta name="copyright" content="(C) Copyright IBM Corporation 1998, 2006" />
|
||
<meta name="DC.Rights.Owner" content="(C) Copyright IBM Corporation 1998, 2006" />
|
||
<meta name="DC.Format" content="XHTML" />
|
||
<meta name="DC.Identifier" content="rbafyaftersql" />
|
||
<meta name="DC.Language" 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. -->
|
||
<link rel="stylesheet" type="text/css" href="./ibmdita.css" />
|
||
<link rel="stylesheet" type="text/css" href="./ic.css" />
|
||
<title>AFTER SQL triggers</title>
|
||
</head>
|
||
<body id="rbafyaftersql"><a name="rbafyaftersql"><!-- --></a>
|
||
<!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
|
||
<h1 class="topictitle1">AFTER SQL triggers</h1>
|
||
<div><p>The WHEN condition can be used in an SQL trigger to specify a condition.
|
||
If the condition evaluates to true, then the SQL statements in the SQL trigger
|
||
routine body are run. If the condition evaluates to false, the SQL statements
|
||
in the SQL trigger routine body are not run, and control is returned
|
||
to the database system. This type of trigger is called an AFTER trigger.</p>
|
||
<div class="section"><p>In the following example, a query is evaluated to determine if
|
||
the statements in the trigger routine body should be run when the trigger
|
||
is activated. </p>
|
||
<pre><strong>CREATE TABLE</strong> TodaysRecords(TodaysMaxBarometricPressure <strong>FLOAT</strong>,
|
||
TodaysMinBarometricPressure <strong>FLOAT</strong>)
|
||
|
||
<strong>CREATE TABLE</strong> OurCitysRecords(RecordMaxBarometricPressure <strong>FLOAT</strong>,
|
||
RecordMinBarometricPressure <strong>FLOAT</strong>)
|
||
|
||
<strong>CREATE TRIGGER</strong> UpdateMaxPressureTrigger
|
||
<strong>AFTER UPDATE OF</strong> TodaysMaxBarometricPressure<strong> ON</strong> TodaysRecords
|
||
<strong>REFERENCING NEW AS</strong> new_row
|
||
<strong>FOR EACH ROW MODE DB2ROW</strong>
|
||
<strong>WHEN</strong> (new_row.TodaysMaxBarometricPressure>
|
||
(<strong>SELECT</strong> <strong>MAX</strong>(RecordMaxBarometricPressure) <strong>FROM</strong>
|
||
OurCitysRecords))
|
||
<strong>UPDATE</strong> OurCitysRecords
|
||
<strong>SET</strong> RecordMaxBarometricPressure =
|
||
new_row.TodaysMaxBarometricPressure
|
||
|
||
<strong>CREATE TRIGGER</strong> UpdateMinPressureTrigger
|
||
<strong>AFTER UPDATE OF</strong> TodaysMinBarometricPressure
|
||
<strong>ON</strong> TodaysRecords
|
||
<strong>REFERENCING NEW AS</strong> new_row
|
||
<strong>FOR EACH ROW MODE DB2ROW</strong>
|
||
<strong>WHEN</strong>(new_row.TodaysMinBarometricPressure<
|
||
(<strong>SELECT</strong> <strong>MIN</strong>(RecordMinBarometricPressure) <strong>FROM</strong>
|
||
OurCitysRecords))
|
||
<strong>UPDATE</strong> OurCitysRecords
|
||
<strong>SET</strong> RecordMinBarometricPressure =
|
||
new_row.TodaysMinBarometricPressure</pre>
|
||
<p>First
|
||
the current values are initialized for the tables.</p>
|
||
<pre><strong>INSERT INTO</strong> TodaysRecords <strong>VALUES</strong>(0.0,0.0)
|
||
<strong>INSERT INTO</strong> OurCitysRecords <strong>VALUES</strong>(0.0,0.0)</pre>
|
||
<p>For
|
||
the SQL update statement below, the RecordMaxBarometricPressure in OurCitysRecords
|
||
is updated by the UpdateMaxPressureTrigger.</p>
|
||
<pre><strong>UPDATE</strong> TodaysRecords <strong>SET</strong> TodaysMaxBarometricPressure = 29.95</pre>
|
||
<p>But tomorrow, if the TodaysMaxBarometricPressure is only 29.91, then the
|
||
RecordMaxBarometricPressure is not updated.</p>
|
||
<pre><strong>UPDATE</strong> TodaysRecords <strong>SET</strong> TodaysMaxBarometricPressure = 29.91</pre>
|
||
<p>SQL allows the definition of multiple triggers for a single triggering action.
|
||
In the previous example, there are two AFTER UPDATE triggers: UpdateMaxPressureTrigger
|
||
and UpdateMinPressureTrigger. These triggers are only activated when specific
|
||
columns of the table TodaysRecords are updated.</p>
|
||
</div>
|
||
<div class="section"><p>AFTER triggers may modify tables. In the example above, an UPDATE
|
||
operation is applied to a second table. Note that recursive insert and update
|
||
operations should be avoided. The database management system terminates the
|
||
operation if the maximum trigger nesting level is reached. You can avoid
|
||
recursion by adding conditional logic so that the insert or update operation
|
||
is exited before the maximum nesting level is reached. The same situation
|
||
needs to be avoided in a network of triggers that recursively cascade through
|
||
the network of triggers.</p>
|
||
</div>
|
||
</div>
|
||
<div>
|
||
<div class="familylinks">
|
||
<div class="parentlink"><strong>Parent topic:</strong> <a href="rbafysqltrig.htm" title="The SQL CREATE TRIGGER statement provides a way for the database management system to actively control, monitor, and manage a group of tables and views whenever an insert, update, or delete operation is performed.">SQL triggers</a></div>
|
||
</div>
|
||
</div>
|
||
</body>
|
||
</html> |