chap13_6.html

来自「Oracle资料大集合」· HTML 代码 · 共 401 行 · 第 1/2 页

<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.6 How do I匧ist information about triggers?</font></b>
<p>
<B>Problem</B>
<p>
I want to list information about the triggers in the database. I need to determine the trigger name, the table the trigger is on, and the status of the trigger. How do I list information about triggers?
<p>
<B>Technique</B>
<p>
Information about triggers is contained in the USER_TRIGGERS, ALL_TRIGGERS, or DBA_TRIGGERS data dictionary views. The columns of the USER_TRIGGERS view are described in Table 13.2. The TABLE_OWNER and TABLE_NAME columns identify the table containing the triggering event. The TRIGGER_NAME column specified returns the name of the trigger. The TRIGGER_TYPE column returns the type of trigger, such as BEFORE EACH ROW or BEFORE STATEMENT. The TRIGGERING_EVENT column returns the event, such as INSERT, UPDATE, or DELETE, that causes the trigger to file. The STATUS column tells whether the trigger is currently enabled or disabled.
<p>
Table 13.2 Columns of the USER_TRIGGERS view
<p>
<TABLE cellpadding="3" cellspacing="3" border="0" bgcolor="cdcdcd">
<TR><td>Column</td>
<td>Description</td></TR>

<TR><td>TRIGGER_NAME</td>
<td>Name of the trigger.</td></TR>

<TR><td>TRIGGER_TYPE</td>
<td>The type of trigger, such as BEFORE ROW, AFTER ROW, BEFORE STATEMENT, AFTER STATEMENT.</td></TR>
<TR>
<td>TRIGGERING_EVENT</td>
<td>Statement that fires the trigger: INSERT, UPDATE, DELETE.</td></TR>

<TR><td>TABLE_OWNER</td>
<td>Owner of the table on which the trigger is defined.</td></TR>

<TR><td>TABLE_NAME</td>
<td>Table on which the trigger is defined.</td></TR>

<TR><td>REFERENCING_NAMES</td>
<td>Names used to reference OLD and NEW.</td></TR>

<TR><td>WHEN_CLAUSE</td>
<td>WHEN clause for the trigger body to be executed on a condition.</td></TR>

<TR><td>STATUS</td>
<td>Trigger status: ENABLED or DISABLED.</td></TR>

<TR><td>DESCRIPTION</td>
<td>Trigger header.</td></TR>

<TR><td>TRIGGER_BODY</td>
<td>Trigger body.</td></TR>
</TABLE>
<p>
<B>Steps</B>
<p>
<B>1.</B>	Run SQL*Plus and connect as the WAITE user account. If you do not have the DEPT13 table and the triggers created on it in your schema, then run CHP13_20.SQL from Step 1 of the previous How-To. The DEPT13 table is created along with the two triggers, DEPT_BI_13 and DEPT_AI_13. The next step demonstrates how a query to the USER_TRIGGERS data dictionary view can provide you with valuable information about triggers.
<p>
<B>2.</B>	Run the CHP13_25.SQL file in SQL*Plus. The query contained in the file queries the USER_TRIGGERS table to display information about the trig-gers on the specified table. <A HREF="image/13oht25.gif" TARGET="_blank">Figure 13.25</A> displays the results of the query for the DEPT13 table.
<p>
The COLUMN commands format the columns before running the statement. After executing the statement, provide DEPT13_31 as the substitution variable. The query displays the two triggers created in Step 1. The columns returned by the query show you the name of the trigger, when it fires, and its status. Including the TRIGGER_BODY column would also show you the code executed when the trigger fires.
<p>
The TRIGGER_NAME column specified in line 2 returns the name of the trigger. The TRIGGERING_EVENT column specified in line 3 returns the type of event that causes the trigger to fire, such as INSERT, UPDATE, or DELETE. The TRIGGER_TYPE column specified in line 4 returns the type of trigger, such as BEFORE EACH ROW or BEFORE STATEMENT. Line 5 returns the STATUS column showing whether the trigger is ENABLED or DISABLED. Including the REFERENCING_NAMES column in the query will return the REFERENCING clause if it was specified while creating the trigger. The WHEN clause for the trigger, if one exists, can be queried from the WHEN_CLAUSE column of the USER_TRIGGERS view.
<p>
<B>How It Works</B>
<p>
The USER_TRIGGERS, ALL_TRIGGERS, and DBA_TRIGGERS data dictionary views contain information about the database triggers in the database. The TABLE_NAME column can be used in the WHERE clause of a query to either of the views for returning information about the views contained on a specific table. Step 1 creates the sample table and two sample triggers used to display the technique. Step 2 executes a query on the USER_ TRIGGERS data dictionary view to show information about the sample triggers.
<p>

<B>Comments</B>

<p>

A simple query to the USER_TRIGGERS data dictionary view will provide you with valuable information about the triggers on a table. If the triggers are not behaving the way you expect, query the data dictionary to verify that they are enabled, they fire when you want, and the code is valid for the operation.

<p>

Similar to stored procedures, triggers have dependencies. They can be invalidated if a referenced object has changed and recompiled when the trigger is invoked again. Refer to How-To 10.6, which explains how you can examine the USER_DEPENDENCIES view to detect trigger dependencies. Trigger compilation errors can be queried from the USER_ERRORS view, or by issuing the SHOW ERRORS command at the SQL*Plus prompt, which will display errors for the last trigger that was compiled in the session.

<p>


<!-- </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>