Trigger SQLite: CREATE TRIGGER, BEFORE/AFTER, dan OLD/NEW

Trigger Menjalankan SQL Secara Otomatis

Trigger adalah blok SQL tersimpan yang berjalan setiap kali peristiwa tertentu terjadi pada tabel tertentu. Kamu menulisnya sekali. SQLite yang mengurus "kapan"-nya untukmu.

Bentuknya:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL NOT NULL
);

CREATE TABLE price_history (
    product_id INTEGER,
    old_price REAL,
    new_price REAL,
    changed_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER log_price_change
AFTER UPDATE OF price ON products
BEGIN
    INSERT INTO price_history(product_id, old_price, new_price)
    VALUES (OLD.id, OLD.price, NEW.price);
END;

INSERT INTO products(name, price) VALUES ('Notebook', 5.00);
UPDATE products SET price = 6.50 WHERE name = 'Notebook';
SELECT * FROM price_history;

Kita tidak pernah menulis INSERT eksplisit ke price_history. Trigger-lah yang melakukannya. Setiap perubahan harga di masa depan akan dicatat dengan cara yang sama, entah berasal dari CLI, script, atau aplikasi.

Anatomi CREATE TRIGGER

Baca sintaksnya bagian demi bagian:

CREATE TRIGGER trigger_name
{ BEFORE | AFTER | INSTEAD OF } { INSERT | UPDATE [ OF column_list ] | DELETE }
ON table_name
[ FOR EACH ROW ]
[ WHEN condition ]
BEGIN
    -- one or more statements
END;

• Waktu: BEFORE berjalan sebelum perubahan, AFTER berjalan setelahnya, INSTEAD OF menggantikannya (hanya untuk view).
• Peristiwa: operasi yang memicunya. UPDATE OF col1, col2 mempersempit update ke kolom tertentu.
• Tabel: tabel yang diawasi.
• FOR EACH ROW: SQLite hanya mendukung trigger tingkat baris, jadi ini sudah tersirat. Kamu boleh menuliskannya agar lebih jelas; tidak ada yang berubah.
• WHEN: kondisi opsional. Isi trigger hanya berjalan jika kondisinya benar.
• Isi: satu atau lebih pernyataan di antara BEGIN dan END. Setiap pernyataan harus diakhiri titik koma.

Itulah seluruh tata bahasanya. Sebagian besar trigger sungguhan hanya lima sampai sepuluh baris.

OLD dan NEW: Baris yang Sedang Diubah

Di dalam isi trigger, dua baris semu memungkinkanmu melihat datanya:

• NEW: baris yang masuk. Tersedia di trigger INSERT dan UPDATE.
• OLD: baris yang sudah ada. Tersedia di trigger UPDATE dan DELETE.

Trigger DELETE hanya punya OLD. Trigger INSERT hanya punya NEW. Trigger UPDATE punya keduanya.

CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    name TEXT,
    balance REAL NOT NULL
);

CREATE TABLE deletions (
    table_name TEXT,
    row_id INTEGER,
    snapshot TEXT,
    deleted_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER archive_account
AFTER DELETE ON accounts
BEGIN
    INSERT INTO deletions(table_name, row_id, snapshot)
    VALUES ('accounts', OLD.id, OLD.name || ' / ' || OLD.balance);
END;

INSERT INTO accounts(name, balance) VALUES ('Rosa', 120.00);
DELETE FROM accounts WHERE name = 'Rosa';
SELECT * FROM deletions;

Baris yang dihapus sudah hilang dari accounts, tapi datanya sempat direkam di deletions sebelum lenyap.

BEFORE: Validasi atau Sesuaikan Baris

Trigger BEFORE berjalan sebelum perubahan baris ditulis ke disk. Trigger ini praktis untuk memunculkan error atau menormalkan data:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL,
    created_at TEXT
);

CREATE TRIGGER lowercase_email
BEFORE INSERT ON users
BEGIN
    UPDATE users SET email = email; -- noop, just illustrating context
END;

CREATE TRIGGER reject_empty_email
BEFORE INSERT ON users
WHEN NEW.email IS NULL OR trim(NEW.email) = ''
BEGIN
    SELECT RAISE(ABORT, 'email cannot be empty');
END;

INSERT INTO users(email) VALUES ('ada@example.com');
INSERT INTO users(email) VALUES ('');
SELECT * FROM users;

INSERT kedua dibatalkan sebelum ada baris yang ditulis. RAISE(ABORT, '...') membatalkan pernyataan yang sedang berjalan dan me-rollback ke awal pernyataan itu. RAISE(FAIL, ...), RAISE(ROLLBACK, ...), dan RAISE(IGNORE) memberimu kendali yang lebih halus atas apa yang terjadi.

Untuk validasi data murni, utamakan constraint CHECK: sifatnya deklaratif dan optimizer mengenalinya. Pakai trigger BEFORE saat aturannya perlu melihat tabel lain atau melakukan sesuatu yang tidak bisa diungkapkan CHECK.

WHEN: Trigger Bersyarat

Klausa WHEN menyaring perubahan baris mana yang benar-benar menjalankan isi trigger. Klausa ini dievaluasi per baris, setelah OLD dan NEW terisi:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    total REAL NOT NULL,
    status TEXT NOT NULL
);

CREATE TABLE big_orders (
    order_id INTEGER,
    total REAL,
    flagged_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER flag_big_order
AFTER INSERT ON orders
WHEN NEW.total >= 1000
BEGIN
    INSERT INTO big_orders(order_id, total) VALUES (NEW.id, NEW.total);
END;

INSERT INTO orders(total, status) VALUES (50, 'paid');
INSERT INTO orders(total, status) VALUES (1500, 'paid');
INSERT INTO orders(total, status) VALUES (2200, 'paid');
SELECT * FROM big_orders;

Pesanan pertama tidak lolos. Dua lainnya lolos. Tanpa klausa WHEN, setiap insert akan menulis ke big_orders dan kamu harus menyaringnya saat membaca.

INSTEAD OF: Membuat View Bisa Ditulisi

Secara default, view bersifat read-only. Trigger INSTEAD OF mencegat penulisan ke sebuah view dan menjalankan SQL-mu sebagai gantinya, biasanya menerjemahkannya menjadi penulisan ke tabel di baliknya:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    salary REAL
);

CREATE VIEW employee_summary AS
SELECT id, first_name || ' ' || last_name AS full_name, salary
FROM employees;

CREATE TRIGGER employee_summary_insert
INSTEAD OF INSERT ON employee_summary
BEGIN
    INSERT INTO employees(first_name, last_name, salary)
    VALUES (
        substr(NEW.full_name, 1, instr(NEW.full_name, ' ') - 1),
        substr(NEW.full_name, instr(NEW.full_name, ' ') + 1),
        NEW.salary
    );
END;

INSERT INTO employee_summary(full_name, salary) VALUES ('Boris Petrov', 60000);
SELECT * FROM employees;

Aplikasi berbicara dengan view seolah-olah view itu tabel. Trigger mengurus pemisahan menjadi first_name dan last_name di balik layar.

Melihat Daftar dan Menghapus Trigger

Trigger tersimpan di sqlite_master, bersama tabel dan index:

CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT);

CREATE TRIGGER touch_note
AFTER UPDATE ON notes
BEGIN
    SELECT 1; -- placeholder body
END;

SELECT name, tbl_name, sql
FROM sqlite_master
WHERE type = 'trigger';

DROP TRIGGER IF EXISTS touch_note;

SELECT count(*) AS remaining
FROM sqlite_master
WHERE type = 'trigger';

DROP TRIGGER IF EXISTS name; adalah bentuk yang aman. Menghapus tabel tempat trigger berada akan otomatis menghapus trigger itu, jadi kamu tidak perlu membersihkannya lebih dulu.

Jebakan yang Perlu Diketahui

Beberapa hal sering menjebak orang di kali pertama:

• Trigger berjalan per baris, bukan per pernyataan. UPDATE yang menyentuh 1.000 baris menjalankan trigger 1.000 kali. Jika isinya mahal, bebannya cepat menumpuk.
• Trigger berjalan di dalam transaksi yang melingkupinya. Jika pernyataan luar di-rollback, penulisan oleh trigger juga ikut di-rollback. Biasanya itu yang kamu mau, tapi artinya trigger bukan jalan keluar untuk "catat ini apa pun yang terjadi."
• Recursive trigger nonaktif secara default. Trigger yang mengubah tabel yang sama tidak akan memicu dirinya sendiri lagi kecuali kamu mengatur PRAGMA recursive_triggers = ON;. Biarkan nonaktif kecuali kamu punya alasan khusus.
• Penulisan dari aplikasi bisa melewatinya, tapi hanya dengan tidak memakai database. Selama setiap penulisan melewati SQLite, trigger akan berjalan. ORM yang mengirim batch lewat SQL mentah tetap memicunya.
• Jangan sebar logika bisnis ke banyak trigger. Trigger tidak terlihat dari tempat pemanggilan: orang yang mencari tahu "kenapa baris ini muncul?" harus menelusuri sqlite_master. Pakai trigger untuk urusan lintas bagian (log audit, kolom turunan, view yang bisa ditulisi) dan simpan sisanya di kode aplikasi.

Contoh Log Audit yang Realistis

Menggabungkan pola-pola di atas: lacak setiap perubahan pada tabel posts:

CREATE TABLE posts (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    body TEXT,
    updated_at TEXT
);

CREATE TABLE post_audit (
    post_id INTEGER,
    action TEXT,
    old_title TEXT,
    new_title TEXT,
    at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER posts_audit_update
AFTER UPDATE OF title, body ON posts
BEGIN
    UPDATE posts SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
    INSERT INTO post_audit(post_id, action, old_title, new_title)
    VALUES (NEW.id, 'update', OLD.title, NEW.title);
END;

INSERT INTO posts(title, body) VALUES ('Draft', 'first cut');
UPDATE posts SET title = 'Published' WHERE id = 1;
SELECT id, title, updated_at FROM posts;
SELECT * FROM post_audit;

Satu trigger menjaga updated_at tetap akurat dan menulis baris audit, semuanya di satu tempat. Kode aplikasi yang menjalankan UPDATE tidak perlu tahu keduanya ada.

Berikutnya: Dukungan JSON

Trigger menangani otomatisasi di sekitar peristiwa baris. Bagian SQLite lanjutan berikutnya adalah apa yang bisa kamu simpan di dalam sebuah baris: JSON. SQLite punya serangkaian lengkap fungsi JSON untuk melakukan query dan memperbarui data terstruktur tanpa keluar dari SQL, dan itu halaman berikutnya.