chap13_5.html

Oracle资料大集合

</tr>
<tr><td colspan="2"><img src="../../../images/dotclear.gif" width=6 height=12 alt="" border="0"></td></tr>
<tr>
<td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td>
<td><a href="http://www.itlibrary.com/reference/dir.operatingsystems.html"><font face="verdana,helvetica" size="1" color="000000"><b>Operating Systems</b></font></td>
</tr>
<tr><td colspan="2"><img src="../../../images/dotclear.gif" width=6 height=12 alt="" border="0"></td></tr>
<tr>
<td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td>
<td><a href="http://www.itlibrary.com/reference/dir.userinterfaces.html"><font face="verdana,helvetica" size="1" color="000000"><b>User Interfaces</b></font></td>
</tr>
<tr><td colspan="2"><img src="../../../images/dotclear.gif" width=6 height=12 alt="" border="0"></td></tr>
<tr>
<td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td>
<td><a href="http://www.itlibrary.com/reference/dir.groupwareandcollaboration1.html"><font face="verdana,helvetica" size="1" color="000000"><b>Groupware &amp; Collaboration</b></font></td>
</tr>
<tr><td colspan="2"><img src="../../../images/dotclear.gif" width=6 height=12 alt="" border="0"></td></tr>
<tr>
<td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td>
<td><a href="http://www.itlibrary.com/reference/dir.contentmanagement.html"><font face="verdana,helvetica" size="1" color="000000"><b>Content Management</b></font></td>
</tr>
<tr><td colspan="2"><img src="../../../images/dotclear.gif" width=6 height=12 alt="" border="0"></td></tr>
<tr>
<td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td>
<td><a href="http://www.itlibrary.com/reference/dir.productivityapplications1.html"><font face="verdana,helvetica" size="1" color="000000"><b>Productivity Applications</b></font></td>
</tr>
<tr><td colspan="2"><img src="../../../images/dotclear.gif" width=6 height=12 alt="" border="0"></td></tr>
<tr>
<td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td>
<td><a href="http://www.itlibrary.com/reference/dir.hardware1.html"><font face="verdana,helvetica" size="1" color="000000"><b>Hardware</b></font></td>
</tr>
<tr><td colspan="2"><img src="../../../images/dotclear.gif" width=6 height=12 alt="" border="0"></td></tr>
<tr>
<td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td>
<td><a href="http://www.itlibrary.com/reference/dir.funandgames1.html"><font face="verdana,helvetica" size="1" color="000000"><b>Fun &amp; Games</b></font></td>
</tr>
</table>


<!-- <IMG SRC="/images/leftnav.gif" WIDTH=111 HEIGHT=404 ALT="nav" border="0"  ISMAP USEMAP="#leftnav"> -->
<p>
<a href="http://www.earthweb.com/jobs/" target=new><IMG SRC="../../../images/dice-105x30.gif" WIDTH=105 HEIGHT=30 ALT="EarthWeb Direct" border="0"></a>
<a href="http://www.earthwebdirect.com/" target=new><IMG SRC="../../../images/earthwebdirect.gif" WIDTH=105 HEIGHT=30 ALT="EarthWeb Direct" border="0"></a>
<a href="http://www.fatbrain.com/home.html?from=UUX592" target=new><IMG SRC="../../../images/fatbrain.gif" WIDTH=105 HEIGHT=30 ALT="Fatbrain" border="0"></a>
<a href="http://auctions.earthweb.com" target=new><IMG SRC="../../../images/auctions_105x30.gif" WIDTH=105 HEIGHT=30 ALT="Auctions" border="0"></a>
<a href="http://www.supportsource.com" target=new><IMG SRC="../../../images/ss2000sneak2.gif" WIDTH=105 HEIGHT=30 ALT="Support Source Answers" border="0"></a>
<P>

