Pertanyaan paling sering muncul saat data produksi bermasalah berbunyi sederhana: siapa mengubah nilai ini, kapan, dan menjadi apa? Jawabannya tidak bisa diandalkan dari ingatan tim, apalagi bila puluhan koneksi aplikasi menulis ke tabel yang sama sepanjang hari. Trigger AFTER level baris adalah instrumen yang tepat untuk menjawabnya — trigger tersebut menyala pada setiap tulis, melihat baris lama dan baru sekaligus, dan tidak bisa dilewati oleh aplikasi yang lupa memanggil fungsi pencatat. Chapter ini merangkai pola audit produksi yang lazim dipakai: satu tabel riwayat_perubahan berisi snapshot jsonb sebelum dan sesudah perubahan, satu fungsi trigger generik yang dipakai ulang untuk semua tabel, dan identitas pengguna aplikasi yang dibawa lewat variabel sesi agar jejak menunjukkan manusia di balik koneksi database.

Merancang Tabel Riwayat #

Satu tabel audit untuk semua tabel yang diawasi membuat desainnya sederhana: kolom nama_tabel membedakan sumber perubahan, kolom operasi menyimpan TG_OP, dan sepasang kolom jsonb menampung snapshot data_lama serta data_baru. Kolom waktu diisi now() oleh trigger — di lingkungan nyata nilainya adalah waktu transaksi, tetapi karena nilainya berbeda pada setiap eksekusi, contoh output di chapter ini sengaja tidak menampilkan kolom waktu.

sql buat-tabel-audit.sql 
-- Tabel audit generik: satu tabel untuk semua tabel yang diawasi
CREATE TABLE riwayat_perubahan (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    waktu      timestamp with time zone NOT NULL DEFAULT now(),
    nama_tabel text NOT NULL,
    operasi    text NOT NULL,
    pengguna   text NOT NULL,
    data_lama  jsonb,
    data_baru  jsonb
);

CREATE TABLE produk (
    id    integer PRIMARY KEY,
    nama  text NOT NULL,
    harga integer NOT NULL,
    stok  integer NOT NULL DEFAULT 0
);

CREATE TABLE pelanggan (
    id    integer PRIMARY KEY,
    nama  text NOT NULL,
    kota  text NOT NULL,
    poin  integer NOT NULL DEFAULT 0
);

-- Data awal diisi SEBELUM trigger dipasang: tidak perlu diaudit
INSERT INTO produk VALUES
    (1, 'Kopi Gayo', 85000, 10),
    (2, 'Teh Melati', 40000, 5);

INSERT INTO pelanggan VALUES
    (1, 'Budi', 'Jakarta', 120),
    (2, 'Siti', 'Bandung', 85);

-- Satu fungsi audit generik untuk semua tabel
CREATE FUNCTION catat_riwayat() RETURNS trigger AS $$
BEGIN
    INSERT INTO riwayat_perubahan (nama_tabel, operasi, pengguna, data_lama, data_baru)
    VALUES (
        TG_TABLE_NAME,
        TG_OP,
        coalesce(nullif(current_setting('app.user', true), ''), current_user),
        CASE WHEN TG_OP IN ('UPDATE', 'DELETE') THEN to_jsonb(OLD) END,
        CASE WHEN TG_OP IN ('INSERT', 'UPDATE') THEN to_jsonb(NEW) END
    );
    RETURN CASE WHEN TG_OP = 'DELETE' THEN OLD ELSE NEW END;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER audit_produk
AFTER INSERT OR UPDATE OR DELETE ON produk
FOR EACH ROW EXECUTE FUNCTION catat_riwayat();

CREATE TRIGGER audit_pelanggan
AFTER INSERT OR UPDATE OR DELETE ON pelanggan
FOR EACH ROW EXECUTE FUNCTION catat_riwayat();

SELECT count(*) AS baris_riwayat FROM riwayat_perubahan;

Output yang Dihasilkan:

text 
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 2
INSERT 0 2
CREATE FUNCTION
CREATE TRIGGER
CREATE TRIGGER
 baris_riwayat 
---------------
             0
(1 row)

