Update and Insert based on Oracle APEX item value

Question

I have APEX page that does updates to a table(SAMPLE_TAB). This table has multiple columns(ID, BADGEID, USERID, NAME, CITY, ZIP, EFTDT, TERMDT, STATUS) ID is distinct number for the records.

BADGEID and USERID are related and if either one record is changed it should automatically create New record and expire old record.

If there is a change in NAME, CITY or ZIP then record update should do.

For Example: See below table

ID BADGEID USERID NAME CITY ZIP EFFDT TERMDT STATUS

1 2345 54623 XYZ NY 00000 8/31/2020 12/31/2199 Active

2 5678 90876 DEF NJ 00001 8/31/2020 12/31/2199 Active

If BADGEID 2345 is changed to 2354 for same USERID(54623) On SEP 1 2020 Then there should be a New Record As below

3 2354 54623 XYZ NY 00000 09/01/2020 12/31/2199 ACTIVE

And update old record to below

1 2345 54623 XYZ NY 00000 8/31/2020 09/01/2020 INACTIVE

I tried below PL/SQL code in update processing but getting error

DECLARE ID_PARM = (SELECT BADGEID FROM SAMPLE_TAB WHERE ID = :P1_ID)

BEGIN IF (:P1_BADGEID = ID_PARM) THEN UPDATE ....... ELSE INSERT and UPDATE

Answer 1

I believe you are looking for something like this. Fill in the "..." with additional columns/page items/conditions:

DECLARE
  l_old_row sample_tab%ROWTYPE;
BEGIN
  SELECT * INTO l_old_row FROM sample_tab WHERE id = :P1_ID;
  IF ((l_old_row.badge_id != :P1_BADGE_ID) OR (l_old_row.user_id != :P1_USERID))
  THEN
    UPDATE sample_tab SET status = 'INACTIVE' WHERE id = :P1_ID;
    INSERT INTO sample_tab (badgeid,userid,...) VALUES (:P1_BADGE_ID, :P1_USERID,...);
  ELSIF ((l_old_row.name != :P1_NAME) OR (l_old_row.city != :P1_CITY) OR ...) THEN
    UPDATE sample_tab
       SET name = :P1_NAME,
           city = :P1_CITY,
           ...
     WHERE id = :P1_ID;
  END IF;
END;