We have an application in which the database contains large parts of the business logic in triggers, followed by updating activation triggers on several other tables. I want to reorganize the mess and want to start by extracting the procedures from the triggers, but I cannot find a reliable tool for this. Using the "Retrieval Procedure" in SQL Developer and Toad failed to process properly: new and: old trigger variables.
If you had a similar problem with triggers, did you find a way around this?
EDIT: Ideally, only the columns referenced by the extracted code will be sent as input / output parameters, for example:
Example source code to be extracted from a trigger:
.....
if :new.col1 = some_var then
:new.col1 := :old.col1
end if
.....
will 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);