Mari kita bedah fungsi auditnya baris per baris, karena fungsi inilah inti pola produksinya:

  1. TG_TABLE_NAME dan TG_OP: variabel bawaan PL/pgSQL yang sudah diperkenalkan di chapter Trigger Function. Nilainya diisi server — fungsi yang sama otomatis benar untuk tabel mana pun yang menempelkannya.
  2. CASE WHEN TG_OP IN ('UPDATE', 'DELETE') THEN to_jsonb(OLD): snapshot baris lama hanya relevan untuk UPDATE dan DELETE — pada INSERT, baris lama tidak ada. Fungsi to_jsonb() mengubah seluruh baris menjadi satu dokumen jsonb, sehingga skema tabel tidak perlu diketahui di awal.
  3. CASE WHEN TG_OP IN ('INSERT', 'UPDATE') THEN to_jsonb(NEW): kebalikannya — baris baru hanya ada untuk INSERT dan UPDATE, sehingga DELETE menyisakan data_baru kosong.
  4. coalesce(nullif(current_setting('app.user', true), ''), current_user): identitas pelaku. Argumen true membuat current_setting() mengembalikan NULL alih-alih error saat variabel app.user belum pernah diatur; nullif(..., '') menangani kasus variabel pernah di-RESET (nilainya menjadi string kosong), dan current_user menjadi pencadang terakhir. Kolom pengguna karena itu selalu terisi.
  5. RETURN CASE WHEN TG_OP = 'DELETE' THEN OLD ELSE NEW END: trigger AFTER tidak bisa mengubah baris, tetapi fungsi trigger tetap wajib mengembalikan baris yang tepat sesuai jenis operasinya.
  6. Dua CREATE TRIGGER dengan fungsi yang sama: bukti sifat generiknya — satu definisi, dua tabel, tanpa satu pun IF nama_tabel = ... di dalam fungsi.

Perhatikan juga urutan pembuatannya: data awal di-seed sebelum trigger dipasang, sehingga baris_riwayat bernilai 0 — keputusan sadar bahwa migrasi data bukan aktivitas yang perlu diaudit, dan latihan kecil bahwa urutan CREATE TRIGGER menentukan sejarah apa yang terekam.

Membawa Identitas Pengguna Aplikasi #

Kolom pengguna punya satu masalah desain: aplikasi biasanya terhubung sebagai satu database user yang sama untuk semua orang, sehingga current_user saja tidak membedakan Budi dari Siti. PostgreSQL menyediakan customized options — variabel sesi dengan nama ber-titik seperti app.user — yang bisa diatur kapan pun tanpa konfigurasi server. Ada satu jebakan penulisan:

sql set-app-user-salah.sql 
-- Bentuk tanpa tanda kutip: ditolak parser
SET app.user = 'budi';

Output yang Dihasilkan:

text 
psql:set-app-user-salah.sql:2: ERROR:  syntax error at or near "user"
LINE 1: SET app.user = 'budi';
                ^

Parser menolak bentuk tersebut karena user adalah kata kunci cadangan SQL — parser membacanya sebagai token khusus, bukan bagian nama variabel. Menambahkan tanda kutip ganda pada komponen namanya menyelesaikannya, dan aplikasi tinggal menjalankan SET app."user" = '...' sekali di awal sesi:

sql ubah-sebagai-budi.sql 
-- Identitas aplikasi dibawa lewat GUC kustom sesi ini
SET app."user" = 'budi';

INSERT INTO produk VALUES (3, 'Gula Aren', 55000, 8);

UPDATE produk SET harga = 95000, stok = 8 WHERE id = 1;

SELECT id, nama_tabel, operasi, pengguna, data_lama, data_baru
FROM riwayat_perubahan
ORDER BY id;

Output yang Dihasilkan:

text 
SET
INSERT 0 1
UPDATE 1
 id | nama_tabel | operasi | pengguna |                         data_lama                          |                         data_baru                         
----+------------+---------+----------+------------------------------------------------------------+-----------------------------------------------------------
  1 | produk     | INSERT  | budi     |                                                            | {"id": 3, "nama": "Gula Aren", "stok": 8, "harga": 55000}
  2 | produk     | UPDATE  | budi     | {"id": 1, "nama": "Kopi Gayo", "stok": 10, "harga": 85000} | {"id": 1, "nama": "Kopi Gayo", "stok": 8, "harga": 95000}
(2 rows)

Dua baris jejak tersebut memperlihatkan hasil pola to_jsonb(OLD)/to_jsonb(NEW): baris INSERT hanya punya data_baru, sedangkan baris UPDATE menyimpan kedua versi produk Kopi Gayo — harga 85.000 dengan stok 10 berubah menjadi 95.000 dengan stok 8. Seluruh kolom tercatat tanpa fungsi audit pernah menyebut nama satu pun kolom produk.

