Skip to main content

We have a custom event that executes on a change to APPROVAL_ROUTING_TAB.  The problem for us is we don’t want that to execute when the reason the record changed in the Approval_ROUTING_TAB is that a prior step in the approval process was deleted.  (Users are supposed to remove steps from the bottom up to avoid this but frequently forget.)

Is there a way I can tell that?  Do other fields besides LINE_NO get updated when a prior step is deleted?  Would it work to add an outer IF statement on the Event Action SQL code something like

If &NEW:LINE_NO<&OLD:LINE_NO AND (All other fields New = Old) then 

--skip the action because prior step was deleted.

else

--insert the existing sql code here

end if;

I’d suggest one of two options:

  1. Create another event that blocks users (whith something like Error_SYS.Record_General) from removing rows in APPROVAL_ROUTING_TAB if its &NEW:LINE_NO <> max_line_no_, where max_line_no_ is fetched with a cursor
  2. Instead of creating separate event, you can put 1. code in your existing event

Both of these would require PRAGMA AUTONOMOUS_TRANSACTION (in DECLARE) - if you decide to use it I’d personally go with 1st option since you really don’t want to do anything else with autonomous transactions aside from blocking users.


Reply