oracle ddl триггер: создайте резервную копию таблицы перед сбросом

Question

Я хочу создать резервную копию таблицы с помощью триггера ddl (до сброса) и столкнулся со следующей проблемой.

Это нормально, пока происходит первое удаление: таблица a_backup содержит данные удаленной таблицы. Но почему я не могу бросить еще один стол после этого?

ORA-01031: недостаточные привилегии

---

---

create table b (x number);

- Таблица Б создана.

create table a (x number);

- Таблица А создана.

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

- Таблица A_BACKUP создана.

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;
/

- Триггер A_BACKUP_TR скомпилирован

---

-- 1

drop table a;

- Таблица А упала.

---

-- 2

drop table b;

- ORA-04045: ошибки во время перекомпиляции / повторной проверки TRANEE.A_BACKUP_TR

- ORA-01031: недостаточные привилегии

И вы не можете удалить любую таблицу после удаления, за исключением того, что вы снова запускаете сценарий создания или замены триггера. Есть ли проблема с частью IF-THEN? Когда таблица A не существует, оператор IF должен перейти в NULL?

Answer 1

Но почему я не могу бросить еще один стол после этого?

insert into a_backup select * from a; 

В триггере вы явно ссылаетесь на таблицу А, и на данный момент она не существует.

Вы можете использовать динамический 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;
/

Лично мне не нравится идея использовать триггер для такого механизма. Также глухая вставка и SELECT * может потерпеть неудачу, если схема сместится в будущем. Возможно, лучший подход - Flashback Drop (Recycle Bin)

---

РЕДАКТИРОВАТЬ:

Как упомянуто @wolφi, чтобы смягчить слепую вставку, вы можете создать таблицу внутри триггера:

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;
/