| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Debug DDL trigger
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.
Dave
Dave Herring, DBA
Acxiom Corporation
3333 Finley
Downers Grove, IL 60515
wk: 630.944.4762
<mailto:dherri_at_acxiom.com <mailto:dherri_at_acxiom.com> >
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of David Wendelken
Sent: Monday, August 22, 2005 4:40 PM
To: 'Oracle-L_at_Freelists. 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.
Thank You.
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Aug 23 2005 - 08:26:21 CDT
 |
 |