Membuat trigger di MySQL

triggers.sql

-- Cara penulisan triggers
DELIMITER $$
CREATE TRIGGER nama_trigger
{BEFORE | AFTER} {INSERT | UPDATE| DELETE }
    ON nama_table 
    FOR EACH ROW
BEGIN
    KODE SQL
END$$
DELIMITER ;


-- BEFORE INSERT	TRIGGER dijalankan sebelum record dimasukkan ke database
-- AFTER INSERT		TRIGGER dijalankan sesudah record dimasukkan ke database
-- BEFORE UPDATE	TRIGGER dijalankan sebelum record dirubah di database
-- AFTER UPDATE		TRIGGER dijalankan sesudah record dirubah database
-- BEFORE DELETE	TRIGGER dijalankan sebelum record dihapus di database
-- AFTER DELETE		TRIGGER dijalankan sesudah record dihapus di database

-- Contoh:
DELIMITER $$ CREATE TRIGGER `payments_after_update` 
AFTER UPDATE 
  ON `payments` FOR EACH ROW BEGIN 
IF NEW.payStatus = 'cancel' 
  OR NEW.vaStatus = 'EXPIRED' THEN 
UPDATE 
  productorders 
SET 
  productorders.orderStatus = 'cancel' 
WHERE 
  productorders.id = NEW.productorderId 
  AND productorders.orderStatus <> 'completed';
ELSEIF NEW.vaStatus = 'PAYMENT_DETECTED' 
OR NEW.vaStatus = 'COMPLETE' 
OR NEW.payStatus = 'paid' THEN 
UPDATE 
  productorders 
SET 
  productorders.orderStatus = 'completed' 
WHERE 
  productorders.id = NEW.productorderId;
END IF;
END $$ DELIMITER;


-- Contoh lain:
DELIMITER $$ CREATE DEFINER = `root` @`localhost` TRIGGER `restockingredients_after_insert` 
AFTER 
  INSERT ON `restockingredients` FOR EACH ROW BEGIN 
UPDATE 
  ingredients 
SET 
  ingredients.amount = ingredients.amount + NEW.restockAmount 
WHERE 
  ingredients.id = NEW.ingredientId;
  
UPDATE 
  stockrooms 
SET 
  stockrooms.qty = stockrooms.qty - NEW.qty 
WHERE 
  stockrooms.id = NEW.stockroomId;
END $$ DELIMITER;

-- Kata kunci
-- NEW.nama_kolom = untuk data yang baru
-- OLD.nama_kolom = untuk data yang lama