Audit Trigger for Tracking User Updates and Deletions in table

#oracle #programming #database #datascience

Audit Trigger for Tracking User Updates and Deletions in table

This trigger audits every update or delete on the GEN_MST_USR_LST table.
It records the old data into an audit table with action type (U or D) and logs transaction details for traceability.

Summary of working of Trigger

The trigger AUD_ULT_AR_UD is designed to maintain an audit trail whenever a row in the GEN_MST_USR_LST table is updated or deleted. It fires after each row change and excludes actions performed by the system user PRG_USER.
When triggered, it first calls the gen_pkg.get_user_context procedure to fetch the current session's user, role, city, and transaction details. If no transaction detail ID (v_dtl_id) is available, it generates one using a sequence (dtl_seq.NEXTVAL) and inserts a record into the GEN_DB_TRANSACTION_LOG table, capturing information about the user, role, host machine, and module.
For a delete operation, the trigger inserts the entire old row into GEN_MST_USR_LST_AUD, marking the action as 'D' (delete) along with the new transaction detail ID.
For an update operation, it similarly inserts the old row values into the audit table but marks the action as 'U' (update). This way, the trigger ensures that all previous states of the data are preserved for auditing, enabling traceability of both user activity and data modifications in the system.
Audit Trigger for Tracking User Updates and Deletions in GEN_MST_USR_LST

Implementation of Trigger

create or replace TRIGGER AUD_ULT_AR_UD
AFTER DELETE OR UPDATE ON  GEN_MST_USR_LST
FOR EACH ROW

DECLARE
 v_user_id            gen_db_transaction_log.username%TYPE DEFAULT 'TC01052';
 v_trns_id            gen_db_transaction_log.transaction_id%TYPE;
 v_city               gen_db_transaction_log.city%TYPE;
 v_role               gen_db_transaction_log.user_role%TYPE;
 v_transaction_name   gen_db_transaction_log.transaction_id%TYPE;
 v_dtl_id             gen_db_transaction_log.dtl_id%TYPE;
 v_sessionid          gen_db_transaction_log.transaction_id%TYPE;

