Refactoring PL/SQL triggers - extract procedures
- by Juraj
Hello,
we have application where database contains large parts of business logic in triggers, with a update subsequently firing triggers on several other tables. I want to refactor the mess and wanted to start by extracting procedures from triggers, but can't find any reliable tool to do this. Using "Extract procedure" in both SQL Developer and Toad failed to properly handle :new and :old trigger variables.
If you had similar problem with triggers, did you find a way around it?
EDIT: Ideally, only columns that are referenced by extracted code would be sent as in/out parameters, like:
Example of original code to be extracted from trigger:
.....
if :new.col1 = some_var then
:new.col1 := :old.col1
end if
.....
would become :
procedure proc(in old_col1 varchar2, in out new_col1 varchar2, some_var varchar2) is
begin
if new_col1 = some_var then
new_col1 := old_col1
end if;
end;
......
proc(:old.col1,:new.col1, some_var);