<b><font face="verdana,helvetica" size="2">EarthWeb sites</FONT></b><br>
  <FONT SIZE="1">
  <a href="http://www.crossnodes.com"><font face="verdana,helvetica" size="1" color="000000">Crossnodes</font></a><br>
  <a href="http://www.datamation.com"><font face="verdana,helvetica" size="1" color="000000">Datamation</font></a><br>
  <a href="http://www.developer.com"><font face="verdana,helvetica" size="1" color="000000">Developer.com</FONT></A><br>
  <a href="http://www.dice.com"><font face="verdana,helvetica" size="1" color="000000">DICE</font></a><br>
  <a href="http://www.earthweb.com"><font face="verdana,helvetica" size="1" color="000000">EarthWeb.com</font></a><br>
  <a href="http://www.earthwebdirect.com"><font face="verdana,helvetica" size="1" color="000000">EarthWeb Direct</font></a><br>
  <a href="http://www.erphub.com"><font face="verdana,helvetica" size="1" color="000000">ERP Hub</font></a><br>
  <a href="http://www.gamelan.com"><font face="verdana,helvetica" size="1" color="000000">Gamelan</font></a><br>
  <a href="http://www.gocertify.com"><font face="verdana,helvetica" size="1" color="000000">GoCertify.com</font></a><br>
  <a href="http://www.htmlgoodies.com"><font face="verdana,helvetica" size="1" color="000000">HTMLGoodies</font></a><br>
  <a href="http://www.intranetjournal.com"><font face="verdana,helvetica" size="1" color="000000">Intranet Journal</font></a><br>
  <a href="http://www.itknowledge.com"><font face="verdana,helvetica" size="1" color="000000">IT Knowledge</font></a><br>
  <a href="http://www.itlibrary.com"><font face="verdana,helvetica" size="1" color="000000">IT Library</font></a><br>
  <a href="http://www.javagoodies.com"><font face="verdana,helvetica" size="1" color="000000">JavaGoodies</font></a><br>
  <a href="http://www.jars.com"><font face="verdana,helvetica" size="1" color="000000">JARS</font></a><br>
  <a href="http://www.javascripts.com"><font face="verdana,helvetica" size="1" color="000000">JavaScripts.com</font></a><br>
  <a href="http://www.opensourceit.com"><font face="verdana,helvetica" size="1" color="000000">open source IT</font></a><br>
  <a href="http://www.roadcoders.com"><font face="verdana,helvetica" size="1" color="000000">RoadCoders</font></a><br>
  <a href="http://www.Y2Kinfo.com"><font face="verdana,helvetica" size="1" color="000000">Y2K Info</font></a>
</FONT>

</td>



<!--End Left Navigation column -->
<!-- spacer column  -->

<TD width="40">
<img src="../../../images/dotclear.gif" WIDTH="40" HEIGHT="1" border=0>
</TD>

<!--Begin Content Column -->

<TD VALIGN="TOP" width="500">

<P>