BEGIN
IF USER <> 'PRG_USER' THEN
  gen_pkg.get_user_context ( v_user_id, v_role, v_city, v_transaction_name, v_trns_id, v_dtl_id, v_sessionid );

   IF v_dtl_id IS NULL THEN
      SELECT dtl_seq.NEXTVAL into v_dtl_id FROM DUAL;
      INSERT INTO gen_db_transaction_log ( dtl_id, username, user_role, apps_server, transaction_id, business_tans_id, created_date, created_by)
      VALUES ( v_dtl_id, USER, SYS_CONTEXT('USERENV', 'OS_USER'), SUBSTR(SYS_CONTEXT('USERENV', 'HOST'), 1, 50), SYS_CONTEXT('USERENV', 'MODULE'), 555, SYSDATE, SYS_CONTEXT('USERENV', 'OS_USER'));
   END IF;
   IF DELETING THEN
      INSERT INTO GEN_MST_USR_LST_AUD
      (ULT_ID, OWNR_CARR_CODE, USR_NAME, FIRST_NAME, LAST_NAME, USR_TYP, USR_DSGN, SALUTATION, DOB, MAX_SESNS, CRNT_SESNS, LANGUAGE, APLN_ADMIN_IND, SYS_ADMIN_IND, LAST_LOGIN_DATT, LAST_LOGOUT_DATT, GRACE_LOGINS_RMNG, ONLINE_IND, USR_PWD, USR_PWD_CODE, USR_PWD_CODE_VAL, REG_MODE, ACNT_ENABLED, PWD_EXPRY_DT, PWD_EXPRY_ALRT_DT, TRACE_ENABLED, STAFF_ID, DFLT_APLN_CLASSIFIER, MGR_ID, ADRS_ID, VALID_UNTIL, INACTIVE, DTL_ID, CREATED_DATE, CREATED_BY, MODIFIED_DATE, MODIFIED_BY, DESKTOP_ALOWD_IND, USR_ROLE, LOCKED_DATE, INVLD_ATMPTS, OTP, OTP_VALID_UNTIL, USR_ACT_KEY, USR_ACT_KEY_VLDTY_DAT, EMAIL_VERIFIED, USR_BCRPT_PWD, IDP_NUMBER, ACTION, NEW_DTL_ID )
      VALUES
      (:OLD.ULT_ID, :OLD.OWNR_CARR_CODE, :OLD.USR_NAME, :OLD.FIRST_NAME, :OLD.LAST_NAME, :OLD.USR_TYP, :OLD.USR_DSGN, :OLD.SALUTATION, :OLD.DOB, :OLD.MAX_SESNS, :OLD.CRNT_SESNS, :OLD.LANGUAGE, :OLD.APLN_ADMIN_IND, :OLD.SYS_ADMIN_IND, :OLD.LAST_LOGIN_DATT, :OLD.LAST_LOGOUT_DATT, :OLD.GRACE_LOGINS_RMNG, :OLD.ONLINE_IND, :OLD.USR_PWD, :OLD.USR_PWD_CODE, :OLD.USR_PWD_CODE_VAL, :OLD.REG_MODE, :OLD.ACNT_ENABLED, :OLD.PWD_EXPRY_DT, :OLD.PWD_EXPRY_ALRT_DT, :OLD.TRACE_ENABLED, :OLD.STAFF_ID, :OLD.DFLT_APLN_CLASSIFIER, :OLD.MGR_ID, :OLD.ADRS_ID, :OLD.VALID_UNTIL, :OLD.INACTIVE, :OLD.DTL_ID, :OLD.CREATED_DATE, :OLD.CREATED_BY, SYSDATE, v_user_id, :OLD.DESKTOP_ALOWD_IND, :OLD.USR_ROLE, :OLD.LOCKED_DATE, :OLD.INVLD_ATMPTS, :OLD.OTP, :OLD.OTP_VALID_UNTIL, :OLD.USR_ACT_KEY, :OLD.USR_ACT_KEY_VLDTY_DAT, :OLD.EMAIL_VERIFIED, :OLD.USR_BCRPT_PWD, :OLD.IDP_NUMBER, 'D', v_dtl_id
      );
   END IF;

   IF UPDATING THEN
      INSERT INTO GEN_MST_USR_LST_AUD
      (ULT_ID, OWNR_CARR_CODE, USR_NAME, FIRST_NAME, LAST_NAME, USR_TYP, USR_DSGN, SALUTATION, DOB, MAX_SESNS, CRNT_SESNS, LANGUAGE, APLN_ADMIN_IND, SYS_ADMIN_IND, LAST_LOGIN_DATT, LAST_LOGOUT_DATT, GRACE_LOGINS_RMNG, ONLINE_IND, USR_PWD, USR_PWD_CODE, USR_PWD_CODE_VAL, REG_MODE, ACNT_ENABLED, PWD_EXPRY_DT, PWD_EXPRY_ALRT_DT, TRACE_ENABLED, STAFF_ID, DFLT_APLN_CLASSIFIER, MGR_ID, ADRS_ID, VALID_UNTIL, INACTIVE, DTL_ID, CREATED_DATE, CREATED_BY, MODIFIED_DATE, MODIFIED_BY, DESKTOP_ALOWD_IND, USR_ROLE, LOCKED_DATE, INVLD_ATMPTS, OTP, OTP_VALID_UNTIL,  USR_ACT_KEY, USR_ACT_KEY_VLDTY_DAT,EMAIL_VERIFIED, USR_BCRPT_PWD, IDP_NUMBER, ACTION, NEW_DTL_ID )
      VALUES
      (:OLD.ULT_ID, :OLD.OWNR_CARR_CODE, :OLD.USR_NAME, :OLD.FIRST_NAME, :OLD.LAST_NAME, :OLD.USR_TYP, :OLD.USR_DSGN, :OLD.SALUTATION, :OLD.DOB, :OLD.MAX_SESNS, :OLD.CRNT_SESNS, :OLD.LANGUAGE, :OLD.APLN_ADMIN_IND, :OLD.SYS_ADMIN_IND, :OLD.LAST_LOGIN_DATT, :OLD.LAST_LOGOUT_DATT, :OLD.GRACE_LOGINS_RMNG, :OLD.ONLINE_IND, :OLD.USR_PWD, :OLD.USR_PWD_CODE, :OLD.USR_PWD_CODE_VAL, :OLD.REG_MODE, :OLD.ACNT_ENABLED, :OLD.PWD_EXPRY_DT, :OLD.PWD_EXPRY_ALRT_DT, :OLD.TRACE_ENABLED, :OLD.STAFF_ID, :OLD.DFLT_APLN_CLASSIFIER, :OLD.MGR_ID, :OLD.ADRS_ID, :OLD.VALID_UNTIL, :OLD.INACTIVE, :OLD.DTL_ID, :OLD.CREATED_DATE, :OLD.CREATED_BY, :OLD.MODIFIED_DATE, :OLD.MODIFIED_BY, :OLD.DESKTOP_ALOWD_IND, :OLD.USR_ROLE, :OLD.LOCKED_DATE, :OLD.INVLD_ATMPTS, :OLD.OTP, :OLD.OTP_VALID_UNTIL,  :OLD.USR_ACT_KEY, :OLD.USR_ACT_KEY_VLDTY_DAT,:OLD.EMAIL_VERIFIED, :OLD.USR_BCRPT_PWD, :OLD.IDP_NUMBER, 'U', v_dtl_id
      );
   END IF;
END IF;
END;
/

Here's a concise 8-line point-wise explanation:

Trigger fires AFTER DELETE or UPDATE on GEN_MST_USR_LST for each row.
Ignores actions by the system user PRG_USER.
Calls gen_pkg.get_user_context to fetch user, role, city, and transaction details.
If no transaction detail ID exists, generates one using dtl_seq.NEXTVAL and logs it in GEN_DB_TRANSACTION_LOG.
On DELETE, inserts the old row into GEN_MST_USR_LST_AUD with action 'D'.
On UPDATE, inserts the old row into GEN_MST_USR_LST_AUD with action 'U'.
Captures all column values along with timestamps and user info for auditing.
Ensures a complete history of user master changes for traceability and accountability.