ora_dict_obj_type is a system defined event attribute for DDL triggers, escentially Oracle-defined variables. In this case its the object type for the object that caused the trigger to fire off.
I need this to be a DDL trigger, not a DML trigger, as I want to capture the table and its contents before its dropped. When created, it could be done via CTAS or just CREATE, followed by various DML statements. To capture its final contents I'd be easiest for me to copy it right before it gets dropped. I guess I could generate a materialized view on it when its created, so that all content would be automatically applied to the copy, but I thought this would be easier.
From: oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)] On Behalf Of David Wendelken Sent: Monday, August 22, 2005 4:40 PM To: 'Oracle-L@(protected) Org (E-mail)' Subject: RE: Debug DDL trigger
I'm guessing ora_dict_obj_type is a column in dherri.schema?
If so, you are using row-level data in a statement-level trigger.
You need to add the magic words that turn the trigger into a row level trigger.
It's "for each row" or words to that effect. You'll find the exact syntax in the manual.
Here's the trigger's text:
CREATE OR REPLACE TRIGGER sys.cp_tab_before_drop_btg
BEFORE drop ON dherri.SCHEMA
WHEN ( ora_dict_obj_type = 'TABLE'
AND ( ora_dict_obj_name LIKE 'SCORE\_ACCT\_TB\_%' ESCAPE '\'
)
)
************************************************************************** The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged.
If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited.
If you have received this communication in error, please resend this communication to the sender and delete the original message or any copy of it from your computer system.
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size: 10.0pt;font-family:Arial;color:navy'>ora_dict_obj_type is a system defined event attribute for DDL triggers, escentially Oracle-defined variables. In this case its the object type for the object that caused the trigger to fire off.<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size: 10.0pt;font-family:Arial;color:navy'>I need this to be a DDL trigger, not a DML trigger, as I want to capture the table and its contents before its dropped. When created, it could be done via CTAS or just CREATE, followed by various DML statements. To capture its final contents I'd be easiest for me to copy it right before it gets dropped. I guess I could generate a materialized view on it when its created, so that all content would be automatically applied to the copy, but I thought this would be easier.<i><span style='font-style:italic'><o :p></o:p></span></i></span></font></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size: 12.0pt'><o:p> </o:p></span></font></p>
<div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size: 12.0pt'> <o:p></o:p></span></font></p>
</div>
<div>
<p class=MsoNormal><font size=2 color=blue face=Arial><span style='font-size: 10.0pt;font-family:Arial;color:blue'>I'm guessing </span></font><font size=2 color=black face=Arial><span style='font-size:10.0pt;font-family:Arial; color:black'>ora_dict_obj_type is a column in dherri.schema?</span></font><o:p> </o:p></p>
</div>
<div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size: 12.0pt'> <o:p></o:p></span></font></p>
</div>
<div>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt; font-family:Arial'>If so, you are using row-level data in a statement-level trigger.</span></font><o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><font size=2 color=blue face=Arial><span style='font-size: 10.0pt;font-family:Arial;color:blue'>You need to add the magic words that turn the trigger into a row level trigger.</span></font><o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><font size=2 color=blue face=Arial><span style='font-size: 10.0pt;font-family:Arial;color:blue'>It's "for each row" or words to that effect. You'll find the exact syntax in the manual.</span> </font><o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size: 12.0pt'> <o:p></o:p></span></font></p>
</div>
<div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size: 12.0pt'> <o:p></o:p></span></font></p>
</div>
<div>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt; font-family:Arial'>Here's the trigger's text:<o:p></o:p></span></font></p>
</div>
<blockquote style='border:none;border-left:solid blue 1.0pt;padding:0in 0in 0in 2.0pt; margin-left:2.2pt;margin-top:5.0pt;margin-right:0in;margin-bottom:5.0pt'>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt; font-family:Arial'> BEFORE drop ON dherri.SCHEMA<o:p></o:p></span>< /font></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size: 12.0pt'> <o:p></o:p></span></font></p>
</blockquote>
</div>
</div>
<pre>************************************************************************** The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged.
If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited.
If you have received this communication in error, please resend this communication to the sender and delete the original message or any copy of it from your computer system.
