84 lines
5.6 KiB
HTML
84 lines
5.6 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="Handlers in SQL triggers" />
|
|||
|
|
<meta name="abstract" content="A handler in an SQL trigger gives the SQL trigger the ability to recover from an error or log information about an error that has occurred while processing the SQL statements in the trigger routine body." />
|
|||
|
|
<meta name="description" content="A handler in an SQL trigger gives the SQL trigger the ability to recover from an error or log information about an error that has occurred while processing the SQL statements in the trigger routine body." />
|
|||
|
|
<meta name="DC.subject" content="trigger, handlers, CREATE TRIGGER statement" />
|
|||
|
|
<meta name="keywords" content="trigger, handlers, 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="rbafyhandlersql" />
|
|||
|
|
<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>Handlers in SQL triggers</title>
|
|||
|
|
</head>
|
|||
|
|
<body id="rbafyhandlersql"><a name="rbafyhandlersql"><!-- --></a>
|
|||
|
|
<!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
|
|||
|
|
<h1 class="topictitle1">Handlers in SQL triggers</h1>
|
|||
|
|
<div><p>A handler in an SQL trigger gives the SQL trigger the ability to
|
|||
|
|
recover from an error or log information about an error that has occurred
|
|||
|
|
while processing the SQL statements in the trigger routine body. </p>
|
|||
|
|
<div class="section"><p>In the following example, there are two handlers defined: one
|
|||
|
|
to handle the overflow condition and a second handler to handle SQL exceptions.</p>
|
|||
|
|
<pre><strong>CREATE TABLE</strong> ExcessInventory(Description <strong>VARCHAR</strong>(50), ItemWeight <strong>SMALLINT</strong>)
|
|||
|
|
|
|||
|
|
<strong>CREATE TABLE</strong> YearToDateTotals(TotalWeight <strong>SMALLINT</strong>)
|
|||
|
|
|
|||
|
|
<strong>CREATE TABLE</strong> FailureLog(Item <strong>VARCHAR</strong>(50), ErrorMessage <strong>VARCHAR</strong>(50), ErrorCode <strong>INT</strong>)
|
|||
|
|
|
|||
|
|
<strong>CREATE TRIGGER</strong> InventoryDeleteTrigger
|
|||
|
|
<strong>AFTER DELETE ON </strong>ExcessInventory
|
|||
|
|
<strong>REFERENCING OLD AS</strong> old_row
|
|||
|
|
<strong>FOR EACH ROW MODE DB2ROW</strong>
|
|||
|
|
<strong>BEGIN</strong>
|
|||
|
|
<strong>DECLARE</strong> sqlcode <strong>INT</strong>;
|
|||
|
|
<strong>DECLARE</strong> invalid_number condition <strong>FOR</strong> '22003';
|
|||
|
|
<strong>DECLARE</strong> exit handler <strong>FOR</strong> invalid_number
|
|||
|
|
<strong>INSERT INTO</strong> FailureLog <strong>VALUES</strong>(old_row.Description,
|
|||
|
|
'Overflow occurred in YearToDateTotals', sqlcode);
|
|||
|
|
<strong>DECLARE</strong> exit handler <strong>FOR</strong> sqlexception
|
|||
|
|
<strong>INSERT INTO</strong> FailureLog <strong>VALUES</strong>(old_row.Description,
|
|||
|
|
'SQL Error occurred in InventoryDeleteTrigger', sqlcode);
|
|||
|
|
<strong>UPDATE</strong> YearToDateTotals <strong>SET</strong> TotalWeight=TotalWeight +
|
|||
|
|
old_row.itemWeight;
|
|||
|
|
<strong>END</strong></pre>
|
|||
|
|
<p> First, the current values for the tables are initialized.</p>
|
|||
|
|
<pre><strong>INSERT INTO</strong> ExcessInventory <strong>VALUES</strong>('Desks',32500)
|
|||
|
|
<strong>INSERT INTO</strong> ExcessInventory<strong> VALUES</strong>('Chairs',500)
|
|||
|
|
<strong>INSERT INTO</strong> YearToDateTotals <strong>VALUES</strong>(0)</pre>
|
|||
|
|
<p>When the
|
|||
|
|
first SQL delete statement below is executed, the ItemWeight for the item
|
|||
|
|
"Desks" is added to the column total for TotalWeight in the table YearToDateTotals.
|
|||
|
|
When the second SQL delete statement is executed, an overflow occurs when
|
|||
|
|
the ItemWeight for the item "Chairs" is added to the column total for TotalWeight,
|
|||
|
|
as the column only handles values up to 32767. When the overflow occurs,
|
|||
|
|
the invalid_number exit handler is executed and a row is written to the FailureLog
|
|||
|
|
table. The sqlexception exit handler runs, for example, if the YearToDateTotals
|
|||
|
|
table was deleted by accident. In this example, the handlers are used to
|
|||
|
|
write a log so that the problem can be diagnosed at a later time.</p>
|
|||
|
|
<pre><strong>DELETE FROM</strong> ExcessInventory <strong>WHERE</strong> Description='Desks'
|
|||
|
|
<strong>DELETE FROM</strong> ExcessInventory <strong>WHERE</strong> Description='Chairs'</pre>
|
|||
|
|
</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>
|