Trigger untuk Audit dan Logging Data - Perwira Learning Center

1. Latar Belakang

    Setelah memahami struktur dan sintaks trigger, hari ini kita akan fokus pada salah satu penggunaan trigger yang paling umum dan penting: Audit Trail (Jejak Audit).

Apa itu Audit Trail?
Audit trail adalah catatan kronologis tentang siapa, melakukan apa, kapan, dan mengapa terhadap suatu data. Ini seperti CCTV di database yang merekam semua kejadian penting.

Kenapa Audit Trail Penting?

• Keamanan: Melacak siapa yang mengubah data sensitif (gaji, data pribadi)
• Kepatuhan: Memenuhi regulasi (keuangan, kesehatan, dll)
• Forensik: Menyelidiki kejadian anomali atau kecurangan
• Recovery: Memulihkan data ke kondisi sebelumnya
• Akuntabilitas: Membuat orang bertanggung jawab atas perbuatannya

Di database plc_trigger_practice, kita punya beberapa contoh audit trail:

• log_gaji - Mencatat setiap perubahan gaji karyawan
• log_stok - Mencatat setiap perubahan stok barang
• log_peminjaman - Mencatat setiap peminjaman dan pengembalian buku

---

2. Alat dan Bahan

a. Perangkat Lunak

1. MySQL - Database dengan trigger yang sudah ada
2. Database plc_trigger_practice - Berisi tabel dan trigger audit
3. MySQL Workbench / phpMyAdmin - Untuk menjalankan query
4. VS Code - Untuk menulis dan dokumentasi

b. Perangkat Keras

1. Laptop/PC dengan spesifikasi standar

---

3. Pembahasan

3.1 Konsep Audit Trail dengan Trigger

Bagaimana Trigger Bisa Membuat Audit Trail?

text

┌──────────────┐
│   APLIKASI   │
│  (User/Sistem)│
└──────┬───────┘
       │ UPDATE karyawan
       │ SET gaji = 15.000.000
       ▼
┌────────────────────────────────────┐
│          DATABASE                   │
├────────────────────────────────────┤
│  Tabel KARYAWAN                     │
│  ┌────┬───────┬──────────┐        │
│  │ id │ nama  │ gaji     │        │
│  ├────┼───────┼──────────┤        │
│  │ 1  │ Budi  │10.000.000│──┐     │
│  └────┴───────┴──────────┘  │     │
│                              ▼     │
│  🔥 TRIGGER KARYAWAN NYALA!        │
│  ┌────────────────────────────┐    │
│  │ "Ada perubahan gaji!"      │    │
│  │ "Catat ke tabel LOG_GAJI!" │    │
│  └────────────┬───────────────┘    │
│               ▼                     │
│  Tabel LOG_GAJI                     │
│  ┌────┬───────┬──────────┬──────┐  │
│  │ id │karyawan│ gaji_lama│gaji_ │  │
│  │    │_id    │          │baru  │  │
│  ├────┼───────┼──────────┼──────┤  │
│  │ 1  │ 1     │10.000.000│15.000│  │
│  │    │       │          │.000  │  │
│  └────┴───────┴──────────┴──────┘  │
└────────────────────────────────────┘

3.2 Komponen Audit Trail yang Ideal

Sebuah audit trail yang baik biasanya mencatat:

Komponen	Deskripsi	Contoh di DB Kita
Who	Siapa yang melakukan	user di log_gaji
What	Apa yang berubah	gaji_lama, gaji_baru
When	Kapan terjadi	waktu (timestamp)
Where	Data mana yang berubah	karyawan_id, product_id
Why	Alasan perubahan (opsional)	alasan_perubahan, keterangan
Action	Aksi apa yang dilakukan	aksi di log_peminjaman

3.3 Studi Kasus 1: Audit Trail Gaji Karyawan

Mari kita bedah tabel log_gaji dan trigger yang mencatatnya:

Tabel log_gaji:

sql

CREATE TABLE log_gaji (
    id INT PRIMARY KEY AUTO_INCREMENT,
    karyawan_id INT,
    gaji_lama DECIMAL(10,2),
    gaji_baru DECIMAL(10,2),
    waktu DATETIME,
    user VARCHAR(50),
    alasan_perubahan VARCHAR(255),
    FOREIGN KEY (karyawan_id) REFERENCES karyawan(id)
);

Field	Fungsi
id	Nomor urut log
karyawan_id	Karyawan mana yang berubah (WHERE)
gaji_lama	Nilai sebelum perubahan (WHAT - before)
gaji_baru	Nilai setelah perubahan (WHAT - after)
waktu	Kapan terjadi (WHEN)
user	Siapa yang melakukan (WHO)
alasan_perubahan	Kenapa berubah (WHY)