Pengguna aplikasi berganti cukup dengan mengatur ulang variabelnya di sesi yang sama, dan jejak penghapusan menyimpan baris yang sudah tidak ada lagi:

sql hapus-sebagai-siti.sql 
-- Pengguna berbeda pada sesi yang sama cukup mengatur ulang nilainya
SET app."user" = 'siti';

DELETE FROM produk WHERE id = 2;

UPDATE pelanggan SET poin = poin + 15 WHERE id = 1;

SELECT id, nama_tabel, operasi, pengguna, data_lama, data_baru
FROM riwayat_perubahan
ORDER BY id;

Output yang Dihasilkan:

text 
SET
DELETE 1
UPDATE 1
 id | nama_tabel | operasi | pengguna |                         data_lama                          |                         data_baru                         
----+------------+---------+----------+------------------------------------------------------------+-----------------------------------------------------------
  1 | produk     | INSERT  | budi     |                                                            | {"id": 3, "nama": "Gula Aren", "stok": 8, "harga": 55000}
  2 | produk     | UPDATE  | budi     | {"id": 1, "nama": "Kopi Gayo", "stok": 10, "harga": 85000} | {"id": 1, "nama": "Kopi Gayo", "stok": 8, "harga": 95000}
  3 | produk     | DELETE  | siti     | {"id": 2, "nama": "Teh Melati", "stok": 5, "harga": 40000} | 
  4 | pelanggan  | UPDATE  | siti     | {"id": 1, "kota": "Jakarta", "nama": "Budi", "poin": 120}  | {"id": 1, "kota": "Jakarta", "nama": "Budi", "poin": 135}
(4 rows)

Baris ketiga adalah alasan audit DELETE berharga: produk Teh Melati sudah lenyap dari tabel produk, tetapi seluruh isinya — id, nama, harga, stok — masih utuh di data_lama. Baris keempat berasal dari tabel pelanggan yang menempelkan fungsi generik yang sama; kolom nama_tabel memisahkan keduanya tanpa fungsi audit perlu tahu bedanya.

Pertanyaan yang Bisa Dijawab Jejak #

Tabel audit hanya berguna sebesar kemampuannya diinterogasi. RESET menghapus identitas aplikasi dari sesi — current_setting('app.user', true) kembali NULL, fallback current_user mengambil alih, dan jejak berikutnya tercatat atas nama postgres, pengguna database yang benar-benar menjalankan koneksi ini:

sql pengguna-tanpa-nama.sql 
-- Hapus identitas aplikasi: fallback ke current_user
RESET app."user";

INSERT INTO produk VALUES (4, 'Kopi Toraja', 120000, 6);

SELECT id, operasi, pengguna FROM riwayat_perubahan ORDER BY id;

-- Rekonstruksi kronologi harga produk dari jejak jsonb
SELECT id, operasi,
       data_lama->>'harga' AS harga_lama,
       data_baru->>'harga' AS harga_baru
FROM riwayat_perubahan
WHERE nama_tabel = 'produk'
ORDER BY id;

Output yang Dihasilkan:

text 
RESET
INSERT 0 1
 id | operasi | pengguna 
----+---------+----------
  1 | INSERT  | budi
  2 | UPDATE  | budi
  3 | DELETE  | siti
  4 | UPDATE  | siti
  5 | INSERT  | postgres
(5 rows)

 id | operasi | harga_lama | harga_baru 
----+---------+------------+------------
  1 | INSERT  |            | 55000
  2 | UPDATE  | 85000      | 95000
  3 | DELETE  | 40000      | 
  5 | INSERT  |            | 120000
(4 rows)

Query kedua adalah rekonstruksi kronologi: operator ->> mengambil nilai harga dari kedua snapshot, dan hasilnya membaca seperti kisah harga produk — Gula Aren masuk di 55.000, Kopi Gayo naik dari 85.000 ke 95.000, Teh Melati terhapus di harga 40.000, Kopi Toraja masuk di 120.000. Karena id riwayat berurutan sesuai waktu kejadian, ORDER BY id adalah urutan kronologisnya.

Agregasi dan pencarian jsonb melengkapi kotak pertanyaannya:

sql kueri-jejak.sql 
-- Siapa mengerjakan apa, di tabel mana
SELECT pengguna, nama_tabel, operasi, count(*) AS jumlah
FROM riwayat_perubahan
GROUP BY pengguna, nama_tabel, operasi
ORDER BY pengguna, nama_tabel, operasi;

