Drop User Trigger

create or replace trigger tg_dropuser before drop on database
when (ora_dict_obj_type ='USER') 
 declare
 l_name varchar2(30);
 begin
 l_name := ORA_DICT_OBJ_NAME;
execute immediate 'delete from ldbo.tbloperatormenurights where coperator = ''' ||ora_dict_obj_name || '''';
 end;
/

create or replace trigger trUserDrop after drop on database 

when (ora_dict_obj_type ='USER') 

begin 

dbms_output.put_line('user dropped '|| ora_dict_obj_name); 

end; 

/

if you have the privilege granted like Grant Create User, Drop, etc., this is good example:

Use a database BEFORE DROP trigger. Make sure to create this as some other user (then the 

user doing the drop, else they can drop the trigger!)

 grant create session, create user, drop user to a 

identified by a;

Grant succeeded.

 grant create session to b identified by b;

Grant succeeded.

 create table app_users ( username varchar2(30) );

Table created.

 insert into app_users values ( 'B' );

1 row created.

 create or replace trigger drop_user_trigger

 before drop on database

 when ( user = 'A' )

 declare

 l_cnt number;

 l_name varchar2(30);

 begin

 if ( ora_dict_obj_type = 'USER' )

 then

 l_name := ORA_DICT_OBJ_NAME;

 select count(*) into l_cnt

 from dual

 where exists ( select null

 from app_users

 where username = l_name );

 if ( l_cnt <> 1 )

then

 raise_application_error( -20001, 'You cannot drop that user' );

end if;

 end if;

 end;

/

Trigger created.

 @connect a/a

 drop user scott;

drop user scott

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-20001: You cannot drop that user

ORA-06512: at line 15

drop user b;

User dropped.