ใช้ Trigger ป้องกัน INSERT หรือ UPDATE ใน MySQL

ใช้คำสั่ง SIGNAL SQLSTATE เพื่อหยุดการ INSERT หรือ UPDATE ใน MySQL

รูปแบบ Trigger

DELIMITER // 
CREATE TRIGGER yourTriggerName BEFORE INSERT ON yourTableName 
FOR EACH ROW 
BEGIN 
IF yourCondition THEN 
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'anyMessageToEndUser'; 
END IF; 
END // 
DELIMITER ;

ตัวอย่างการใช้งาน

สร้างตาราง:

mysql> CREATE TABLE Insert_Prevent ( Id INT ); Query OK, 0 rows affected (0.62 sec)

สร้าง Trigger ป้องกัน INSERT หาก Id น้อยกว่า 1 หรือมากกว่า 5:

mysql> DELIMITER // 
mysql> CREATE TRIGGER InsertPreventTrigger BEFORE INSERT ON Insert_Prevent 
FOR EACH ROW 
BEGIN 
IF (NEW.Id < 1 OR NEW.Id > 5) THEN 
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'You cannot insert record'; 
END IF; 
END // 
Query OK, 0 rows affected (0.20 sec) 
mysql> DELIMITER ;

ทดสอบ INSERT ค่าที่ไม่ผ่านเงื่อนไข (0 และ 6):

mysql> INSERT INTO Insert_Prevent VALUES(0); ERROR 1644 (45000): You cannot insert record
mysql> INSERT INTO Insert_Prevent VALUES(6); ERROR 1644 (45000): You cannot insert record

INSERT ค่าที่ผ่านเงื่อนไข (1-5):

mysql> INSERT INTO Insert_Prevent VALUES(1); Query OK, 1 row affected (0.20 sec)
mysql> INSERT INTO Insert_Prevent VALUES(5); Query OK, 1 row affected (0.17 sec)
mysql> INSERT INTO Insert_Prevent VALUES(2); Query OK, 1 row affected (0.11 sec)
mysql> INSERT INTO Insert_Prevent VALUES(3); Query OK, 1 row affected (0.23 sec)

แสดงข้อมูล:

mysql> SELECT * FROM Insert_Prevent;
+------+ | Id   | +------+ | 1    | | 5    | | 2    | | 3    | +------+ 4 rows in set (0.00 sec)

Trigger ทำงานแบบ BEFORE INSERT จึงสามารถป้องกันการเขียนข้อมูลที่ไม่ถูกต้องได้อย่างมีประสิทธิภาพ