Trigger to update existing record when a new record inserted in to the table with the same id

Question

//For eg: //Time_Entry Table ID='SAM' Work Order='WO1234' LoginTime='MM-DD-YYHH:MMAM'; LogoutTme=NULL;

Answer 1

Presuming that "system" creates entries one-by-one, then a row-level trigger might do what you want.

Sample table and trigger:

SQL> create table test
  2    (c_user        varchar2(10),
  3     working_order varchar2(10),
  4     login_time    date,
  5     logout_time   date
  6    );

Table created.

SQL> create or replace trigger trg_bi_test
  2    before insert on test
  3    for each row
  4  begin
  5    update test set
  6      logout_time = sysdate
  7      where c_user = :new.c_user
  8        and logout_time is null;
  9  end;
 10  /

Trigger created.

Insert one row:

SQL> insert into test values ('SAM', 'WO1234', sysdate, null);

1 row created.

SAM now gets another work order:

SQL> insert into test values ('SAM', 'WO5555', sysdate, null);

1 row created.

Table contents (I'm setting date format; you don't have to do that):

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

SQL> select * from test order by login_time;

C_USER     WORKING_OR LOGIN_TIME          LOGOUT_TIME
---------- ---------- ------------------- -------------------
SAM        WO1234     19.04.2022 21:53:29 19.04.2022 21:54:50
SAM        WO5555     19.04.2022 21:54:50

SQL>

Looks OK.

---

But, if "system" creates more than a single work order, trigger will see the table mutating and raise an error:

SQL> insert into test
  2    select 'SAM', 'WO6666', sysdate, null from dual union all
  3    select 'SAM', 'WO7777', sysdate, null from dual;
insert into test
            *
ERROR at line 1:
ORA-04091: table SCOTT.TEST is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.TRG_BI_TEST", line 2
ORA-04088: error during execution of trigger 'SCOTT.TRG_BI_TEST'


SQL>

---

What should you do? Abandon the trigger idea. When "system" creates a new entry in that table, let it first log off previous work order and then insert new work order(s), i.e.

update test set logout_time = sysdate where c_user = 'SAM' and logout_time is null;
insert into test ...