DDL Trigger question

From: Rudy Susanto <rsusanto_at_atlas-sp.com>
Date: 30 Jul 2002 01:30:16 -0700
Message-ID: <1a928d0b.0207300030.4d450a18@posting.google.com>

Hi,

How to prevent my objects from deletion? I think I can use DDL trigger but i found a problem. Here is the script.

SQL>connect scott/tiger
Connected.

SQL> create trigger tr_drop
  2 before drop on schema
  3 begin
  4 raise_application_error(-20000,'drop object may not allow...');   5 end;
  6 /
Trigger created.

SQL> create table emp2 as select * from emp where 1=2; Table created.

SQL> drop table emp2;
drop table emp2
*
ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1
ORA-20000: drop object may not allow...
ORA-06512: at line 2

SQL> conn system/manager
Connected.

SQL> drop table scott.emp2;
Table dropped.
SQL> When I connect as SCOTT, I can't drop the table but when I connect as SYSTEM, I can drop it easily. Why this trigger was't running as i expected?

This trigger works on schema level, is it possible if i want to know which object that user want to delete?

I am using Oracle 8.1.7 NT.

Thank in advance,

Rudy Susanto Received on Tue Jul 30 2002 - 03:30:16 CDT