代码之家 › 专栏 › 技术社区 › diaphol

Oracle DDL触发器:使用before drop创建备份表

ddl-trigger triggers plsql oracle

diaphol · 技术社区 · 6 年前

我想用DDL触发器(在删除之前)创建一个备份表,但遇到了以下问题。

当第一次删除发生时是可以的:a_备份表包含已删除表的数据。但为什么我不能在这之后再放下一张桌子呢?

ORA-01031:权限不足

create table b (x number);

--表B已创建。

create table a (x number);

--表A已创建。

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

--表A创建的备份。

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

--触发已编译的备份

-- 1

drop table a;

--A表掉了。

-- 2

drop table b;

--ORA-04045:重新编译/重新验证Tranee.a_备份时出错

--ORA-01031:权限不足

并且除了再次运行create或replace触发器脚本之外,不能在删除后删除任何表。if-then部分有问题吗?当表A不存在时,if语句必须变为空?

1 回复 | 直到 6 年前

Lukasz Szozda 6 年前

但为什么我不能在这之后再放下一张桌子呢?

insert into a_backup select * from a;

在触发器中,您显式地引用表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)

编辑:

正如@wl_ 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;
/

推荐文章

MagicMatt · 由于值(…)中的“SELECT*”,MySQL触发器的插入导致基数冲突

2 年前

user1312312 · 如何为一组表编写通用触发器?

2 年前

max · 如果未在INSERT上设置字段,则触发器行为怪异

6 年前

Software Engineer · 如何将sql触发器转换为MYSQL触发器

6 年前

cninicu · 为什么在触发器内部没有执行raise\u application\u错误之前的代码?

6 年前

Taniq · 能否在SQL Server触发器中多次使用UPDATE()函数?

6 年前

Kankuro · 从Google工作表中删除自定义菜单

6 年前

Egor · SQL Server在数据库触发器中获取表名

6 年前

hope1234 · 如何在sql触发器中使用insert语句中的变量

6 年前

user7468494 · 触发器中的SQL大小写

6 年前