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

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

ddl-trigger triggers plsql oracle

diaphol · 技术社区 · 8 年前

我想用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 回复 | 直到 8 年前

Lukasz Szozda 8 年前

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

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

推荐文章

Ilia K · 当通过NEW更改字段时,不会执行Postgres触发器

1 年前

Saijo-Shi · PLpgsql中的更新触发器

1 年前

user26814368 · Oracle在触发器中过滤错误数据

2 年前

geoCode · 如何在不授予其ALTER权限的情况下从数据库前端执行包含ALTER语句的SQL Server存储过程?

2 年前

Mark · 使用Select子句的MySql After Insert触发器

2 年前

robertspierre · 在多对多关系中自动删除未引用的行

2 年前

Finn Kimbrel · 如何在SQL触发器中泛化“WHERE”子句

2 年前

Martin · 由于活动触发器,无法登录到SQL Server

2 年前

gonzalo2000 · 脚本在手动运行时有效,但触发器不起作用

3 年前

yessoos · 在触发的处理程序中使用类属性

3 年前