chap13_7.html

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

<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.7 How do I匯ebuild CREATE TRIGGER statements?
</font></b>
<p>
<B>Problem</B>

<p>

I need to re-create the statements used to create some of the triggers in the database. I either lost the original files or entered the trigger directly into SQL*Plus without saving the CREATE TRIGGER statements. I know that throughout the book we have used the data dictionary and SQL*Plus to rebuild statements. How do I rebuild CREATE TRIGGER statements?

<p>

<B>Technique</B>

<p>

There are data dictionary views containing information about triggers, which are USER_TRIGGERS, ALL_TRIGGERS, and DBA_TRIGGERS. When you rebuild the CREATE TRIGGER statement for a trigger, you can connect to the database as the owner of the trigger and use the USER_TRIGGERS or ALL_TRIGGERS data dictionary view. The DBA_TRIGGERS view contains all views in the database and requires privileges in the DBA role. The description of the USER_TRIGGERS view is shown in <A HREF="image/13oht14.gif" TARGET="_blank">Figure 13.14</A>. The TRIGGER_BODY column contains the text of the trigger. The datatype of the column is LONG, so it will be slightly harder to work with. The TRIGGER_NAME, TRIGGER_TYPE, and TRIGGERING_EVENT columns will be concatenated with the trigger body in order to rebuild the statement.
<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 How-To 13.5. The DEPT13 table is created along with the two triggers, DEPT_BI_13 and DEPT_AI_13. The USER_TRIGGERS provides valuable information about triggers as examined in the previous How-To. In this How-To, the same view is used to rebuild the code that originally created the trigger.

<p>

<B>2.</B>	Run the CHP13_26.SQL in SQL*Plus, as shown in <A HREF="image/13oht26.gif" TARGET="_blank">Figure 13.26</A>. The file contains a query of the USER_TRIGGERS data dictionary view displaying the header and body of the trigger.

<p>

The DESCRIPTION column in line 2 returns the trigger header. The TRIGGER_BODY column in line 3 returns the trigger body. Line 5 specifies the USER_TRIGGERS view as the source of the data and line 7 specifies the name of the trigger.

<p>

Because the TRIGGER_BODY column is of the LONG datatype, you may increase the value of the LONG SQL*Plus variable to 2000, if you have SQL*Plus complaints about the data being too long to display. Use the SET LONG 2000 command at the prompt and try running the query again. If a lot of columns are returned by a query, the ARRAYSIZE system variable can be decreased before running the query. For example, use the SET ARRAYSIZE 1 command at the prompt to reduce the value of SQL*Plus ARRAYSIZE variable.

<p>

LINESIZE is another SQL*Plus variable that you may occasionally need to adjust to fit the output of a column to a single line. For example, use the SET LINESIZE 132 command to set the SQL*Plus LINESIZE variable to 132.
<p>
With the DESCRIPTION and TRIGGER_BODY columns, the result of the query almost looks like a CREATE TRIGGER statement, except for the CREATE OR REPLACE TRIGGER keywords. The next step presents the query to actually rebuild the trigger code.
<p>
<B>3.</B>	Load CHP13_27.SQL into the SQL buffer. The file contains a query that returns the actual trigger code to re-create the trigger. <A HREF="image/13oht27.gif" TARGET="_blank">Figure 13.27</A> displays the results of the operation.
<p>
The HEADING SQL*Plus variable is set to OFF to suppress the column headings. Line 1 prefixes the CREATE OR REPLACE TRIGGER keywords and concatenates the trigger header to it. Line 3 retrieves the trigger body. When this query is executed, it returns the exact code that originally created the trigger. You can spool the output to a file using the SPOOL command and then run the spooled file to re-create the trigger.
<p>
With this query, you can only re-create triggers that belong to the current user account. A query to ALL_TRIGGERS or DBA_TRIGGERS enables you to create re-create all triggers in the system, provided you have DBA permissions.
<p>

<B>How It Works</B>

<p>

Throughout the book, we use dictionary views to create DDL statements. In this case, a CREATE TRIGGER statement is rebuilt. The ALL_TRIGGERS data dictionary view contains information about the triggers in the database.

<p>

Step 1 creates the sample table and trigger used throughout this How-To. Step 2 displays the DESCRIPTION and TRIGGER_BODY columns, returning the header and code section of the trigger. Step 2 also creates the trigger specification by prefixing the CREATE OR REPLACE TRIGGER keywords to the DESCRIPTION column value. The process is completed by concatenating the TRIGGER_BODY column value. The resulting output is the trigger code that originally created the trigger.

<p>

<B>Comments</B>

<p>

The process presented in this How-To can be used to rebuild CREATE TRIGGER statements when the original source code is no longer available. Rebuilding CREATE TRIGGER statements can be a necessary database management task. If you need to create all the triggers on a table or in the database, a simple modification to the WHERE clause of the query is all you need.

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