Example of before and after triggers in Oracle

Make a table insert trigger to modify only the fields of the inserted rows.

Create or replace trigger tr_rme_slot
Before insert on rme_slot
For each row
Begin
If (: New. Position> = 0 and: New. Position <10) then
: New. slot_name: = '0' | to_char (: New. position );
Else
: New. slot_name: = to_char (: New. position );
End if;
End;

The insert row needs to be modified before insertion. The update statement is not required in trigger implementation.

At the same time, if you want to modify the table records in the trigger, you need to write as follows:

Create or replace trigger tr_rme_slot
After insert on rme_slot
For each row
Declare
Pragma autonomous_transaction;
Begin
If (: New. Position> = 0 and: New. Position <10) then
Update rme_slot set slot_name = '0' | to_char (: New. Position) Where slot_id =: New. slot_id;
Else
Update rme_slot set slot_name = to_char (: New. Position) Where slot_id =: New. slot_id;
End if;
Commit;
End;

Note that there is an additional declare section, and commit is required when the trigger ends.

Create or replace trigger tr_afterupdate
After insert or update
On test_trigger
For each row
Declare
Pragma autonomous_transaction;
Begin

Update test_trigger
Set t_fact = t_salary * (1-t_tax)-T_EAT-T_HOUSE
Where t_name =: New. t_name;
Commit;

Exception
When others then
-- Consider logging the error and then re-raise
Raise;
End tr_testupdate;