Hi Experts,
I am working for a customer where a lot of the business logic is implemented in triggers. We are on Oracle Database 12c Enterprise Edition Release 12.2.0.1.0
One of the trigger types handles the label of a product. Depending on the type of product, a label can be based on different tables and columns. Each of these tables will have a trigger in this format:
create or replace trigger handle_label
before update of
relevant_column1,
relevant_column2
on this_table
begin
update another_table
set label = :new.relevant_column1 || ' ' || :new.relevant_column2
where ...;
end;
Just as an example. The actual code is not important here. The problem is with a second trigger:
create or replace trigger handle_something_else
before update of
other_column1,
other_column2
on this_table
begin
:new.relevant_column1 := 'value';
end;
The second trigger changes a field that is relevant for the first trigger that handles the label update. But since the change is through an assignment instead of an update, it does not fire the handle_label trigger.
I understand that the first trigger is not fired, because this could lead to trigger recursion.
~One solution would be to remove the "before update of columns" in the trigger. But that would cause many useless firing of the trigger and decrease performance.~ <- on second thought, no this would also not work, because :new.relevant_column := 'value' will never fire any trigger.
What would be the best approach to detect or handle the change that is made to :new.relevant_column in the second trigger so that we can ensure that the label is always correctly updated after each change.
Many thanks!
Rop