Update query in AFTER INSERT is ignored

Question

I have this triggers, that to put it simple do this things:

Before Insert:creates new record with new id,name and puts value in start time cols.

After insert: updates value in end time cols.

create or replace trigger stg_control_inserts_before
  before insert on Distributionplan
  for each row
declare
  GetError varchar2(280);
  Jobname varchar2(20) := 'DistributionPlan';
  HoraActual  timestamp (6) := systimestamp;
  FullJobName varchar2 (80) := Jobname ||' '|| HoraActual;
Begin
  insert into stg_control  (job_name,job_id,execution_start,exec_tocore_start) 
   values (FullJobName,stg_control_seq.nextval,HoraActual,HoraActual);
end;
/

create or replace trigger stg_control_inserts
  after insert on Distributionplan
  for each row
declare
  HoraActual  timestamp := systimestamp;
Begin
  update stg_control set execution_end='14/05/19 00:00:00,493000000' 
  where job_id in (select max(job_id) from stg_control);
end;
/

The issue is that the second trigger is ignored, the first one that creates the new record is executed but the second one, that should update the end time doesn't do anything. I tried passing a hardcoded value instead of the variable but doesn't work either.

Regards.

Answer 1

I'm not sure why your trigger isn't working - unless it's because other rows have been inserted in between the two triggers firing.

I suspect your issues may be mitigated by using a compound trigger along the lines of:

CREATE OR REPLACE TRIGGER stg_control_inserts
  FOR insert on Distributionplan
    COMPOUND TRIGGER

    v_job_id stg_control.job_id%TYPE;
    v_job_name stg_control.job_name%TYPE := 'DistributionPlan';

   BEFORE EACH ROW IS
   BEGIN
     INSERT INTO stg_control (job_name, job_id, execution_start, exec_tocore_start)
     VALUES (v_job_name || ' ' || SYSTIMESTAMP, v_job_id, SYSTIMESTAMP, SYSTIMESTAMP)
     RETURNING job_id INTO v_job_id;
   END BEFORE EACH ROW;

   AFTER EACH ROW IS
   BEGIN
     UPDATE stg_control
     SET    execution_end = '14/05/19 00:00:00,493000000'
     WHERE  job_id = v_job_id;
   END AFTER EACH ROW;
END stg_control_inserts;
/

N.B. untested.