.::: Enable Change Data Capture (CDC) Oracle Database using TRIGGER with session_user & client_ip :::.

 

Berikut adalah langkah-langkah untuk mengaktifkan Change Data Capture (CDC) di Oracle menggunakan Trigger, termasuk contoh tabel, isi tabel, dan implementasi trigger untuk melacak perubahan data:

1. Membuat Tabel Sumber
Tabel ini adalah tabel utama tempat data disimpan, dan perubahan pada tabel ini akan dilacak.

CREATE TABLE EMPLOYEES (
    EMP_ID NUMBER PRIMARY KEY,
    EMP_NAME VARCHAR2(50),
    DEPARTMENT_ID NUMBER,
    SALARY NUMBER
);
 

2. Masukkan Data Sampel ke Tabel and other priviledge

INSERT INTO EMPLOYEES VALUES (1, 'Alice', 101, 5000);
INSERT INTO EMPLOYEES VALUES (2, 'Bob', 102, 6000);
INSERT INTO EMPLOYEES VALUES (3, 'Charlie', 103, 7000);
COMMIT;

GRANT SELECT, UPDATE, INSERT,DELETE ON AISYAH.EMPLOYEES TO ADMIN;
commit;

3. Membuat Tabel Log untuk Menyimpan Perubahan
Buat tabel tambahan untuk mencatat perubahan yang dilakukan pada tabel EMPLOYEES.

CREATE TABLE EMPLOYEES_CHANGE_LOG (
    LOG_ID NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    EMP_ID NUMBER,
    OPERATION VARCHAR2(10), -- INSERT, UPDATE, DELETE
    OLD_SALARY NUMBER,
    NEW_SALARY NUMBER,
    CHANGE_DATE TIMESTAMP DEFAULT SYSTIMESTAMP,
    SESSION_USER VARCHAR2(50),
    CLIENT_IP VARCHAR2(50)
);

4. Membuat Trigger untuk Melacak Perubahan
Trigger ini akan secara otomatis mencatat operasi INSERT, UPDATE, dan DELETE ke tabel log.

CREATE OR REPLACE TRIGGER EMPLOYEES_CDC_TRIGGER
AFTER INSERT OR UPDATE OR DELETE ON EMPLOYEES
FOR EACH ROW
DECLARE
    v_session_user VARCHAR2(50);
    v_client_ip    VARCHAR2(50);
BEGIN
    -- Ambil info user dan IP
    v_session_user := SYS_CONTEXT('USERENV', 'SESSION_USER');
    v_client_ip    := SYS_CONTEXT('USERENV', 'IP_ADDRESS');

    IF INSERTING THEN
        INSERT INTO EMPLOYEES_CHANGE_LOG (
            EMP_ID, OPERATION, OLD_SALARY, NEW_SALARY,
            CHANGE_DATE, SESSION_USER, CLIENT_IP
        )
        VALUES (
            :NEW.EMP_ID, 'INSERT', NULL, :NEW.SALARY,
            SYSTIMESTAMP, v_session_user, v_client_ip
        );

    ELSIF UPDATING THEN
        INSERT INTO EMPLOYEES_CHANGE_LOG (
            EMP_ID, OPERATION, OLD_SALARY, NEW_SALARY,
            CHANGE_DATE, SESSION_USER, CLIENT_IP
        )
        VALUES (
            :OLD.EMP_ID, 'UPDATE', :OLD.SALARY, :NEW.SALARY,
            SYSTIMESTAMP, v_session_user, v_client_ip
        );

    ELSIF DELETING THEN
        INSERT INTO EMPLOYEES_CHANGE_LOG (
            EMP_ID, OPERATION, OLD_SALARY, NEW_SALARY,
            CHANGE_DATE, SESSION_USER, CLIENT_IP
        )
        VALUES (
            :OLD.EMP_ID, 'DELETE', :OLD.SALARY, NULL,
            SYSTIMESTAMP, v_session_user, v_client_ip
        );
    END IF;
END;
/


5. Uji Trigger dengan Operasi DML
Lakukan beberapa perubahan pada tabel EMPLOYEES untuk melihat hasilnya di tabel log.

sqlplus admin/oracle@10.10.10.19:1521/tgh
sqlplus aisyah/hanin@10.10.10.19:1521/tgh

a. Operasi INSERT
Tambahkan data baru:

INSERT INTO EMPLOYEES VALUES (4, 'Diana', 104, 8000);
COMMIT;

b. Operasi UPDATE
Perbarui data karyawan:

UPDATE EMPLOYEES SET SALARY = 9000 WHERE EMP_ID = 1;
COMMIT;

c. Operasi DELETE

DELETE FROM EMPLOYEES WHERE EMP_ID = 2;
COMMIT;

6. Periksa Tabel Log
Query tabel log untuk melihat catatan perubahan:

SELECT * FROM EMPLOYEES_CHANGE_LOG;

or

select log_id,emp_id,operation,old_salary,new_salary,session_user,client_ip,change_date,(SELECT i.host_name FROM V$INSTANCE i) AS HOST_NAME,
       (SELECT i.version FROM V$INSTANCE i) AS ORACLE_VERSION,
       (SELECT v.banner FROM V$VERSION v WHERE ROWNUM = 1) AS BANNER
FROM EMPLOYEES_CHANGE_LOG;