.::: How to enable CDC (Change Data Capture) in MariaDB, MySQL, MarinaDB in Table :::.

Enable Binary Logging

1. check table for sample

SELECT *,@@hostname,@@version FROM teguhth.pembelian
 

2. Create table for cdc

# create table

CREATE TABLE audit_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    table_name VARCHAR(100),
    action_type ENUM('INSERT', 'UPDATE', 'DELETE'),
    old_data JSON,
    new_data JSON,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

SELECT *,@@hostname,@@version FROM audit_table;
 

3. create trigger Change Data Capture for insert, update & delete
# cdc insert
DELIMITER //

CREATE TRIGGER cdc_trigger_insert AFTER INSERT ON teguhth.pembelian FOR EACH ROW
BEGIN
    INSERT INTO audit_table (table_name, action_type, new_data)
    VALUES ('pembelian', 'INSERT', JSON_OBJECT('KODE_PEMBELIAN', NEW.KODE_PEMBELIAN, 'KODE_BARANG', NEW.KODE_BARANG,'KODE_CUSTOMER', NEW.KODE_CUSTOMER, 'TANGGAL_PEMBELIAN', NEW.TANGGAL_PEMBELIAN,'JUMLAH_PEMBELIAN', NEW.JUMLAH_PEMBELIAN));
END //

DELIMITER ;

# cdc update

DELIMITER //

CREATE TRIGGER cdc_trigger_update
AFTER UPDATE ON pembelian
FOR EACH ROW
BEGIN
    INSERT INTO audit_table (table_name, action_type, old_data, new_data)
    VALUES ('pembelian', 'UPDATE',
            JSON_OBJECT('JUMLAH_PEMBELIAN', OLD.JUMLAH_PEMBELIAN),
            JSON_OBJECT('JUMLAH_PEMBELIAN', NEW.JUMLAH_PEMBELIAN));
END //

DELIMITER ;

# cdc delete

DELIMITER //

CREATE TRIGGER cdc_trigger_delete
AFTER DELETE ON pembelian
FOR EACH ROW
BEGIN
    INSERT INTO audit_table (table_name, action_type, old_data)
    VALUES ('pembelian', 'DELETE',
            JSON_OBJECT('KODE_PEMBELIAN', OLD.KODE_PEMBELIAN,
                        'KODE_BARANG', OLD.KODE_BARANG,
                        'KODE_CUSTOMER', OLD.KODE_CUSTOMER,
                        'TANGGAL_PEMBELIAN', OLD.TANGGAL_PEMBELIAN,
                        'JUMLAH_PEMBELIAN', OLD.JUMLAH_PEMBELIAN));
END //

DELIMITER ;
 

4. testing insert,update & delete

use teguhth

# test insert
insert into pembelian(KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E016','ELK-04','B-0002','2002-07-17',16);
insert into pembelian(KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E017','ELK-04','B-0002','2002-07-17',17);
insert into pembelian(KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E018','ELK-04','B-0002','2002-07-17',18);

# test update
UPDATE pembelian SET JUMLAH_PEMBELIAN = '22' WHERE kode_pembelian='BEL-E015';

# test delete
DELETE FROM pembelian WHERE kode_pembelian = 'BEL-E016';
 

5. check CDC Record audit_table

SELECT *,@@hostname,@@version FROM audit_table;