Trigger yang mencatat:

sql

DELIMITER $$

CREATE TRIGGER catat_perubahan_gaji
AFTER UPDATE ON karyawan
FOR EACH ROW
BEGIN
    -- Catat perubahan gaji
    IF OLD.gaji != NEW.gaji THEN
        INSERT INTO log_gaji 
        (karyawan_id, gaji_lama, gaji_baru, waktu, user, alasan_perubahan)
        VALUES 
        (NEW.id, OLD.gaji, NEW.gaji, NOW(), USER(), 'Update gaji karyawan');
    END IF;
    
    -- Catat perubahan jabatan (promosi/demosi)
    IF OLD.jabatan != NEW.jabatan THEN
        INSERT INTO log_gaji 
        (karyawan_id, gaji_lama, gaji_baru, waktu, user, alasan_perubahan)
        VALUES 
        (NEW.id, OLD.gaji, NEW.gaji, NOW(), USER(), 
         CONCAT('Perubahan jabatan dari ', OLD.jabatan, ' ke ', NEW.jabatan));
    END IF;
END$$

DELIMITER ;

Cara Kerjanya:

1. Ada aplikasi yang UPDATE tabel karyawan
2. Trigger AFTER UPDATE otomatis jalan
3. Trigger cek: apakah gaji berubah? (OLD.gaji != NEW.gaji)
4. Jika berubah, INSERT ke log_gaji dengan data lengkap
5. User yang melakukan perubahan diambil dari USER() (fungsi MySQL)

Demo Sederhana:

sql

-- Lihat data karyawan sebelum diubah
SELECT * FROM karyawan WHERE id = 1;

-- Update gaji karyawan
UPDATE karyawan 
SET gaji = 9500000 
WHERE id = 1;

-- Lihat log gaji (otomatis tercatat!)
SELECT * FROM log_gaji WHERE karyawan_id = 1;

-- Hasil: Ada 1 baris baru dengan gaji_lama=8500000, gaji_baru=9500000

3.4 Studi Kasus 2: Audit Trail Stok Barang

Tabel log_stok:

sql

CREATE TABLE log_stok (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT,
    stok_sebelum INT,
    stok_sesudah INT,
    perubahan INT,
    keterangan VARCHAR(255),
    waktu TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id)
);

Field	Fungsi
product_id	Produk mana yang berubah
stok_sebelum	Stok sebelum perubahan
stok_sesudah	Stok setelah perubahan
perubahan	Selisih (bisa negatif atau positif)
keterangan	Kenapa stok berubah
waktu	Kapan terjadi

Trigger yang mencatat:

sql

DELIMITER $$

CREATE TRIGGER kurangi_stok_saat_order
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    DECLARE stok_lama INT;
    
    -- Ambil stok lama
    SELECT stok INTO stok_lama
    FROM products
    WHERE id = NEW.product_id;
    
    -- Update stok produk
    UPDATE products 
    SET stok = stok - NEW.jumlah,
        terjual = terjual + NEW.jumlah
    WHERE id = NEW.product_id;
    
    -- Catat di log stok
    INSERT INTO log_stok (product_id, stok_sebelum, stok_sesudah, perubahan, keterangan)
    VALUES (
        NEW.product_id,
        stok_lama,
        stok_lama - NEW.jumlah,
        -NEW.jumlah,
        CONCAT('Pengurangan stok karena order #', NEW.id, ' oleh ', NEW.customer_name)
    );
END$$

DELIMITER ;

Cara Kerjanya:

1. Ada order baru masuk (INSERT ke orders)
2. Trigger AFTER INSERT otomatis jalan
3. Ambil stok lama produk
4. Kurangi stok di tabel products
5. Catat perubahan ke log_stok

Demo Sederhana:

sql

-- Lihat stok laptop sebelum order
SELECT * FROM products WHERE id = 1;

-- Insert order baru
INSERT INTO orders (product_id, jumlah, customer_name) 
VALUES (1, 2, 'Andi Pratama');

-- Lihat log stok (otomatis tercatat!)
SELECT * FROM log_stok WHERE product_id = 1;

-- Hasil: stok_sebelum=10, stok_sesudah=8, perubahan=-2
-- Keterangan: "Pengurangan stok karena order #1 oleh Andi Pratama"

3.5 Studi Kasus 3: Audit Trail Peminjaman Buku

