Environment Oracle 19c (also 12c)
Here's my code:
CREATE OR REPLACE TRIGGER ClientTime
AFTER INSERT OR UPDATE OR DELETE ON ClientTime
FOR EACH ROW
DECLARE
v_pk_json CLOB;
v_changed CLOB;
v_old_part VARCHAR2(32767);
v_new_part VARCHAR2(32767);
v_first_old BOOLEAN;
v_first_new BOOLEAN;
BEGIN
-- build pk json
v_pk_json := '{' ||
CASE
WHEN INSERTING OR UPDATING THEN '"id":' || NVL(TO_CHAR(:NEW.ID),'null')
ELSE '"id":' || NVL(TO_CHAR(:OLD.ID),'null')
END ||
'}';
-- build changed columns json
IF INSERTING THEN
v_changed := '{' ||
'"id":' || NVL(TO_CHAR(:NEW.ID), 'null') || ',' ||
'"client":' || NVL(TO_CHAR(:NEW.CLIENT), 'null') || ',' ||
'"timestamp":"' || NVL(TO_CHAR(:NEW.DATETIME, 'YYYY-MM-DD HH24:MI:SS'), 'null') || '"' ||
'}';
ELSIF DELETING THEN
v_changed := '{' ||
'"id":' || NVL(TO_CHAR(:OLD.ID), 'null') || ',' ||
'"client":' || NVL(TO_CHAR(:OLD.CLIENT), 'null') || ',' ||
'"timestamp":"' || NVL(TO_CHAR(:OLD.DATETIME, 'YYYY-MM-DD HH24:MI:SS'), 'null') || '"' ||
'}';
ELSE -- UPDATE: only changed columns
v_old_part := '{';
v_new_part := '{';
v_first_old := TRUE;
v_first_new := TRUE;
IF NVL(:OLD.CLIENT, -999999) <> NVL(:NEW.CLIENT, -999999) THEN
v_old_part := v_old_part || '"client":' || NVL(TO_CHAR(:OLD.CLIENT),'null');
v_new_part := v_new_part || '"client":' || NVL(TO_CHAR(:NEW.CLIENT),'null');
v_first_old := FALSE;
v_first_new := FALSE;
END IF;
IF NVL(:OLD.DATETIME, TO_DATE('1900-01-01','YYYY-MM-DD')) <> NVL(:NEW.DATETIME, TO_DATE('1900-01-01','YYYY-MM-DD')) THEN
IF NOT v_first_old THEN
v_old_part := v_old_part || ',';
v_new_part := v_new_part || ',';
END IF;
v_old_part := v_old_part || '"timestamp":"' || NVL(TO_CHAR(:OLD.DATETIME,'YYYY-MM-DD HH24:MI:SS'),'null') || '"';
v_new_part := v_new_part || '"timestamp":"' || NVL(TO_CHAR(:NEW.DATETIME,'YYYY-MM-DD HH24:MI:SS'),'null') || '"';
END IF;
v_old_part := v_old_part || '}';
v_new_part := v_new_part || '}';
v_changed := '{"old":' || v_old_part || ',"new":' || v_new_part || '}';
END IF;
-- insert into audit_log
INSERT INTO audit_log(change_txid, scn, change_ts, db_user, session_id, client_host, module_name,
schema_name, table_name, op_type, pk_json, changed_columns)
VALUES (DBMS_TRANSACTION.LOCAL_TRANSACTION_ID, ORA_ROWSCN, SYSTIMESTAMP,
SYS_CONTEXT('USERENV','SESSION_USER'), SYS_CONTEXT('USERENV','SESSIONID'),
SYS_CONTEXT('USERENV','HOST'), SYS_CONTEXT('USERENV','MODULE'),
'DBNAME','CLIENTTIME',
CASE WHEN INSERTING THEN 'I' WHEN UPDATING THEN 'U' ELSE 'D' END,
v_pk_json, v_changed);
END;
Disclaimer: I changed the columns names for security reasons. Datetime is not actually the column name, so that is not an oracle keyword in my use case.
The error (removed the lines, because I can't find any reason why that exact line has an error):
PL/SQL: SQL Statement ignored
PL/SQL: ORA-00920: invalid relational operator
I have already tried changing these variables without success (these were the lines highlighted to change according to Oracle):
SYS_CONTEXT('USERENV', 'SESSION_USER')-> to simplyUSERSYS_CONTEXT('USERENV', 'SESSIONID')->SESSIONIDSYS_CONTEXT('USERENV', 'HOST')->HOST
The other line was the final end if;.
Several AI engines mentioned to remove the case statement CASE WHEN INSERTING THEN 'I' WHEN UPDATING THEN 'U' ELSE 'D' END, and place this logic in the code before the entire insert statement, without any success either.
I think something structural is wrong, but I can't find it.
Update: I changed the insert statement to:
INSERT INTO audit_log(change_txid, scn, change_ts, db_user, session_id, client_host, module_name,
schema_name, table_name, op_type, pk_json, changed_columns)
VALUES (NULL, NULL, SYSDATE, USER, NULL, NULL,NULL, 'DBNAME','CLIENTTIME', 'I', v_pk_json, v_changed);
And now the trigger compiled without errors.