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

มิถุนายน 4, 2018 2:20 am โดย admin
0
32

ตอนที่ 14 : การสร้าง Trigger บน MySQL Database (MySQL : Trigger) สำหรับ Trigger เป็นฟีเจอร์ที่น่าสนใจมากและถ้านำมันไปใช้งานจะช่วยให้การเขียนโปรแกรมกับ MySQL Database จะทำให้โปรแกรมนั้น ๆ มีประสิทธิภาพมากขึ้นมากเลยทีเดียวโดย Trigger นี้ เป็น Events (เหตุการณ์) ที่เกิดขึ้นกับ TABLE เช่นมีการใช้คำสั่ง INSERT, UPDATE, หรือ DELETE กับ TABLE ที่เราได้สร้าง Trigger ไว้ ก็จะทำให้ Trigger นั้นทำงานอัตโนมัติตามคำสั่ง SQL ที่เราได้เขียนไว้นั่นเอง โดย Trigger นี้สามารถเพิ่มเงื่อนไขเช่น { BEFORE AFTER } จะให้ทำงานก่อนการเกิด Event หรือ หลังจากที่เกิด Event นั้นแล้ว รองรับ Action ทั้งหมด 3 รูปแบบคือ { INSERT UPDATE DELETE }

โครงสร้างของตาราง customer และ country

Table : customer

Stored Procedure บน MySQL

Table : country

Stored Procedure บน MySQL

Table : audit

Stored Procedure บน MySQL

Create Trigger Syntax

1.CREATE TRIGGER trigger_name { BEFORE | AFTER } { INSERT UPDATE DELETE ON table_name
2.FOR EACH ROW
3.BEGIN
4.// Statement
5.// new.col_name
6.// old.col_name
7.END

อธิบาย
new.col_name ในกรณีที่ Trigger นี้เกิดหลังจากที่ INSERT เราสามารถเรียก Column นั้นด้วยใช้ new.col_name
old.col_name ในกรณีที่ Trigger นี้เกิดหลังจากที่ DELETE เราสามารถเรียก Column นั้นด้วยใช้ old.col_name

Example 1 : ตัวอย่างการสร้าง Trigger ให้ DELETE ข้อมูลใน Table ที่เกี่ยวข้อง

MySQL Trigger

จากรูปจะเห็นว่าตาราง customer กับ audit จะมีข้อมูลที่เชื่อมโยงกันด้วย CUSTOMER_ID ซึ่งเราจะสร้าง Trigger เมื่อลบข้อมูลจาก customer ให้มันไปลบข้อมูลใน audit ด้วย

01.DROP TRIGGER IF EXISTS delAudit;
02. 
03.DELIMITER //
04.CREATE TRIGGER delAudit AFTER DELETE ON customer
05.FOR EACH ROW
06.BEGIN
07.DELETE FROM audit
08.WHERE CUSTOMER_ID = old.CUSTOMER_ID;
09.END //
10.DELIMITER ;

จากคำสั่งนี้จะเห็นว่าเมื่อมีการ DELETE ที่ customer จะสั่งให้ DELETE ที่ audit ด้วยการ CUSTOMER_ID = old.CUSTOMER_ID

MySQL Trigger

รายการ Trigger ที่ถูกสร้าง

MySQL Trigger

ทดสอบลบหรือ DELETE ข้อมูลในตาราง customer

MySQL Trigger

ยืนยันลบข้อมูล

MySQL Trigger

หลังจากที่ลบข้อมูลใน customer จะเห็นว่าใน audit ถูกลบออกไปด้วย

เพิ่มเติม การสร้าง Trigger บน phpMyAdmin

MySQL Trigger

เลือกเมนู Trigger -> Add triggers

MySQL Trigger

จะเห็นว่าการสร้าง Trigger บน phpMyAdmin ค่อนข้างจะทำได้สะดวกมาก

MySQL Trigger

รายการ Trigger ที่ถูกสร้าง

Example 2 : ตัวอย่างการสร้าง Trigger ให้ INSERT ข้อมูล และอัพเดดข้อมูลที่เกี่ยวข้อง

MySQL Trigger

จากรูปจะเห็นว่าตาราง customer กับ audit จะมีข้อมูลที่เชื่อมโยงกันด้วย CUSTOMER_ID ใน Column ของ USED ใน customer จะเป็นค่า SUM มาจาก audit โดยในตัวอย่างนี้เราจะทำ Trigger เมื่อเพิ่มข้อมูลใน audit ให้ทำการ SUM แล้วอัพเดดค่า USED ใน customer ด้วย

01.DROP TRIGGER IF EXISTS sumUsed;
02. 
03.DELIMITER //
04.CREATE TRIGGER sumUsed AFTER INSERT ON audit
05.FOR EACH ROW
06.BEGIN
07.DECLARE dUsed DECIMAL(18,2) DEFAULT 0;
08. 
09.SELECT SUM(USED) INTO dUsed FROM audit WHERE CUSTOMER_ID = new.CUSTOMER_ID;
10. 
11.UPDATE customer SET USED = dUsed WHERE CUSTOMER_ID = new.CUSTOMER_ID;
12.END //
13.DELIMITER ;

จากคำสั่งนี้จะเห็นว่าเมื่อมีการ INSERTที่ audit จะสั่งให้ SUM ค่าแล้วไป UPDATE ที่ customer

MySQL Trigger

Trigger ที่ถูกสร้างบน phpMyAdmin

MySQL Trigger

ทดสอบเพิ่มหรือ INSERTข้อมูลในตาราง audit

MySQL Trigger

จะเห็นว่าที่ customer จะมีการ UPDATE คอลัมบ์ของ USED ให้อัตโนมัติ

ในขณะเดียวกันเมื่อมีการ DELETE ที่ audit เราก็สามารถเขียน Trigger ไป UPDATE ไป customer ได้เช่นเดียวกัน

01.DROP TRIGGER IF EXISTS sumUsed_Delete;
02. 
03.DELIMITER //
04.CREATE TRIGGER sumUsed_Delete AFTER DELETE ON audit
05.FOR EACH ROW
06.BEGIN
07.DECLARE dUsed DECIMAL(18,2) DEFAULT 0;
08. 
09.SELECT SUM(USED) INTO dUsed FROM audit WHERE CUSTOMER_ID = old.CUSTOMER_ID;
10. 
11.UPDATE customer SET USED = dUsed WHERE CUSTOMER_ID = old.CUSTOMER_ID;
12.END //
13.DELIMITER ;

แหล่งที่มา : thaicreate.com/tutorial