.::: How to enable CDC (Change Data Capture) in PostgreSQL, EDB :::.

1. Enable WAL (Write-Ahead Logging): PostgreSQL uses Write-Ahead Logging for durability. Ensure that your PostgreSQL server is configured to use WAL.

2. Create Audit Table: Create an audit table where you will log the changes.

CREATE TABLE audit_table (
    id SERIAL PRIMARY KEY,
    table_name TEXT NOT NULL,
    action_type TEXT NOT NULL,
    old_data JSONB,
    new_data JSONB,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
3. Create Trigger Function
## Create Trigger Function
CREATE OR REPLACE FUNCTION cdc_trigger_function()
RETURNS TRIGGER AS $$
BEGIN
    IF (TG_OP = 'DELETE') THEN
        INSERT INTO audit_table (table_name, action_type, old_data)
        VALUES (TG_TABLE_NAME, 'DELETE', row_to_json(OLD));
        RETURN OLD;
    ELSIF (TG_OP = 'INSERT') THEN
        INSERT INTO audit_table (table_name, action_type, new_data)
        VALUES (TG_TABLE_NAME, 'INSERT', row_to_json(NEW));
        RETURN NEW;
    ELSIF (TG_OP = 'UPDATE') THEN
        INSERT INTO audit_table (table_name, action_type, old_data, new_data)
        VALUES (TG_TABLE_NAME, 'UPDATE', row_to_json(OLD), row_to_json(NEW));
        RETURN NEW;
    END IF;
END;
$$ LANGUAGE plpgsql;

4. Create Trigger Function
## Create Trigger

CREATE TRIGGER cdc_trigger
AFTER INSERT OR UPDATE OR DELETE ON pembelian
FOR EACH ROW
EXECUTE FUNCTION cdc_trigger_function();

5. Test Insert, update & Delete
# 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';

6. check result in table cdc

select * from audit_table;
select * from teguhth.public.audit_table;