Tabel log_peminjaman:

sql

CREATE TABLE log_peminjaman (
    id INT PRIMARY KEY AUTO_INCREMENT,
    peminjaman_id INT,
    buku_id INT,
    anggota VARCHAR(100),
    aksi VARCHAR(50),
    tgl_aksi TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    keterangan TEXT
);

Field	Fungsi
peminjaman_id	ID transaksi peminjaman
buku_id	Buku mana yang dipinjam/dikembalikan
anggota	Siapa yang meminjam
aksi	'PINJAM' atau 'KEMBALI'
tgl_aksi	Kapan aksi terjadi
keterangan	Detail tambahan

Trigger untuk Peminjaman:

sql

DELIMITER $$

CREATE TRIGGER kurangi_stok_buku_saat_pinjam
AFTER INSERT ON peminjaman
FOR EACH ROW
BEGIN
    -- Update stok buku
    UPDATE buku 
    SET stok = stok - 1,
        dipinjam = dipinjam + 1
    WHERE id = NEW.buku_id;
    
    -- Catat log peminjaman
    INSERT INTO log_peminjaman (peminjaman_id, buku_id, anggota, aksi, keterangan)
    VALUES (
        NEW.id,
        NEW.buku_id,
        NEW.anggota,
        'PINJAM',
        CONCAT('Peminjaman buku oleh ', NEW.anggota, ' pada ', NEW.tgl_pinjam)
    );
END$$

DELIMITER ;

Trigger untuk Pengembalian:

sql

DELIMITER $$

CREATE TRIGGER tambah_stok_buku_saat_kembali
AFTER UPDATE ON peminjaman
FOR EACH ROW
BEGIN
    IF NEW.status = 'kembali' AND OLD.status = 'dipinjam' THEN
        -- Update stok buku
        UPDATE buku 
        SET stok = stok + 1,
            dipinjam = dipinjam - 1
        WHERE id = NEW.buku_id;
        
        -- Catat log pengembalian
        INSERT INTO log_peminjaman (peminjaman_id, buku_id, anggota, aksi, keterangan)
        VALUES (
            NEW.id,
            NEW.buku_id,
            NEW.anggota,
            'KEMBALI',
            CONCAT('Pengembalian buku oleh ', NEW.anggota, ' pada ', NEW.tgl_kembali)
        );
    END IF;
END$$

DELIMITER ;

Demo Lengkap:

sql

-- 1. Lihat stok buku "Pemrograman Web dengan PHP"
SELECT * FROM buku WHERE id = 1;

-- 2. Andi pinjam buku
INSERT INTO peminjaman (buku_id, anggota, tgl_pinjam) 
VALUES (1, 'Andi Pratama', CURDATE());

-- 3. Lihat log peminjaman
SELECT * FROM log_peminjaman WHERE buku_id = 1;
-- Hasil: aksi = 'PINJAM'

-- 4. Andi kembalikan buku
UPDATE peminjaman 
SET status = 'kembali' 
WHERE id = LAST_INSERT_ID();

-- 5. Lihat log lagi
SELECT * FROM log_peminjaman WHERE buku_id = 1;
-- Hasil: Ada 2 baris - PINJAM dan KEMBALI

3.6 Fungsi USER() untuk Mencatat "Siapa"

MySQL punya fungsi USER() yang mengembalikan user yang sedang login ke database.

Contoh:

sql

SELECT USER(); -- 'root@localhost'

Pemanfaatan di trigger:

sql

INSERT INTO log_gaji (user, ...) 
VALUES (USER(), ...);

Catatan:

• Di aplikasi nyata, biasanya kita login ke database dengan satu user aplikasi
• Untuk mencatat user aplikasi yang sebenarnya, perlu pendekatan lain (misal: menyimpan user di variable session)

3.7 Membaca Data Audit Trail

Query untuk melihat riwayat perubahan gaji karyawan:

sql

SELECT 
    k.nama AS 'Nama Karyawan',
    k.jabatan AS 'Jabatan',
    lg.gaji_lama AS 'Gaji Lama',
    lg.gaji_baru AS 'Gaji Baru',
    lg.waktu AS 'Waktu Perubahan',
    lg.user AS 'Diubah Oleh',
    lg.alasan_perubahan AS 'Alasan'
FROM log_gaji lg
JOIN karyawan k ON lg.karyawan_id = k.id
ORDER BY lg.waktu DESC;

Query untuk melihat riwayat stok produk:

sql

