TRIGGER

Fi

วันที่

เรื่องที่นำเสนอ

รายละเอียด

ตัวอย่าง

เรื่องที่จะนำเสนอครั้งถัดไป

คำถาม

1

21/1/2015

-อธิบายเกี่ยวกับ trigger และการทำงาน

- Code คำสั่ง SQL ที่ถูกสร้างขึ้นให้ทำงานกับ Table หรือ View โดยที่คำสั่งเหล่านั้นจะทำงานโดยอัตโนมัติ เมื่อมีการเปลี่ยนแปลงอย่างใดอย่างหนึ่งกับ Table หรือ View (insert,update,delete) -เมื่อเกิด Query ใน Mysql แล้ว Database จะทำการตรวจสอบว่ามี Trigger ไหนตรงตามเงื่อนไขบ้าง และเรียกใช้งาน

-ข้อดี

-ทำงานอัตโนมัติเมื่อมีการเปลี่ยนแปลงข้อมูลในตารางที่กำหนด -ทำงานเร็วกว่าการส่งข้อมูลไปมาระหว่าง Mysql กับ PHP -ช่วยแบ่งเบา Process ของโปรแกรมไปได้มาก เพราะ Trigger จะทำงานบน Database Server -ตรวจสอบความสมบูรณ์ของข้อมูล เช่น หลังจากแก้ไขข้อมูลแล้ว เก็บประวัติการแก้ไขในตาราง log ด้วย -ตรวจสอบการเปลี่ยนแปลงของข้อมูลในตาราง เช่น ใช้เงื่อนไขเช็คข้อมูลก่อนบันทึกในตาราง

-ข้อเสีย

-Debug ยาก -การสร้างเงื่อนไขใน PHP มีความยืดหยุ่นกว่า -อาจเกิดความยุ่งยากในการดูแลในระยะยาว หากต้องแก้ไขโปรแกรมที่มี trigger ผูกกับตารางต่างๆในโปรแกรม

-ข้อจำกัด

-ในหนึ่งตารางไม่สามารถสร้างหลาย trigger ที่มี action time กับ event เหมือนกัน -ไม่สามารถเปลี่ยนแปลงข้อมูลของตารางที่ถูกกำหนด event ใน trigger (สามารถ select ตารางนั้นได้) รวมถึงเรียก stored ให้เปลี่ยนแปลงข้อมูลก็ไม่สามารถทำได้ -ไม่สามารถ rollback ข้อมูลได้

-CREATE TRIGGER Syntax

CREATE TRIGGER trigger_name trigger_time trigger_event ON table_name FOR EACH ROW BEGIN ... END **trigger_time: { BEFORE | AFTER } **trigger_event: { INSERT | UPDATE | DELETE }

DELIMITER // CREATE TRIGGER TG_UPDATE_DATA AFTER UPDATE ON TG_SUM_EMP FOR EACH ROW BEGIN INSERT INTO test2 SET a2 = NEW.a1; DELETE FROM test3 WHERE a3 = OLD.a1; END; // DELIMITER ; **OLD คือข้อมูลเก่าก่อนการแก้ไข (event INSERT จะไม่สามารถใช้ได้) **NEW คือข้อมูลใหม่ (event DELETE จะไม่สามารถใช้ได้) **การเขียน OLD|NEW.field_name

-การปรกาศตัวแปร

DECLARE variable_name variable_type ;

DECLARE count_emp INTEGER; DECLARE sum INTEGER DEFAULT 0; DECLARE comp_name VARCHAR(250) CHARACTER SET utf8;

2

29/1/2015

-ความเชื่องโยงระหว่าง Triger และ Stored

-stored ทำงานเมื่อมีการ call ส่วน trigger ทำงานอัตโนมัติตาม event ที่กำหนด -การประกาศตัวแปร ,สร้างเงื่อนไข เหมือกัน -trigger สามารถ call stored ได้

การ call stored :CALL stored_name();

-คำสั่งใน phpMyAdmin

-แสดงทั้งหมด : SHOW TRIGGERS; -ค้นหาจากชื่อtrigger : SHOW TRIGGERS LIKE '%update%'; -ลบ : DROP TRIGGER IF EXISTS trigger_name ;

