A bit of a background first:

I have 2 tables, HOLD and CALC. When I start a transaction, a row is created in the HOLD table with JOB_ID, STATUS:START, and TIME: SYSDATETIME. When the job is completed, another row is created in the HOLD table with the same JOB_ID, STATUS:END and TIME: SYSDATETIME. So when the transaction is completed, the HOLD table will look like this:

I plan to create a trigger that activates after the status END data is inserted. This trigger will pull the JOB_ID and calculate (TIME2 - TIME1) and insert it into the CALC table as minutes or seconds only. So in the end, there will only be 1 row for each JOB_ID.

I'm currently having trouble with selecting the rows with the same JOB_ID as I keep getting error when I try to use a where clause, and how to insert the time into variables so I can use TIME_DIFF.

CREATE OR REPLACE TRIGGER "TEST".CALC_TRIGGER AFTER INSERT ON "HOLD" REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
DECLARE
v_STATUS VARCHAR2(1 Byte);
BEGIN
IF (:NEW.STATUS = 'END') THEN
BEGIN
    INSERT INTO CALC(JOB_ID, TIME_TAKEN)
VALUES (:OLD.JOB_ID, NULL);
END;
END IF;
END;
/

Thanks in advance!

CodePudding user response：

With sample data like here:

WITH
    tbl AS
        (
            Select 1234 "JOB_ID", 'START' "STATUS", To_Date('01.01.2023 17:12:35', 'dd.mm.yyyy hh24:mi:ss') "TIME" From Dual Union All
            Select 1234 "JOB_ID", 'END' "STATUS", To_Date('04.01.2023 10:10:10', 'dd.mm.yyyy hh24:mi:ss') "TIME" From Dual Union All
            
            Select 4567 "JOB_ID", 'START' "STATUS", To_Date('01.01.2023 17:12:35', 'dd.mm.yyyy hh24:mi:ss') "TIME" From Dual Union All
            Select 4567 "JOB_ID", 'END' "STATUS", To_Date('03.01.2023 11:12:13', 'dd.mm.yyyy hh24:mi:ss') "TIME" From Dual Union All 
            
            Select 6789 "JOB_ID", 'START' "STATUS", To_Date('01.01.2023 17:12:35', 'dd.mm.yyyy hh24:mi:ss') "TIME" From Dual Union All
            Select 6789 "JOB_ID", 'END' "STATUS", To_Date('06.01.2023 17:12:35', 'dd.mm.yyyy hh24:mi:ss') "TIME" From Dual 
        ),

You can create a CTE to get your starting and ending dates(times) in the same row...

  jobs AS
    (   Select   t.JOB_ID "JOB_ID",
                (Select TIME From tbl Where JOB_ID = t.JOb_ID And STATUS = 'START') "STARTED",
                (Select TIME From tbl Where JOB_ID = t.JOb_ID And STATUS = 'END') "ENDED"
        From    tbl t
        Group By  JOB_ID
    )

... now you can get your date-time difference for any job id ...

Select  JOB_ID "JOB_ID",
        To_Char(STARTED, 'dd.mm.yyyy hh24:mi:ss' ) "STARTED",
        To_Char(ENDED,   'dd.mm.yyyy hh24:mi:ss' ) "ENDED",
        Trunc( ENDED - STARTED ) "DAYS", 
        Trunc( (ENDED - STARTED ) * 24 ) - ( Trunc( ENDED - STARTED ) * 24 ) "HOURS",
        Trunc( mod( (ENDED - STARTED ) *24 * 60, 60 ) ) "MINUTES",
        Trunc( mod( (ENDED - STARTED ) *24 *60 * 60, 60 ) ) "SECONDS"
From    jobs
Where   JOB_ID = 1234
--  
--  R e s u l t :
--      JOB_ID STARTED             ENDED                     DAYS      HOURS    MINUTES    SECONDS
--  ---------- ------------------- ------------------- ---------- ---------- ---------- ----------
--        1234 01.01.2023 17:12:35 04.01.2023 10:10:10          2         16         57         34

CodePudding user response：

As long as an access to the table is not possible in for each row trigger, you need to use a compound trigger for this:

create trigger trg_hold_
for insert on hold_
when (new.status = 'END')

compound trigger
  /*List of changed JOB_IDs*/
  g_jobs sys.odcinumberlist := sys.odcinumberlist();

after each row is
begin
  g_jobs.extend(1);
  g_jobs(g_jobs.last) := :new.job_id;
end after each row;

after statement is
begin
  /*Insert the timing into CALC table for all added job_id*/
  insert into calc(job_id, time_taken)
  select
    job_id,
    /*MAX is used to select the latest start and finish*/
    max(decode(status, 'END', time_)) - max(decode(status, 'START', time_))
  from hold_
  where job_id in (select * from table(g_jobs))
  group by job_id;
end after statement;

end;/

insert into hold_(job_id, status, time_)
values(1, 'START', systimestamp)

1 rows affected

select * from calc

insert into hold_(job_id, status, time_)
values(1, 'END', systimestamp)

1 rows affected

select * from calc

fiddle