SELECT 
    p.nama AS 'Nama Produk',
    ls.stok_sebelum AS 'Stok Sebelum',
    ls.stok_sesudah AS 'Stok Sesudah',
    ls.perubahan AS 'Perubahan',
    ls.keterangan AS 'Keterangan',
    ls.waktu AS 'Waktu'
FROM log_stok ls
JOIN products p ON ls.product_id = p.id
ORDER BY ls.waktu DESC;

Query untuk melihat riwayat peminjaman buku:

sql

SELECT 
    b.judul AS 'Judul Buku',
    lp.anggota AS 'Peminjam',
    lp.aksi AS 'Aksi',
    lp.tgl_aksi AS 'Waktu',
    lp.keterangan AS 'Keterangan'
FROM log_peminjaman lp
JOIN buku b ON lp.buku_id = b.id
ORDER BY lp.tgl_aksi DESC;

3.8 Tabel Ringkasan Trigger Audit di Database Kita

Nama Trigger	Tabel Utama	Tabel Audit	Event	Fungsi
catat_perubahan_gaji	karyawan	log_gaji	UPDATE	Catat perubahan gaji & jabatan
kurangi_stok_saat_order	orders	log_stok	INSERT	Catat pengurangan stok karena order
peringatan_stok_menipis	products	notifikasi	UPDATE	Catat peringatan stok menipis
kurangi_stok_buku_saat_pinjam	peminjaman	log_peminjaman	INSERT	Catat peminjaman buku
tambah_stok_buku_saat_kembali	peminjaman	log_peminjaman	UPDATE	Catat pengembalian buku

3.9 Keuntungan Audit Trail dengan Trigger

Keuntungan	Penjelasan
Otomatis	Tidak perlu kode di aplikasi, database yang catat sendiri
Konsisten	Semua perubahan tercatat, tidak ada yang terlewat
Aman	Tidak bisa diakali oleh aplikasi (trigger selalu jalan)
Detail	Bisa catat data sebelum dan sesudah
Performansi	Eksekusi di level database, cepat

3.10 Hal yang Perlu Diperhatikan

1. Jangan catat semuanya → Pilih data sensitif/ penting saja
2. Bersihkan log secara berkala → Tabel log bisa membesar
3. Pertimbangkan performa → Terlalu banyak trigger bisa memperlambat
4. Dokumentasikan → Trigger audit harus didokumentasikan

---

4. Latihan Praktikum

Exercise 1: Analisis Log Gaji

Jalankan query berikut dan analisis hasilnya:

sql

-- 1. Update gaji Budi Santoso
UPDATE karyawan SET gaji = 12000000 WHERE id = 1;

-- 2. Promosi Siti Aminah jadi Direktur
UPDATE karyawan 
SET jabatan = 'Direktur', gaji = 30000000 
WHERE id = 2;

-- 3. Lihat log_gaji
SELECT * FROM log_gaji ORDER BY waktu DESC;

Pertanyaan:

1. Berapa baris yang masuk ke log_gaji?

2. Apa perbedaan antara update biasa dan promosi?

3. Kolom alasan_perubahan berisi apa?

Exercise 2: Buat Trigger Audit Baru

Buat trigger untuk mencatat perubahan harga produk ke tabel log_harga:

Langkah 1: Buat tabel log_harga

sql

CREATE TABLE log_harga (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT,
    harga_lama DECIMAL(10,2),
    harga_baru DECIMAL(10,2),
    perubahan DECIMAL(10,2),
    waktu TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    user VARCHAR(50),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

Langkah 2: Buat trigger AFTER UPDATE di tabel products

sql

-- Tulis trigger untuk mencatat perubahan harga
-- Hint: Mirip dengan log_gaji

Exercise 3: Rekap Peminjaman per Anggota

Buat query untuk melihat riwayat peminjaman Andi Pratama:

sql

-- Tampilkan semua buku yang pernah dipinjam Andi
-- Lengkap dengan tanggal pinjam, tanggal kembali, dan status

---

5. Daftar Pustaka

1. Navicat Blog (2023). Mengimplementasikan Audit Trail Logging Menggunakan Triggers. https://www.navicat.co.id
2. PostgreSQL Wiki (2012). Audit Trigger. https://wiki.postgresql.org
3. NetWrix (n.d.). How to Create a SQL Server Audit Trigger. https://netwrix.com
4. Sekawan Media (2025). Audit Trail: Fungsi, Cara Kerja, Jenis, Contoh, dan Element Pentingnya. https://www.sekawanmedia.co.id
5. Hash Micro (2025). Mengenal Audit Trail dan Cara Kerjanya. https://www.hashmicro.com