Re: Debug DDL trigger

From: rjamya <rjamya_at_gmail.com>
Date: Wed, 24 Aug 2005 08:09:08 -0400
Message-ID: <9177895d05082405093888df0d@mail.gmail.com>

Not true, see below for an example on 9204

SQL> get t1.sql
1 conn t1/t1
2 create table t1.some_tbl tablespace cons 3 as select * from dba_objects where rownum < 100 4 /
5 drop trigger raj_test_trig
6 /
7 alter session set events '10046 trace name context forever, level 4' 8 /
9 CREATE OR REPLACE TRIGGER raj_test_trig 10 BEFORE DDL ON SCHEMA
11 DECLARE
12 PRAGMA AUTONOMOUS_TRANSACTION;

13 v_datestamp DATE := sysdate;
14 v_ddl_stmt VARCHAR2(4000);
15 v_ora_error NUMBER;
16 v_table_count PLS_INTEGER := 1;

17 BEGIN

18 dbms_output.enable(1000000);
19 dbms_output.put_line('Trigger: checking for table');
20 dbms_output.put_line('Trigger: sysevent is ' || ora_sysevent);

21 if ora_sysevent = 'DROP' then
22 SELECT COUNT(*) INTO v_table_count
23 FROM user_tables
24 WHERE table_name = ora_dict_obj_name; 25 dbms_output.put_line('Trigger: table count is ' || v_table_count); 26 IF v_table_count > 0 THEN
27 v_ddl_stmt := 'CREATE TABLE t1.'||ora_dict_obj_name||'_bkp AS SELECT * FROM t1.'||ora_dict_obj_name;
28 dbms_output.put_line('Trigger cmd: ' || v_ddl_stmt); 29 EXECUTE IMMEDIATE v_ddl_stmt;
30 END IF;
31 END IF;
32 END;
33 /
34 set serveroutput on
35 drop table some_tbl;
36* select table_name from user_tables;

SQL> @t1
Connected.
create table t1.some_tbl tablespace cons *
ERROR at line 1:
ORA-00955: name is already used by an existing object

Trigger dropped.

Session altered.

Trigger created.

drop table some_tbl
*
ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1
ORA-06519: active autonomous transaction detected and rolled back
ORA-06512: at line 19

TABLE_NAME

SOME_TBL SQL> connect / as sysdba
Connected.
SQL> drop table t1.some_tbl;

Table dropped.

Raj

On 8/24/05, Jared Still <jkstill_at_gmail.com> wrote:
>
> I believe the triggers that Raj was thinking of were logon triggers.
>
> Those do not fire for accounts with the DBA privilege.
>
> Jared
>
> On 8/23/05, Wolfgang Breitling <breitliw_at_centrexcc.com> wrote:
> >
> > At 08:08 AM 8/23/2005, rjamya wrote:
> > >psst: for all this to succeed, you should be a non-dba user ...
> > >these triggers don't fire for people with DBA privs.
> >
> > Not quite correct. They don't fire for sys, but they DO fire for
> > ordinary users, even those with the DBA role.
> >
> >
> > Regards
> >
> > Wolfgang Breitling
> > Centrex Consulting Corporation
> > http://www.centrexcc.com
> >
> > --
> > http://www.freelists.org/webpage/oracle-l
> >
>
>
>
> --
> Jared Still
> Certifiable Oracle DBA and Part Time Perl Evangelist
>
>

-- 
------------------------------
select standard_disclaimer from company_requirements where category = 
'MANDATORY';

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Aug 24 2005 - 07:11:45 CDT