Solution to modifying the base table in Oracle triggers

Sometimes we may have this requirement: Create a trigger on the table, and then when the fields A and B are updated in the table, the field C is changed to a + B, this forms the situation of modifying the base table data in the trigger.

Create a test table:

View code

-- Create table
create table TEST
(
  A  NUMBER,
  B  NUMBER,
  C  NUMBER,
  ID NUMBER not null
)

-- Create/Recreate primary, unique and foreign key constraints 
alter table TEST
add constraint TEST_PRIMARY_ID primary key (ID)

 

Let's take a look at the writing method of the trigger below:

View code

create or replace trigger test_trigger
  after insert or update on test  
for each row
declare
-- local variables here
begin
update test set c = :new.a+:new.b where id = :new.id ;
end test_trigger;

Our goal is to make the value of field C equal to the value of field a + field B when inserting or updating. we execute the following SQL statement:

View code

insert into test(id,a,b) values(1,10,20);

 

We expect that the value of C is automatically changed to a + B = 30, but we find the following error:

The error message indicates that the table has changed and the trigger cannot read it, because the trigger time of the departure is set to "after, that is, after a record is inserted, the update operation is performed. However, the table has changed and the trigger cannot read the table. then, can we set the trigger time to before to achieve the goal? modify the trigger:

View code

create or replace trigger test_trigger
  before insert or update on test  
for each row
declare
-- local variables here
begin
update test set c = :new.a+:new.b where id = :new.id ;
end test_trigger;

 

Compile the trigger and execute the preceding SQL statement again. At this time, we find that the record has been successfully inserted and no error is reported. Does this mean we have succeeded? Don't worry, let's first query the test table:

We can see that although the record is successfully inserted, it does not achieve our intended purpose, that is, the value of C is changed to 30. Why? Is our trigger not executed? In fact, the trigger is actually executed here, but because the trigger is executed at before, that is, the update operation is performed before the record is inserted, but at this time, the table does not actually have id =: New. ID (equal to 1 at this time), so the number of rows affected by this update operation is zero, and then the insert operation is executed.

How can we achieve our goal? In fact, in the trigger, we can directly access the current record and modify the trigger:

View code

create or replace trigger test_trigger
  before insert or update on test  
for each row
declare
-- local variables here
begin
  :new.c := :new.a + :new.b;
end test_trigger;

You can use: New to access the value of the current row when the trigger is executed. then execute the insert operation:

We can see that the record is successfully inserted, and the C field is also the expected value.

In this way, we have achieved the goal of changing the base table in the trigger, but this method also has limitations:

1. Only before can be set for trigger execution. If after is set, compilation will fail.

2. You can only operate on the currently inserted or updated records, but cannot operate on other records.