limit the amount of trigger updates for a specific row in oracle-CodePudding

I want to audit update changes in a specific table, and for that I've created a trigger that tracks every time a row is updated, it then write the updated changes into a new historical table:

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

What I would like to do is to limit the amount a specific row is updated to a certain value. For example, the following update statement can only be executed 10 times:

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

In this way if I execute the above statement 10 times it should work, but if the execution happens the 11th time it should fail. So the maximum amount of rows of a specific unique id is 10.

CodePudding user response：

Count number of rows in the history table and raise an error if it exceeds value you find appropriate.

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.

Update:

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>