SQL & PL/SQL

Hi everyone,

I work in a closed environment as a solo developer without a senior lead. Recently, I've been collaborating with external engineers who use Oracle Forms and traditional journaling tables (the ones generated by SQL Developer Data Modeler).

I am now building a database for a new app in Oracle APEX. Since I’m starting from scratch, I’m wondering: is this traditional journaling still the standard approach, or is there a more modern way to track DML changes and user activity in an APEX/Oracle environment?

Here is an example of a table and the trigger to populate it:

CREATE TABLE EMPLOYEES_JN (
  JN_OPERATION   CHAR(3) NOT NULL,
  JN_ORACLE_USER VARCHAR2(30) NOT NULL,
  JN_DATETIME    DATE NOT NULL,
  ID             NUMBER,
  NAME           VARCHAR2(100)
);

CREATE OR REPLACE TRIGGER EMPLOYEES_JN_TRG 
AFTER INSERT OR UPDATE OR DELETE ON EMPLOYEES FOR EACH ROW 
DECLARE 
rec EMPLOYEES_JN%ROWTYPE; 
blank EMPLOYEES_JN%ROWTYPE; 
BEGIN 
rec := blank; 

IF INSERTING OR UPDATING THEN 
rec.id := :NEW.id; 
rec.name := :NEW.name; 
rec.JN_DATETIME := SYSDATE; 
rec.JN_ORACLE_USER := SYS_CONTEXT ('USERENV', 'SESSION_USER'); 
rec.JN_OPERATION := CASE WHEN INSERTING THEN 'INS' ELSE 'UPD' END; 

ELSIF DELETING THEN 
rec.id := :OLD.id; 
rec.name := :OLD.name; 
rec.JN_DATETIME := SYSDATE; 
rec.JN_ORACLE_USER := SYS_CONTEXT ('USERENV', 'SESSION_USER'); 
rec.JN_OPERATION := 'DEL'; 
END IF; 

INSERT INTO EMPLOYEES_JN VALUES rec; 
END; 
/ 
SHOW ERRORS;

I’d appreciate any insights on best practices for a fresh implementation.

Thank you!