<blockquote>
<p>
<b><font size="+1">13.5 How do I匘isable and enable triggers?</font></b>
<p>
<B>Problem</B>
<p>
When I perform batch processing and system management tasks on our database, I need to disable triggers from firing. Many of our triggers modify data in one or more tables. If I do not disable database triggers before performing these functions, they can fail. How do I disable and enable database triggers?
<p>
<B>Technique</B>
<p>
The ALTER TRIGGER statement is used to disable or enable a specific trigger. The DISABLE clause is used to disable a trigger, and the ENABLE clause is used to enable it. All triggers for a given table can be disabled or enabled together using the ALTER TABLE statement with the DISABLE ALL TRIGGERS or ENABLE ALL TRIGGERS clause.
<p>
<B>Steps</B>
<p>
<B>1.</B>	Run SQL*Plus and connect as the WAITE user account. CHP13_20.SQL, shown in <A HREF="image/13oht20.gif" TARGET="_blank">Figure 13.20</A>, creates a sample table and triggers used in this How-To. Run the statement to create the objects.
<p>
The script first drops the DEPT13 table along with the triggers created in the previous How-To抯. It then re-creates the DEPT13 table with two triggers that will be disabled and enabled in the examples. The BEFORE INSERT trigger桪EPT_BI_13梐nd an AFTER INSERT trigger桪EPT_AI_13梐re created.
<code><b><p>
SQL>  START CHP13-20
<p>
Table created.
<p>
Trigger created.
<p>
Trigger created.
<p></b></code>
<B>2.</B> Run the CHP13_21.SQL in SQL*Plus. The file contains an ALTER TRIGGER statement to disable a specific trigger. <A HREF="image/13oht21.gif" TARGET="_blank">Figure 13.21</A> displays the execution of the DDL statement.
<p>
Line 1 presents the ALTER TRIGGER statement. The ALTER TRIGGER statement can be used to disable a specific trigger. Line 2 provides the name of the trigger to be disabled and line 3 specifies the DISABLE clause to disable the trigger.
<p>
When a record is inserted into the table, the BEFORE INSERT trigger will not fire because it was disabled by the ALTER TRIGGER statement. When you are done performing your management tasks, the trigger should be enabled. The next step provides the technique to enable a specific trigger.
<p>
<B>3.</B>	Run the CHP13_22.SQL file in SQL*Plus. The file contains an ALTER TRIGGER statement to re-enable the trigger disabled in the previous step. <A HREF="image/13oht22.gif" TARGET="_blank">Figure 13.22</A> displays the results of the operation.
<p>
The format of the statement is the same as presented in Step 2. The only difference is that line 3 specifies the ENABLE clause, which causes the trigger to be enabled. Once the trigger is enabled, it will fire whenever the triggering event occurs.
<p>
<B>4.</B>	It is often necessary to disable all of the triggers on a given table. Run the CHP13_23.SQL file in SQL*Plus. The file contains an ALTER TABLE statement, which disables all of the triggers on the sample table. <A HREF="image/13oht23.gif" TARGET="_blank">Figure 13.23</A> shows the output in SQL*Plus.
<p>
Line 1 specifies the ALTER TABLE statement and Line 2 specifies the table to be altered. Line 3 presents the DISABLE ALL TRIGGERS clause, disabling all triggers on the specified table. Using this technique does not require that you know the names of the triggers on the table.
<p>
Once you have completed your management tasks with the table, the triggers should be enabled. The next step presents the statement used to enable all the triggers on the table.
<p>
<B>5.</B>	All the triggers on a table can be enabled using the ALTER TABLE statement. Run the CHP13_24.SQL file in SQL*Plus. This file contains the statement to re-enable all the triggers disabled in the previous step. <A HREF="image/13oht24.gif" TARGET="_blank">Figure 13.24</A> shows the output after running the statement.
<p>
The first two lines of the ALTER TABLE statement are identical to the statement provided in Step 6. Line 3 of the statement presents the EN-ABLE ALL TRIGGERS clause, enabling all triggers on the specified table.
<p>
<B>How It Works</B>
<p>
The ALTER TABLE and ALTER TRIGGER statements disable and enable triggers. The ALTER TRIGGER statement is used to disable or enable a specific trigger by name. The ALTER TABLE statement with the ENABLE ALL TRIGGERS or DISABLE ALL TRIGGERS clause is used to enable or disable all triggers on the specified table. Step 1 creates a sample table and the two triggers used throughout this How-To. Step 2 presents the ALTER TRIGGER statement using the DISABLE clause to disable one of the sample triggers. Step 3 presents the ENABLE clause, to enable the sample trigger. Step 4 uses the ALTER TABLE statement to disable all the triggers on the sample table. Step 5 uses the ALTER TABLE statement with the ENABLE ALL TRIGGERS clause, enabling all the triggers on the sample table.
<p>
<B>Comments</B>
<p>
Database triggers ensure that a specific operation is performed when a table is modified. There will be times when you need to modify the data in a table but do not want the triggers to fire. The ALTER TABLE statement is the easiest way to disable and enable all the triggers on a table. It does not require that you remember the names of the triggers on a table. 

<!-- </td>

</td>
</tr>
</table> -->
</td>

</td>
</tr>
</table>

<!-- begin footer information -->

<MAP NAME="footer">
<AREA SHAPE=RECT COORDS="0,0,62,26" HREF="http://www.itlibrary.com/">
<AREA SHAPE=RECT COORDS="62,0,135,26" HREF="http://www.developer.com/about/">
<AREA SHAPE=RECT COORDS="135,0,199,26" HREF="http://www.itlibrary.com/search/">
<AREA SHAPE=RECT COORDS="200,0,274,26" HREF="http://www.itlibrary.com/subscribe/">
<AREA SHAPE=RECT COORDS="275,0,335,25" HREF="http://www.developer.com/contact/adinfo.html">
<AREA SHAPE=RECT COORDS="335,0,417,25" HREF="http://www.developer.com/contact/">
<AREA SHAPE=RECT COORDS="418,0,467,26" HREF="http://www.developer.com/about/faq.html">
</MAP>

<P>
<table width="640" cellpadding="0" cellspacing="0" border="0">
<tr>
<TD WIDTH="130"></TD>
<td width="468">
<IMG SRC="../../../images/footer/footerfile.gif" ALT="footer nav" width="467" height="26" BORDER="0" usemap="#footer" ismap>
</td>
</tr>
<tr>
<TD WIDTH="130"></TD>
<td width="468"> 
<font face=arial,helvetica size="1"> Use of this site is subject certain <a href="http://www.developer.com/legal/">Terms &amp; Conditions.</a><br>
Copyright (c) 1996-1999 <A HREF="http://www.earthweb.com/">EarthWeb, Inc.</A>.  All rights reserved.  Reproduction in whole or in part in any form or medium without express written permission of EarthWeb is prohibited.
<a href="http://www.earthweb.com/corporate/privacy.html">Please read our privacy policy for details.</a>
</td>
</tr>
</table>

</BODY>
</HTML>