-ตัวอย่าง

การ call stored

DELIMITER // CREATE TRIGGER TG_update_emp AFTER UPDATE ON emp_data FOR EACH ROW BEGIN DECLARE count_emp INTEGER; DECLARE check_TG_SUM_EMP INTEGER DEFAULT 0;DECLARE count_emp INTEGER; DECLARE check_TG_SUM_EMP INTEGER DEFAULT 0; SELECT COUNT(company_id) INTO check_TG_SUM_EMP FROM TG_SUM_EMP ; IF check_TG_SUM_EMP = 0 THEN CALL SP_add_newData(); ELSE SELECT COUNT(DataNo) INTO count_emp FROM emp_data WHERE Prosonnal_Being != 3 AND Working_Company = NEW.Working_Company; UPDATE TG_SUM_EMP SET sum_emp = count_emp,date_update = NOW() WHERE company_id = NEW.Working_Company; END IF; END; // DELIMITER ;

-ตัวอย่าง

เงื่อนไข IF

DELIMITER // CREATE TRIGGER TG_UPDATE_working_company AFTER UPDATE ON working_company FOR EACH ROW BEGIN IF NEW.status = 99 THEN DELETE FROM ERP_easyhr_checktime.TG_SUM_EMP WHERE company_id = NEW.id; ELSE UPDATE ERP_easyhr_checktime.TG_SUM_EMP SET company_id = NEW.id,company_name = NEW.name ,date_update = NOW() WHERE company_id = OLD.id; END IF; END; // DELIMITER ;

3

7/2/2015

-MySQL Cursor

การประกาศ:DECLARE cursor_name CURSOR FOR select_statement; เช็คข้อมูลว่าง:DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

DELIMITER $$ CREATE PROCEDURE build_email_list (INOUT email_list varchar(4000)) BEGIN DECLARE v_finished INTEGER DEFAULT 0; DECLARE v_email varchar(100) DEFAULT ""; -- declare cursor for employee email DEClARE email_cursor CURSOR FOR SELECT email FROM employees; -- declare NOT FOUND handler DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1; OPEN email_cursor; get_email: LOOP FETCH email_cursor INTO v_email; IF v_finished = 1 THEN LEAVE get_email; END IF; -- build email list SET email_list = CONCAT(v_email,";",email_list); END LOOP get_email; CLOSE email_cursor; END$$ DELIMITER ;

ตัวอย่าง

การใช้ MySQL Cursor

DELIMITER // CREATE TRIGGER TG_add_newData BEFORE INSERT ON emp_data FOR EACH ROW BEGIN DECLARE loop_finish INTEGER DEFAULT 0; DECLARE comp_id INTEGER; DECLARE comp_name VARCHAR(250); DECLARE count_emp INTEGER; DECLARE GROUP_data CURSOR FOR SELECT id, name FROM ERP_easyhr_OU.working_company WHERE status != 99 order by id; DECLARE CONTINUE HANDLER FOR NOT FOUND SET loop_finish = 1; OPEN GROUP_data; get_data:LOOP FETCH GROUP_data INTO comp_id,comp_name; IF loop_finish = 1 THEN LEAVE get_data; ELSE SELECT COUNT(DataNo) INTO count_emp FROM emp_data WHERE Prosonnal_Being != 3 AND Working_Company = comp_id; INSERT INTO TG_SUM_EMP (company_id, company_name,sum_emp,date_update) VALUES(comp_id,comp_name,count_emp,NOW()); END IF; END LOOP get_data; CLOSE GROUP_data; END; // DELIMITER ;

-DECLARE ... HANDLER Syntax เพื่อการ debug

DECLARE handler_action HANDLER FOR condition_value [, condition_value] ... statement ** handler_action: CONTINUE | EXIT | UNDO condition_value: mysql_error_code | SQLSTATE [VALUE] sqlstate_value | condition_name | SQLWARNING | NOT FOUND | SQLEXCEPTION

DELIMITER // CREATE TRIGGER TG_UPDATE_EMP AFTER UPDATE ON emp_data FOR EACH ROW BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN END; INSERT INTO LOG_DATA (id, data,event) VALUES(NEW.id,NEW.data,'update'); END; // DELIMITER ;