76 lines
5.1 KiB
HTML
76 lines
5.1 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="SQL trigger transition tables" />
|
||
<meta name="abstract" content="An SQL trigger may need to refer to all of the affected rows for an SQL insert, update, or delete operation. This is true, for example, if the trigger needs to apply aggregate functions, such as MIN or MAX, to a specific column of the affected rows. The OLD_TABLE and NEW_TABLE transition tables can be used for this purpose." />
|
||
<meta name="description" content="An SQL trigger may need to refer to all of the affected rows for an SQL insert, update, or delete operation. This is true, for example, if the trigger needs to apply aggregate functions, such as MIN or MAX, to a specific column of the affected rows. The OLD_TABLE and NEW_TABLE transition tables can be used for this purpose." />
|
||
<meta name="DC.subject" content="trigger, transition tables, CREATE TRIGGER statement" />
|
||
<meta name="keywords" content="trigger, transition tables, CREATE TRIGGER statement" />
|
||
<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="rbafytransitiontable" />
|
||
<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>SQL trigger transition tables</title>
|
||
</head>
|
||
<body id="rbafytransitiontable"><a name="rbafytransitiontable"><!-- --></a>
|
||
<!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
|
||
<h1 class="topictitle1">SQL trigger transition tables</h1>
|
||
<div><p>An SQL trigger may need to refer to all of the affected rows for
|
||
an SQL insert, update, or delete operation. This is true, for example, if
|
||
the trigger needs to apply aggregate functions, such as MIN or MAX, to a specific
|
||
column of the affected rows. The OLD_TABLE and NEW_TABLE transition tables
|
||
can be used for this purpose.</p>
|
||
<div class="section"><p>In the following example, the trigger applies the aggregate function
|
||
MAX to all of the affected rows of the table StudentProfiles.</p>
|
||
<pre><strong>CREATE TABLE</strong> StudentProfiles(StudentsName <strong>VARCHAR</strong>(125),
|
||
StudentsYearInSchool <strong>SMALLINT</strong>, StudentsGPA <strong>DECIMAL</strong>(5,2))
|
||
|
||
<strong>CREATE TABLE</strong> CollegeBoundStudentsProfile
|
||
(YearInSchoolMin <strong>SMALLINT</strong>, YearInSchoolMax <strong>SMALLINT</strong>, StudentGPAMin
|
||
<strong>DECIMAL</strong>(5,2), StudentGPAMax <strong>DECIMAL</strong>(5,2))
|
||
|
||
<strong>CREATE TRIGGER</strong> UpdateCollegeBoundStudentsProfileTrigger
|
||
<strong>AFTER UPDATE ON</strong> StudentProfiles
|
||
<strong>REFERENCING NEW_TABLE AS</strong> ntable
|
||
<strong>FOR EACH STATEMENT MODE DB2SQL</strong>
|
||
<strong>BEGIN</strong>
|
||
<strong>DECLARE</strong> maxStudentYearInSchool <strong>SMALLINT</strong>;
|
||
<strong>SET</strong> maxStudentYearInSchool =
|
||
(<strong>SELECT MAX</strong>(StudentsYearInSchool) <strong>FROM</strong> ntable);
|
||
<strong>IF</strong> maxStudentYearInSchool >
|
||
(<strong>SELECT MAX</strong> (YearInSchoolMax)<strong> FROM</strong>
|
||
CollegeBoundStudentsProfile)<strong> THEN</strong>
|
||
<strong>UPDATE</strong> CollegeBoundStudentsProfile <strong>SET</strong> YearInSchoolMax =
|
||
maxStudentYearInSchool;
|
||
<strong>END IF</strong>;
|
||
<strong>END</strong></pre>
|
||
<p>In the preceding example, the trigger is processed
|
||
a single time following the processing of a triggering update
|
||
statement because it is defined as a FOR EACH STATEMENT trigger. You will
|
||
need to consider the processing overhead required by the database management
|
||
system for populating the transition tables when you define a trigger that
|
||
references transition tables.</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> |