Oracle: Create Trigger on drop user

oracle, oracle11g, triggers

Solution

Of course you can. Assuming you're actually dropping a user and not a table:

You're looking for a system trigger as opposed to a schema trigger or a database trigger. You can add these to a large number of ddl events including `DROP`.

To quote from the documentation:

Causes the database to fire the trigger whenever a DROP statement removes a database object from the data dictionary.

Your trigger might look something like the following:

create or replace trigger tr_drop_user
  before drop on database

begin
   if dbms_standard.dictionary_obj_type = 'USER' then

      insert into logs ( user, object_name )
      values ( sys_context('USERENV','OS_USER') -- OS User doing the dropping
             , dbms_standard.dictionary_obj_name -- name of object being dropped
              );

   end if;
end;
/

This uses the not particularly well documented `dbms_standard` package to work out what's happening. PSOUG has better documentation, in my opinion.

If you only want to do this for a specific schema rather than for the entire database use `before drop on schema` instead.

Just as a little side note I'm not convinced of the need to do this in a trigger. It would be better if you created a package with `drop_user` and `create_user` procedures that do everything you want in one place rather than disguising the logic in a trigger.

Problem

Can a trigger be created on drop user event? if so, how do I write the the trigger - * I want to delete some rows related to that user from a table after drop user * "Drop User" Can you provide an example: If the user is drop and oracle purge the user's schema objects is there anyway I can select what row of data I want to keep or delete? So are you saying that a Trigger can not be created on drop user event?

Original source