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.
Best Answer
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:
N.B. untested.