-- Kronologi per objek yang tersentuh, lintas jenis operasi
SELECT id, pengguna, operasi,
       coalesce(data_lama->>'nama', data_baru->>'nama') AS objek
FROM riwayat_perubahan
WHERE nama_tabel = 'produk'
ORDER BY id;

-- Cari jejak yang menyentuh data ber-kolom "poin", tanpa tahu tabelnya
SELECT id, nama_tabel, operasi
FROM riwayat_perubahan
WHERE data_lama ? 'poin' OR data_baru ? 'poin'
ORDER BY id;

Output yang Dihasilkan:

text 
 pengguna | nama_tabel | operasi | jumlah 
----------+------------+---------+--------
 budi     | produk     | INSERT  |      1
 budi     | produk     | UPDATE  |      1
 postgres | produk     | INSERT  |      1
 siti     | pelanggan  | UPDATE  |      1
 siti     | produk     | DELETE  |      1
(5 rows)

 id | pengguna | operasi |    objek    
----+----------+---------+-------------
  1 | budi     | INSERT  | Gula Aren
  2 | budi     | UPDATE  | Kopi Gayo
  3 | siti     | DELETE  | Teh Melati
  5 | postgres | INSERT  | Kopi Toraja
(4 rows)

 id | nama_tabel | operasi 
----+------------+---------
  4 | pelanggan  | UPDATE
(1 row)

Query pertama merangkum aktivitas per pengguna — dasar laporan kepatuhan yang lazim diminta auditor. Query kedua merangkai kronologi per objek dengan coalesce(data_lama->>'nama', data_baru->>'nama'): nama objek diambil dari snapshot mana pun yang tersedia, sehingga INSERT, UPDATE, dan DELETE bisa berbagi satu kolom hasil. Query ketiga memakai operator keanggotaan kunci ? dari chapter Operator JSON dan JSONB untuk mencari jejak yang menyentuh data bertipe tertentu — “tampilkan semua perubahan atas data yang punya kolom poin” — tanpa perlu tahu tabel sumbernya. Seluruh operator jsonb yang sudah Anda kuasai bisa dipakai di atas kolom audit ini.

Biaya yang Harus Dihargai #

Kemampuan audit ini tidak gratis. Setiap tulis kini menjadi dua tulis — baris asli plus baris audit dengan dua konversi to_jsonb — sehingga beban tulis naik dan penyimpanan tumbuh lebih cepat dari tabel aslinya; tabel yang sibuk di-UPDATE menghasilkan dua dokumen jsonb per perubahan. Beberapa kebiasaan menjaga biaya ini tetap rasional:

  • Pasang trigger hanya pada tabel yang memang perlu diaudit, bukan seluruh skema.
  • Tambahkan klausa WHEN pada trigger agar hanya perubahan kolom penting yang dicatat, bukan setiap UPDATE apa pun isinya.
  • Untuk operasi massal, pertimbangkan trigger level pernyataan dengan transition table (chapter Row-Level vs Statement-Level) agar satu baris audit mewakili seluruh batch.
  • Arsipkan atau partisi riwayat_perubahan berdasarkan waktu — jejak lama jarang dibaca tetapi terus memakan penyimpanan bila dibiarkan menumpuk.

Dengan ini pola trigger di section Trigger dan Event sudah menutup tiga kebutuhan: validasi yang bisa ditunda, penjagaan skema lewat event trigger, dan jejak perubahan data. Sebelum Anda menjadikan trigger jawaban untuk semua masalah, chapter penutup section ini mengajak kita berhitung jujur: biaya per baris, debugging yang sulit, efek berantai antar-trigger, dan kapan constraint atau lapisan aplikasi seharusnya menang — semuanya di Keterbatasan Trigger.

Referensi #

Berkas Sumber

Berkas pendukung halaman ini dapat diunduh dan dijalankan langsung sesuai penjelasan di atas.

buat-tabel-audit.sql

Unduh berkas
sql 
-- Tabel audit generik: satu tabel untuk semua tabel yang diawasi
CREATE TABLE riwayat_perubahan (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    waktu      timestamp with time zone NOT NULL DEFAULT now(),
    nama_tabel text NOT NULL,
    operasi    text NOT NULL,
    pengguna   text NOT NULL,
    data_lama  jsonb,
    data_baru  jsonb
);

CREATE TABLE produk (
    id    integer PRIMARY KEY,
    nama  text NOT NULL,
    harga integer NOT NULL,
    stok  integer NOT NULL DEFAULT 0
);

