限制oracle中特定行的触发器更新量

ohtdti5x 于 2022-12-29 发布在 Oracle

关注(0)|答案(1)|浏览(209)

我想审计特定表中的更新更改，为此我创建了一个触发器，它跟踪每次行更新，然后将更新的更改写入新的历史表中：

create table test (id number generated always as identity,name varchar2(10) default null, school varchar2(10) null);
insert into test (name,school) values ('John','MIT');
insert into test (name,school) values ('Max','Oxford');

create table test_history (id int,name varchar2(10), school varchar2(10));

create or replace trigger test_trigger
after update
of name,school
on test
for each row
begin
insert into test_history
values 
(
:old.id,
:new.name,
:new.school
);
end;
/

我想做的是将特定行的更新量限制在一定的值，例如，下面的update语句只能执行10次：

update test
set 
name = 'Jason'
where id = 1;

这样，如果我执行上面的语句10次，它应该可以工作，但是如果执行到第11次，它应该失败，所以一个特定的唯一id的最大行数是10。

oracle

来源：https://stackoverflow.com/questions/74938330/limit-the-amount-of-trigger-updates-for-a-specific-row-in-oracle

1条答案

按热度按时间

rqmkfv5c1#

计算 * history * 表中的行数，如果超过您认为合适的值，则引发错误。

SQL> create or replace trigger test_trigger
  2    after update
  3    of name,school
  4    on test
  5    for each row
  6  declare
  7    l_cnt number;
  8  begin
  9    select count(*) into l_cnt
 10    from test_history
 11    where id = :new.id;
 12
 13    if l_cnt <= 10 then
 14       insert into test_history
 15       values
 16       (
 17          :old.id,
 18          :new.name,
 19          :new.school
 20       );
 21    else
 22       raise_application_error(-20000, 'Too many updates');
 23    end if;
 24  end;
 25  /

Trigger created.

更新：

SQL> update test set name = 'Jason' where id = 1;

1 row updated.

<snip>

SQL> update test set name = 'Jason' where id = 1;

1 row updated.

SQL> update test set name = 'Jason' where id = 1;
update test set name = 'Jason' where id = 1
*
ERROR at line 1:
ORA-20000: Too many updates
ORA-06512: at "SCOTT.TEST_TRIGGER", line 17
ORA-04088: error during execution of trigger 'SCOTT.TEST_TRIGGER'

SQL>

赞(0）回复(0）举报 2022-12-29

我来回答

限制oracle中特定行的触发器更新量

1条答案

相关问题

热门标签

最新问答