Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select * from V$VERSION;


BANNER
BANNER_FULL
BANNER_LEGACY
CON_ID


Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
0


create table bb_tax ( tr_id integer generated always as identity 
                     , taxrate numeric(9,6) 
                     ) ;
create table tax_change_info ( tci_id      integer generated always as identity 
                             , user_id     varchar2(30) 
                             , tr_id       integer
                             , old_taxrate numeric(9,6) 
                             , new_taxrate numeric(9,6) 
                             );
create or replace 
procedure capture_tax_rate_change( tr_id_in    bb_tax.tr_id%type
                                 , old_rate_in bb_tax.taxrate%type
                                 , new_rate_in bb_tax.taxrate%type
                                 ) 
as 
   PRAGMA AUTONOMOUS_TRANSACTION;
begin 
   insert into tax_change_info(user_id, tr_id, old_taxrate,  new_taxrate)
        values (user, tr_id_in, old_rate_in, new_rate_in);
   commit; 
end capture_tax_rate_change; 
/
create or replace trigger taxrate_bru
before update of taxrate 
on bb_tax 
for each row 
begin
    capture_tax_rate_change(:old.tr_id, :old.taxrate, :new.taxrate); 
    raise_application_error(-20505,'You are not allowed to update taxes.');   
end  taxrate_bru;
/
insert into bb_tax (taxrate)
    select round(dbms_random.value * 10, 4) 
      from dual connect by level <= 3; 
3 rows affected

select * from bb_tax; 


TR_ID
TAXRATE


1
7.6157

2
7.1171

3
9.4489


select * from tax_change_info; 
-- Attempt to Update a tax_rate;
update bb_tax
   set taxrate = 6.75
 where tr_id = 1; 
ORA-20505: You are not allowed to update taxes.
ORA-06512: at "FIDDLE_WZOAXASVWURIABIXNCLG.TAXRATE_BRU", line 3
ORA-04088: error during execution of trigger 'FIDDLE_WZOAXASVWURIABIXNCLG.TAXRATE_BRU'

select * from bb_tax; 


TR_ID
TAXRATE


1
7.6157

2
7.1171

3
9.4489


select * from tax_change_info; 


TCI_ID
USER_ID
TR_ID
OLD_TAXRATE
NEW_TAXRATE


1
FIDDLE_WZOAXASVWURIABIXNCLG
1
7.6157
6.75