SQL & PL/SQL

I've recently started trying to automate around a dozen procedures. These procedures are set to run immediately after the necessary previous procedure(s) is(are) done.

What I am attempting to accomplish is a single generic trigger that will fire off each procedure when its parent procedures have finished firing. This will be accompanied by an update_flag table with three columns

PARENT_PRC----------------------CHILD_PRC----------------------FLAG
parent_prc_name1--------------child_prc_name1-----------------N
parent_prc_name1--------------child_prc_name2-----------------N
parent_prc_name3--------------child_prc_name3-----------------Y

Logic:
*1.* When a procedure fires it updates this table to set any rows in which it is the “PARENT_PRC” by updating the FLAG column to = Y.
*2.* The trigger will execute a child procedure if its flag (or in the case of multiple parent procedures; all of its flags) are set to 'Y'. This trigger is set to fire AFTER a table update on the UPDATE_FLAG table.
----a. I have to execute the procedure UFLAG in a job because I want the trigger to execute the procedure and then continue running immediately, rather than wait for the procedure to finish then commit. This way the trigger could start several procedures all running at the same time.
----b. I have made it an autonomous transaction because I needed the job to fire immediately rather than be queued, which required a commit within the trigger.
*3.* The last step is to set the flag in UPDATE_FLAGS back to 'N' for CHILD_PRC = '||uflag||' once the child procedure is complete.
----a. I have tried placing the update child_prc = 'N' in the trigger but it won’t allow a trigger that fires on update to update the same table.
----b. I want to avoid putting the update statement in all of my procedures because I would like the option of running these procedures manually for testing purposes WITHOUT effecting the update_flags table.

Number 3. is the key problem I have been having. Placing code within the trigger to update the update_flags table setting 'Y's back to 'N's once the procedures have fired causes a deadlock error.
I believe this is simply because the trigger is attempting to update a table which (upon updating) causes the same trigger to fire before it has finish executing.


How can I update the Flag table to reset the update flags back to 'N'?
Is there a different way of doing this all together?

Here is some code with dummy procedures that demonstrates what I have so far.
With this code, executing parent procedures should set the update_flag table to 'Y' for FLAG where procedure = 'parent_prc'.
I need to find a way to execute the child procedures AND set the FLAG column back to 'N' from the trigger.

ex. executing parent_1 should set update_flags.flag = 'Y' where parent_prc = 'parent_1' and thus execute procedure CHILD_A and CHILD_B.

create table update_flags (parent_prc varchar2(10), child_prc varchar2(10), flag varchar2(1));
insert into update_flags values('parent_1', 'child_a', 'N');
insert into update_flags values('parent_1', 'child_b', 'N');
insert into update_flags values('parent_2', 'child_c', 'N');
insert into update_flags values('parent_3', 'child_c', 'N');
insert into update_flags values('parent_4', 'child_d', 'N');

CREATE OR REPLACE procedure parent_1 as 
BEGIN
update update_flags set flag = 'Y' where parent_prc = 'parent_1';
END parent_1;
/
CREATE OR REPLACE procedure parent_2 as 
BEGIN
update update_flags set flag = 'Y' where parent_prc = 'parent_2';
END parent_2;
/
CREATE OR REPLACE procedure parent_3 as 
BEGIN
update update_flags set flag = 'Y' where parent_prc = 'parent_3';
END parent_3;
/
CREATE OR REPLACE procedure parent_4 as 
BEGIN
update update_flags set flag = 'Y' where parent_prc = 'parent_4';
END parent_4;
/
CREATE OR REPLACE procedure child_a as 
BEGIN
dbms_output.PUT_LINE('CHILD_A Worked');
commit;
END child_a;
/
CREATE OR REPLACE procedure child_b as 
BEGIN
dbms_output.PUT_LINE('CHILD_B Worked');
commit;
END child_b;
/
CREATE OR REPLACE procedure child_c as 
BEGIN
dbms_output.PUT_LINE('CHILD_C Worked');
commit;
END child_c;
/
CREATE OR REPLACE procedure child_d as 
BEGIN
dbms_output.PUT_LINE('CHILD_D Worked');
commit;
END child_d;
/


CREATE OR REPLACE TRIGGER MASTER_TRG
AFTER UPDATE 
ON UPDATE_FLAGS
DECLARE
Pragma  AUTONOMOUS_TRANSACTION;
BEGIN
  
  DECLARE
  job_num number;
  uflag varchar2(1000); 
    
  BEGIN
        select  MAX(case when COUNT(case when flag='Y' then 1 end)=COUNT(*) then CHILD_PRC else '  ' end)
        into uflag
        from        update_flags
        group by    child_prc;
                 
        IF   uflag <> '  ' THEN
                                  --update update_flags set  flag = 'N' where child_prc = uflag     --(line of code that causes deadlock error)

                        dbms_job.submit (job => job_num,
                        what => ' '||uflag||';'
                       );                   

        END IF;  
        
   END;             
COMMIT; 
END MASTER_TRG;
/


execute parent_2;
execute parent_3;