.::: How to enable CDC (Change Data Capture) in SQL Server Using Trigger :::.

 

Berikut adalah implementasi Change Data Capture (CDC) menggunakan trigger di SQL Server, termasuk contoh tabel, data awal, serta operasi INSERT, UPDATE, dan DELETE.

correlation  with http://teguhth.blogspot.com/2022/11/how-to-enable-cdc-change-data-capture.html
 
1. Membuat Tabel Utama
Misalkan tabel utama bernama Products:

CREATE TABLE Products (
    ProductID INT IDENTITY PRIMARY KEY,
    ProductName NVARCHAR(100),
    Price DECIMAL(10, 2),
    Stock INT
);
 

2. Membuat Tabel Audit
Tabel audit (cdc_trigger_log) akan mencatat semua perubahan pada tabel Products:

CREATE TABLE cdc_trigger_log (
    AuditID INT IDENTITY PRIMARY KEY,
    EventTime DATETIME DEFAULT GETDATE(), -- Waktu perubahan
    EventType NVARCHAR(10),              -- Jenis perubahan: INSERT, UPDATE, DELETE
    ProductID INT,                       -- ID produk yang berubah
    OldData NVARCHAR(MAX),               -- Data sebelum perubahan
    NewData NVARCHAR(MAX),               -- Data setelah perubahan
    UserName NVARCHAR(50)                -- Pengguna yang melakukan perubahan
);

3. Membuat Trigger
Trigger ini mencatat semua perubahan (INSERT, UPDATE, DELETE) ke tabel cdc_trigger_log:

CREATE TRIGGER trg_cdc_Products
ON Products
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;

    -- Log INSERT
    IF EXISTS (SELECT 1 FROM inserted)
    BEGIN
        INSERT INTO cdc_trigger_log (EventType, ProductID, NewData, UserName)
        SELECT
            'INSERT',
            ProductID,
            CONCAT('ProductName=', ProductName, ', Price=', Price, ', Stock=', Stock),
            USER_NAME()
        FROM inserted;
    END;

    -- Log DELETE
    IF EXISTS (SELECT 1 FROM deleted)
    BEGIN
        INSERT INTO cdc_trigger_log (EventType, ProductID, OldData, UserName)
        SELECT
            'DELETE',
            ProductID,
            CONCAT('ProductName=', ProductName, ', Price=', Price, ', Stock=', Stock),
            USER_NAME()
        FROM deleted;
    END;

    -- Log UPDATE
    IF EXISTS (SELECT 1 FROM inserted) AND EXISTS (SELECT 1 FROM deleted)
    BEGIN
        INSERT INTO cdc_trigger_log (EventType, ProductID, OldData, NewData, UserName)
        SELECT
            'UPDATE',
            i.ProductID,
            CONCAT('ProductName=', d.ProductName, ', Price=', d.Price, ', Stock=', d.Stock),
            CONCAT('ProductName=', i.ProductName, ', Price=', i.Price, ', Stock=', i.Stock),
            USER_NAME()
        FROM inserted i
        INNER JOIN deleted d ON i.ProductID = d.ProductID;
    END;
END;

4. Contoh Data Awal
Isi awal tabel Products:

ProductID    ProductName    Price    Stock
1    Laptop    15000.00    10
2    Smartphone    8000.00    25
3    Tablet    6000.00    15

INSERT INTO Products (ProductName, Price, Stock)
VALUES
('Laptop', 15000.00, 10),
('Smartphone', 8000.00, 25),
('Tablet', 6000.00, 15);
 

5. Contoh Operasi: INSERT, UPDATE, DELETE
5.1 INSERT
Menambahkan produk baru:

INSERT INTO Products (ProductName, Price, Stock)
VALUES ('Smartwatch', 2000.00, 30);

5.2 UPDATE
Mengubah harga dan stok ProductID = 2:

UPDATE Products
SET Price = 8500.00, Stock = 20
WHERE ProductID = 2;

5.3 DELETE
Menghapus produk ProductID = 3:

DELETE FROM Products
WHERE ProductID = 3;

6. Cek Isi Tabel cdc_trigger_log
Gunakan query berikut untuk memeriksa hasil audit:

SELECT * FROM cdc_trigger_log ORDER BY EventTime DESC;
SELECT @@servername as Servername, *,@@VERSION as SQLVersion FROM cdc_trigger_log ORDER BY EventTime DESC;
 

Keuntungan Trigger Sebagai CDC
Real-time: Data langsung dicatat saat perubahan terjadi.
Fleksibilitas: Bisa disesuaikan untuk mencatat data tertentu.
Audit Lengkap: Mendukung INSERT, UPDATE, dan DELETE.
Kekurangan
Performa: Memengaruhi performa jika tabel besar atau banyak operasi.
Pemeliharaan: Membutuhkan pemeliharaan tambahan, terutama jika banyak tabel yang menggunakan trigger.
Pendekatan ini cocok untuk kebutuhan pelacakan perubahan yang spesifik. Namun, untuk solusi yang lebih efisien dan performa tinggi, gunakan fitur bawaan seperti CDC atau Change Tracking di SQL Server.