ตอนที่ 14 : การสร้าง Trigger บน MySQL Database (MySQL : Trigger)

CREATE TRIGGER trigger_name { BEFORE | AFTER } { INSERT | UPDATE | DELETE } ON table_name
	FOR EACH ROW
BEGIN
    // Statement
    // new.col_name 
    // old.col_name
END

DROP TRIGGER IF EXISTS delAudit;

DELIMITER //
CREATE TRIGGER delAudit AFTER DELETE ON customer
	FOR EACH ROW
BEGIN
    DELETE FROM audit
        WHERE CUSTOMER_ID = old.CUSTOMER_ID;
END //
DELIMITER ;

DROP TRIGGER IF EXISTS sumUsed;

DELIMITER //
CREATE TRIGGER sumUsed AFTER INSERT ON audit
	FOR EACH ROW
BEGIN
    DECLARE dUsed DECIMAL(18,2) DEFAULT 0;
    
    SELECT SUM(USED) INTO dUsed FROM audit WHERE CUSTOMER_ID = new.CUSTOMER_ID;
    
    UPDATE customer SET USED = dUsed WHERE CUSTOMER_ID = new.CUSTOMER_ID; 
END //
DELIMITER ;

DROP TRIGGER IF EXISTS sumUsed_Delete;

DELIMITER //
CREATE TRIGGER sumUsed_Delete AFTER DELETE ON audit
	FOR EACH ROW
BEGIN
    DECLARE dUsed DECIMAL(18,2) DEFAULT 0;
    
    SELECT SUM(USED) INTO dUsed FROM audit WHERE CUSTOMER_ID = old.CUSTOMER_ID;
    
    UPDATE customer SET USED = dUsed WHERE CUSTOMER_ID = old.CUSTOMER_ID; 
END //
DELIMITER ;