首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >Oracle触发器,用于防止根据条件插入新行

问Oracle触发器,用于防止根据条件插入新行
EN

Stack Overflow用户
提问于 2018-06-09 02:11:55
回答 2查看 2.1K关注 0票数 0

我发现解决相同问题的问题很少,但没有更好的解决方案。我需要创建一个Oracle触发器,它将阻止对某个条件执行新的插入操作,但不会引发错误。

例如:我只需要停止插入带有bar='FOO‘的行。(我无法编辑表的约束,无法访问真正执行插入等操作的过程,因此触发器是唯一的选项)

到目前为止,解决方案证实了这是不可能的。一个有希望的建议是创建一个中间表,在bar='FOO‘时插入键值,然后在插入完成后从原始表中删除这些记录,我想这是不正确的。任何答案都将受到高度赞赏。

EN

回答 2

Stack Overflow用户

发布于 2018-06-11 22:49:00

显然,使用触发器停止插入而不引发异常是不可能的。

但是,如果您有权访问该模式(并询问触发器,这可能是可以的),您可以考虑用视图和instead of触发器来替换表。

作为当前表的最小模型。myrole只是表上被授予的特权的替身:

代码语言:javascript
复制
CREATE ROLE myrole;

CREATE TABLE mytable (
  bar VARCHAR2(30)
);
GRANT ALL ON mytable TO myrole;

现在重命名该表,并确保没有人可以直接访问它,并将其替换为视图。此视图可以通过instead of触发器进行保护:

代码语言:javascript
复制
REVOKE ALL ON mytable FROM myrole;
RENAME mytable TO myrealtable;

CREATE OR REPLACE VIEW mytable AS SELECT * FROM myrealtable;
GRANT ALL ON mytable TO myrole;

CREATE OR REPLACE TRIGGER myioftrigger 
  INSTEAD OF INSERT ON mytable
  FOR EACH ROW
BEGIN
  IF :new.bar = 'FOO' THEN
    NULL;
  ELSE 
    INSERT INTO myrealtable(bar) VALUES (:new.bar);
  END IF;
END;
/

因此,如果有人将普通行插入到伪视图中,数据就会插入到您的真实表中:

代码语言:javascript
复制
INSERT INTO mytable(bar) VALUES('OK');
1 row inserted.

SELECT * FROM mytable;
OK

但是,如果有人插入了魔术值'FOO',触发器会默默地将其吞下,并且在真实表中不会发生任何更改:

代码语言:javascript
复制
INSERT INTO mytable(bar) VALUES('FOO');
1 row inserted.

SELECT * FROM mytable;
OK

注意:如果您还想保护您的表不受更新的影响,则必须为更新添加第二个触发器。

票数 0
EN

Stack Overflow用户

发布于 2018-06-11 23:01:29

一种方法是隐藏行。从12c开始,这相当简单:

代码语言:javascript
复制
create table demo
( id integer primary key
, bar varchar2(10) );

-- This adds a hidden column and registers the table for in-database archiving:
alter table demo row archival;

-- Set the hidden column to '1' when BAR='FOO', else '0':
create or replace trigger demo_hide_foo_trg
before insert or update on demo
for each row
begin
    if :new.bar = 'FOO' then
        :new.ora_archive_state := '1';
    else
        :new.ora_archive_state := '0';
    end if;
end demo_hide_foo_trg;
/

-- Enable in-database archiving for the session
-- (probably you could set this in a log-on trigger):
alter session set row archival visibility = active;

insert into demo (id, bar) values (1, 'ABC');
insert into demo (id, bar) values (2, 'FOO');
insert into demo (id, bar) values (3, 'XYZ');

commit;

select * from demo;

      ID BAR
-------- --------
       1 ABC
       3 XYZ

-- If you want to see all rows (e.g. to delete hidden rows):
alter session set row archival visibility = all;

在早期版本的Oracle中,您可以使用安全策略来实现相同的功能。

另一种方法可能是在触发器中添加一个‘bar = 'FOO'’标志,该标志默认为'Y‘,并在触发器中将其设置为'N’,并且(假设您不能更改应用程序以使用视图等),让第二个触发器删除所有这些行(或者可能更好,将它们移动到存档表中)。

代码语言:javascript
复制
create table demo
( id integer primary key
, bar varchar2(10) );

alter table demo add required_yn varchar2(1) default on null 'Y';

create or replace trigger demo_set_not_required_trg
before insert or update on demo
for each row
begin
    if :new.bar = 'FOO' then
        :new.required_yn := 'N';
    end if;
end demo_hide_foo_trg;
/

create or replace trigger demo_delete_not_required_trg
after insert or update on demo
begin
    delete demo where required_yn = 'N';
end demo_delete_not_required_trg;
/
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/50766292

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档