Hi all,
I got a trigger as below:
create or replace
TRIGGER "T_BANK_ACCOUNTS_AUDIR"
AFTER
UPDATE OR
INSERT OR
DELETE ON BANK_ACCOUNTS_ORIGIN FOR EACH row DECLARE n_id NUMBER;
n_tx_id VARCHAR(30);
BEGIN
SELECT SQ_USER_PROFILES_LOG_ID.NEXTVAL INTO n_id FROM dual;
n_tx_id := DBMS_TRANSACTION.LOCAL_TRANSACTION_ID;
IF updating THEN
INSERT
INTO BANK_ACCOUNTS_LOG
(
SEQ_NO,
TRIGGER_TYPE,
AC_NO,
USER_CREATE,
CREATE_DATE,
DB_TX_ID
,TRANSFER_PER
)
VALUES
(
n_id,
'2',
:old.AC_NO,
USER,
SYSDATE,
n_tx_id
,:old.TRANSFER_PER
);
INSERT
INTO BANK_ACCOUNTS_LOG
(
SEQ_NO,
TRIGGER_TYPE,
AC_NO,
USER_CREATE,
CREATE_DATE,
DB_TX_ID
,TRANSFER_PER
)
VALUES
(
n_id,
'3',
:new.AC_NO,
USER,
SYSDATE,
n_tx_id
, :new.TRANSFER_PER
);
END IF;
IF inserting THEN
INSERT
INTO BANK_ACCOUNTS_LOG
(
SEQ_NO,
TRIGGER_TYPE,
AC_NO,
USER_CREATE,
CREATE_DATE,
DB_TX_ID
,TRANSFER_PER
)
VALUES
(
n_id,
'1',
:new.AC_NO,
USER,
SYSDATE,
n_tx_id
,:new.TRANSFER_PER
);
END IF;
IF deleting THEN
INSERT
INTO BANK_ACCOUNTS_LOG
(
SEQ_NO,
TRIGGER_TYPE,
AC_NO,
USER_CREATE,
CREATE_DATE,
DB_TX_ID
,TRANSFER_PER
)
VALUES
(
n_id,
'4',
:old.AC_NO,
USER,
SYSDATE,
n_tx_id
,:old.TRANSFER_PER
);
END IF;
END;
Expect that while record update happen in "BANK_ACCOUNTS_ORIGIN", the trigger will get into "updating" condition and write the old value in LOG table with type = 2 and new value in LOG table with type = 3.
But today, my user claim that she modified a record during 14:xx; and I found below:
1. The LOG table logged 4 records,
USER_CREATE CREATE_DATE TO_CHAR(CREATE_DATE, 'DD-MON-YYYY HH24:MI:SS') AC_NO TRIGGER_TYPE TRANSFER_PER DB_TX_ID
---------------------- ---------------------- ----------------------------------------------------------------- ------------ ------------------------------- ----------------------- ------------------------ -------------------
SYSTEM 09-AUG-18 09-AUG-2018 14:57:16 987-654-321-1234-0 2 90 3.34.24896
SYSTEM 09-AUG-18 09-AUG-2018 14:57:16 987-654-321-1234-0 3 70 3.34.24896
UserA 09-AUG-18 09-AUG-2018 08:57:17 987-654-321-1234-0 2 90 3.25.24893
UserA 09-AUG-18 09-AUG-2018 08:57:17 987-654-321-1234-0 3 90 3.25.24893
2. User claim that, she didn't modify any record during 08:xx
3. User claim that, she did modify the column "TRANSFER_PER" from 90 to 70 during 14:xx
Thus, the image in log table didn't match the case; May I ask is there any misunderstand I made from the trigger or any issue from the trigger please?
Please advise; Many thanks.