oracle ddl trigger: create backup table with before drop

Question

I want to create a backup table with ddl trigger (before drop) and encountered the following problem.

It is okay while the first drop happens: the a_backup table contains the data of dropped table. But why I cannot drop another table after this?

ORA-01031: insufficient privileges

---

---

create table b (x number);

-- Table B created.

create table a (x number);

-- Table A created.

create table a_backup as select * from a where 1 = 0;

-- Table A_BACKUP created.

create or replace trigger a_backup_tr
    before drop
    on database
begin
    IF ora_dict_obj_name <> 'A' then
    null;
    ELSIF ora_dict_obj_name = 'A'
    and ora_dict_obj_owner = 'TRANEE' then
    insert into a_backup
    select * from a;
    ELSE null;
    end if;
end;
/

-- Trigger A_BACKUP_TR compiled

---

-- 1

drop table a;

-- Table A dropped.

---

-- 2

drop table b;

-- ORA-04045: errors during recompilation/revalidation of TRANEE.A_BACKUP_TR

-- ORA-01031: insufficient privileges

And you cannot drop any table after the drop except you runs the create or replace trigger script again. Is there a problem with the IF-THEN part? When the table A doesn't exist, the IF-statement has to go into NULL?

Answer 1

But why I cannot drop another table after this?

insert into a_backup select * from a; 

In trigger you explicitly refer to table A and it does not exist at that moment.

You could use dynamic SQL:

create or replace trigger a_backup_tr
    before drop
    on database
begin
    IF ora_dict_obj_name <> 'A' then
        null;
    ELSIF ora_dict_obj_name = 'A' and ora_dict_obj_owner = 'TRANEE' then
        EXECUTE IMMEDIATE 'insert into tranee.a_backup select * from tranee.a';
    ELSE null;
    end if;
end;
/

Personally I don't like the idea of using trigger for such mechanism. Also blind insert and SELECT * may fail if schema drifts in the future. Perhaps better approach is Flashback Drop (Recycle Bin)

---

EDIT:

As mentioned by @wolφi to mitigate blind insert you could create table inside trigger:

create or replace trigger a_backup_tr
    before drop
    on database
begin
    IF ora_dict_obj_name <> 'A' then
      null;
    ELSIF ora_dict_obj_name = 'A' and ora_dict_obj_owner = 'TRANEE' then
      --TODO: additional check if table already exists
      EXECUTE IMMEDIATE 'CREATE TABLE tranee.a_backup AS SELECT * FROM tranee.a';
    ELSE null;
    end if;
end;
/