CREATE TABLE pelanggan (
    id    integer PRIMARY KEY,
    nama  text NOT NULL,
    kota  text NOT NULL,
    poin  integer NOT NULL DEFAULT 0
);

-- Data awal diisi SEBELUM trigger dipasang: tidak perlu diaudit
INSERT INTO produk VALUES
    (1, 'Kopi Gayo', 85000, 10),
    (2, 'Teh Melati', 40000, 5);

INSERT INTO pelanggan VALUES
    (1, 'Budi', 'Jakarta', 120),
    (2, 'Siti', 'Bandung', 85);

-- Satu fungsi audit generik untuk semua tabel
CREATE FUNCTION catat_riwayat() RETURNS trigger AS $$
BEGIN
    INSERT INTO riwayat_perubahan (nama_tabel, operasi, pengguna, data_lama, data_baru)
    VALUES (
        TG_TABLE_NAME,
        TG_OP,
        coalesce(nullif(current_setting('app.user', true), ''), current_user),
        CASE WHEN TG_OP IN ('UPDATE', 'DELETE') THEN to_jsonb(OLD) END,
        CASE WHEN TG_OP IN ('INSERT', 'UPDATE') THEN to_jsonb(NEW) END
    );
    RETURN CASE WHEN TG_OP = 'DELETE' THEN OLD ELSE NEW END;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER audit_produk
AFTER INSERT OR UPDATE OR DELETE ON produk
FOR EACH ROW EXECUTE FUNCTION catat_riwayat();

CREATE TRIGGER audit_pelanggan
AFTER INSERT OR UPDATE OR DELETE ON pelanggan
FOR EACH ROW EXECUTE FUNCTION catat_riwayat();

SELECT count(*) AS baris_riwayat FROM riwayat_perubahan;

hapus-sebagai-siti.sql

Unduh berkas
sql 
-- Pengguna berbeda pada sesi yang sama cukup mengatur ulang nilainya
SET app."user" = 'siti';

DELETE FROM produk WHERE id = 2;

UPDATE pelanggan SET poin = poin + 15 WHERE id = 1;

SELECT id, nama_tabel, operasi, pengguna, data_lama, data_baru
FROM riwayat_perubahan
ORDER BY id;

kueri-jejak.sql

Unduh berkas
sql 
-- Siapa mengerjakan apa, di tabel mana
SELECT pengguna, nama_tabel, operasi, count(*) AS jumlah
FROM riwayat_perubahan
GROUP BY pengguna, nama_tabel, operasi
ORDER BY pengguna, nama_tabel, operasi;

-- Kronologi per objek yang tersentuh, lintas jenis operasi
SELECT id, pengguna, operasi,
       coalesce(data_lama->>'nama', data_baru->>'nama') AS objek
FROM riwayat_perubahan
WHERE nama_tabel = 'produk'
ORDER BY id;

-- Cari jejak yang menyentuh data ber-kolom "poin", tanpa tahu tabelnya
SELECT id, nama_tabel, operasi
FROM riwayat_perubahan
WHERE data_lama ? 'poin' OR data_baru ? 'poin'
ORDER BY id;

pengguna-tanpa-nama.sql

Unduh berkas
sql 
-- Hapus identitas aplikasi: fallback ke current_user
RESET app."user";

INSERT INTO produk VALUES (4, 'Kopi Toraja', 120000, 6);

SELECT id, operasi, pengguna FROM riwayat_perubahan ORDER BY id;

-- Rekonstruksi kronologi harga produk dari jejak jsonb
SELECT id, operasi,
       data_lama->>'harga' AS harga_lama,
       data_baru->>'harga' AS harga_baru
FROM riwayat_perubahan
WHERE nama_tabel = 'produk'
ORDER BY id;

set-app-user-salah.sql

Unduh berkas
sql 
-- Bentuk tanpa tanda kutip: ditolak parser
SET app.user = 'budi';

ubah-sebagai-budi.sql

Unduh berkas
sql 
-- Identitas aplikasi dibawa lewat GUC kustom sesi ini
SET app."user" = 'budi';

INSERT INTO produk VALUES (3, 'Gula Aren', 55000, 8);

UPDATE produk SET harga = 95000, stok = 8 WHERE id = 1;

SELECT id, nama_tabel, operasi, pengguna, data_lama, data_baru
FROM riwayat_perubahan
ORDER BY id;