Re: Debug DDL trigger
- From: rjamya <rjamya@xxxxxxxxx>
- To: Jared Still <jkstill@xxxxxxxxx>
- Date: Wed, 24 Aug 2005 08:09:08 -0400
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@xxxxxxxxx> 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@xxxxxxxxxxxxx> 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
> >
> > --
> > //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';
Other related posts:
