Kursus MariaDB / Perbedaan dan Penggunaan Prosedur Tersimpan, Fungsi, serta Trigger

Prosedur tersimpan dipanggil secara eksplisit dengan CALL, fungsi tersimpan mengembalikan nilai di dalam ekspresi SQL, dan trigger berjalan otomatis ketika perubahan tabel yang sesuai terjadi. Ketiganya menyimpan logika SQL pada server, tetapi berbeda dalam cara pemanggilan, hak akses, dan tanggung jawab transaksi.

Pilih objek berdasarkan tugasnya

Objek Pemanggilan Tugas yang umum
Prosedur tersimpan CALL nama(argumen) Operasi eksplisit yang terdiri dari beberapa pernyataan SQL
Fungsi tersimpan nama(argumen) dalam ekspresi SQL Aturan yang dapat digunakan ulang dan menghasilkan satu nilai
Trigger Otomatis pada INSERT, UPDATE, atau DELETE Tindakan singkat per baris yang terkait dengan perubahan tabel

Prosedur tidak harus mengubah data, dan fungsi tersimpan tidak selalu terbatas pada aritmetika. Latihan ini memakai tugas yang konkret: prosedur mengubah stok, fungsi menghitung nilai barang, dan trigger mencatat stok lama serta baru. Trigger MariaDB dijalankan untuk setiap baris; perubahan banyak baris juga menimbulkan pekerjaan trigger pada setiap baris tersebut.

Bandingkan pengendalian COMMIT dan ROLLBACK oleh koneksi pemanggil dengan konteks hak akses VIEW. Perilaku otomatis di server dapat menyulitkan diagnosis. Bedakan operasi yang sengaja dipanggil aplikasi dari logika yang menyertai setiap penulisan tabel. Tentukan aturan yang cukup diperiksa pada masukan aplikasi dan aturan yang perlu diterapkan pada beberapa jalur penulisan.

Siapkan hak akses dan dua tabel InnoDB

Gunakan appops_lab dengan akun belajar yang mempunyai CREATE ROUTINE, TRIGGER, serta hak tabel yang diperlukan. Pemanggilan rutin juga membutuhkan EXECUTE. Tinjau akun dan pemberian hak akses untuk membatasi kebutuhan tanpa memberikan hak administrator global kepada aplikasi. Kebijakan binary log, yaitu catatan perubahan untuk pemulihan atau replikasi, dapat menambah persyaratan pembuatan fungsi. Minta administrator basis data memeriksa kebijakan itu alih-alih melonggarkan pengaturan kepercayaan seluruh server demi latihan.

Periksa server, basis data, dan objek yang sudah ada. Pertahankan latihan lain dalam appops_lab. Pastikan nama kedua tabel, fungsi, prosedur, dan trigger berikut belum dipakai. Penyiapan ini tidak menghapus objek yang ada.

CREATE DATABASE IF NOT EXISTS appops_lab
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE appops_lab;
CREATE TABLE routine_stock (
  product_id INT NOT NULL PRIMARY KEY,
  stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB;
CREATE TABLE routine_audit (
  audit_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  product_id INT NOT NULL,
  old_stock INT NOT NULL,
  new_stock INT NOT NULL
) ENGINE=InnoDB;
INSERT INTO routine_stock VALUES (1, 8);

Kedua tabel menggunakan InnoDB sehingga perubahan stok dan penambahan catatan oleh trigger dapat dibatalkan bersama. Jika sebuah objek sudah ada, periksa definisi serta datanya sebelum mengulang pembuatan.

Buat definisi lengkap ketiga objek

Skrip berikut memakai sintaks klien baris perintah mariadb. DELIMITER mengatur cara klien mengenali akhir pernyataan; perintah ini bukan SQL yang dikirim ke server. Pada editor grafis, gunakan fitur eksekusi skripnya. Melalui pengandar Python, kirim setiap CREATE sebagai satu pernyataan lengkap tanpa baris DELIMITER dan tanpa penutup //.

DELIMITER //
CREATE FUNCTION line_total(p_price DECIMAL(10,2), p_qty INT)
RETURNS DECIMAL(12,2)
DETERMINISTIC NO SQL SQL SECURITY INVOKER
BEGIN
  IF p_price IS NULL OR p_qty IS NULL OR p_price < 0 OR p_qty < 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Nonnegative price and quantity required';
  END IF;
  RETURN ROUND(p_price * p_qty, 2);
END//

CREATE PROCEDURE adjust_stock(IN p_product_id INT, IN p_delta INT)
SQL SECURITY INVOKER
MODIFIES SQL DATA
BEGIN
  IF p_delta IS NULL OR p_delta = 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Nonzero stock delta required';
  END IF;
  UPDATE routine_stock SET stock = stock + p_delta
  WHERE product_id = p_product_id AND stock + p_delta >= 0;
  IF ROW_COUNT() <> 1 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Missing product or insufficient stock';
  END IF;
END//

CREATE TRIGGER audit_stock_update
AFTER UPDATE ON routine_stock FOR EACH ROW
BEGIN
  IF OLD.stock <> NEW.stock THEN
    INSERT INTO routine_audit (product_id, old_stock, new_stock)
    VALUES (NEW.product_id, OLD.stock, NEW.stock);
  END IF;
END//
DELIMITER ;

line_total menolak harga atau jumlah negatif maupun NULL, lalu mengembalikan nilai dengan dua angka desimal. Jumlah nol diperbolehkan. Tinjau rentang DECIMAL jika harga atau jumlah dapat besar. Deklarasi DETERMINISTIC NO SQL menjelaskan implementasi ini dan harus tetap benar jika isinya berubah. Referensi CREATE FUNCTION menjelaskan karakteristik dan konteks hak aksesnya.

adjust_stock memperbarui tepat satu produk yang ada hanya jika stok akhirnya tidak negatif. Jika syarat tidak terpenuhi, SIGNAL menghasilkan kesalahan aturan bisnis. Baca ROW_COUNT segera setelah UPDATE karena pernyataan lain dapat mengganti operasi yang diukur. Referensi CREATE PROCEDURE membahas parameter serta konteks pemanggilan.

Trigger menambahkan catatan hanya ketika stok benar-benar berubah. OLD dan NEW adalah nilai baris sebelum dan sesudah perubahan. Tabel ini merupakan catatan perubahan untuk latihan, bukan sistem audit independen yang tahan manipulasi. Referensi CREATE TRIGGER menjelaskan nilai baris dan waktu AFTER UPDATE.

Bandingkan kedua tabel ketika rollback dan komit

SELECT line_total(12.50, 3) AS amount;
START TRANSACTION;
CALL adjust_stock(1, -3);
SELECT product_id, stock FROM routine_stock;
SELECT product_id, old_stock, new_stock FROM routine_audit;
ROLLBACK;
SELECT stock FROM routine_stock WHERE product_id = 1;
SELECT COUNT(*) AS audit_rows FROM routine_audit;

Nilai hasil fungsi adalah 37.50. Di dalam transaksi, stok berubah dari delapan menjadi lima, dan catatan menunjukkan perubahan 8 → 5. Setelah ROLLBACK, stok harus kembali delapan dan jumlah catatan nol. ID yang memakai AUTO_INCREMENT tidak harus dipakai ulang sesudah rollback; celah nomor audit tidak membuktikan pembatalan gagal.

START TRANSACTION;
CALL adjust_stock(1, -2);
COMMIT;
SELECT stock FROM routine_stock WHERE product_id = 1;
SELECT old_stock, new_stock FROM routine_audit ORDER BY audit_id;

Setelah COMMIT, stok menjadi enam dan satu catatan menunjukkan 8 → 6. Prosedur sengaja tidak mempunyai COMMIT internal agar koneksi pemanggil dapat menggabungkannya dengan pekerjaan lain. Trigger berjalan dalam konteks transaksi pernyataan pemicunya dan tidak melakukan komit secara terpisah.

Amati masukan tidak valid dan batas hak akses

Jalankan kedua pernyataan berikut secara terpisah. Yang pertama mencoba mengurangi stok lebih dari enam unit yang tersedia; yang kedua memberikan harga satuan negatif. Keduanya seharusnya menghasilkan SQLSTATE 45000. Setelah itu, periksa bahwa stok tetap enam dan tabel audit masih memiliki satu catatan yang telah dikomit.

CALL adjust_stock(1, -99);
SELECT line_total(-1, 2);

Fungsi dan prosedur memakai SQL SECURITY INVOKER, sehingga akses tabel diperiksa berdasarkan hak pemanggil. Akun dengan EXECUTE tetapi tanpa UPDATE tidak dapat mengubah stok melalui prosedur ini. Trigger menggunakan konteks DEFINER, yaitu akun pendefinisinya, dan tidak dideklarasikan sebagai INVOKER. Kelola akun tersebut, masa berlakunya, dan hak tabel yang dibutuhkan.

Batasan resmi trigger membatasi tindakan seperti pengendalian transaksi dan perubahan kembali pada tabel pemicu. Jaga pekerjaan trigger singkat serta terkait dengan transaksi basis data, tanpa menyembunyikan operasi eksternal yang panjang di balik perubahan baris.

Periksa definisi dan hapus hanya objek latihan

SHOW CREATE PROCEDURE adjust_stock;
SHOW CREATE FUNCTION line_total;
SHOW TRIGGERS FROM appops_lab;

Simpan definisi dan konteks hak akses. Pastikan pencadangan serta pemulihan mencakup rutin dan trigger yang dibutuhkan. Definisi yang dipulihkan ke server lain masih dapat gagal jika akun DEFINER tidak ada atau tidak mempunyai hak yang sesuai. Saat latihan terpisah ini selesai, hapus hanya objeknya sendiri yang memang boleh dibuang:

DROP TRIGGER appops_lab.audit_stock_update;
DROP PROCEDURE appops_lab.adjust_stock;
DROP FUNCTION appops_lab.line_total;
DROP TABLE appops_lab.routine_audit;
DROP TABLE appops_lab.routine_stock;

ROLLBACK biasa tidak mengembalikan objek yang telah dihapus. Periksa nama dan pertahankan tabel lain dalam appops_lab. Perubahan rutin pada sistem nyata juga memerlukan koordinasi dengan pemanggil serta prosedur penerapan dan pemulihan.