SQL TRIGGER

การสร้างและใช้งาน Trigger
> จะเกิดการทำงานก็ต่อเมื่อมีเหตุการณ์ที่เกี่ยวข้องกับการเปลี่ยนแปลงแก้ไขข้อมูลเกิดขึ้นตรงกันกับที่ object นี้ได้ถูกกำหนดไว้
> จะมีการทำงานในทุกๆครั้ง ที่มีการเปลี่ยนแปลงของข้อมูลภายในตารางที่กำหนดทั้งที่เป็นผลมาจากการทำ INSERT หรือ UPDATE หรือ DELETE

ข้อจำกัดของ Trigger
> ไม่สามารถใช้ได้กับ Temporary Table
> ไม่สามารถส่งคืนข้อมูลกลับมาเป็น Recordset ได้
> คำสั่ง WRITETEXT ไม่สามารถ ปลุกให้ Trigger ทำงานได้
> คำสั่ง ต่อไปนี้ทำงานบน Trigger ไม่ได้ ได้แก่ ALTER DATABASE, CREATE DATABASE, DISK INIT,DISK RESIZE, DROP DATABASE, LOAD DATABASE, LOAD LOG, RECONFIGURE, RESTORE DATABASE,RESTORE LOG

Inserted Table และ Deleted Table
>รู้จักและเข้าใจตาราง Inserted และ Deleted
โดยตารางนี้จะใช้งานได้เพียงภายในระยะการทำงานของ Trigger หนึ่งๆเท่านั้น
โดยตารางที่กล่าวถึงนี้จะเป็นตารางเปล่าๆที่ไม่มีข้อมูลใดๆ
แต่จะมีโครงสร้างทั้งชื่อและจำนวนคอลัมน์ ตลอดจนประเภทข้อมูลเหมือนกับตาราง
ที่ Trigger นั้นอาศัยอยู่ทุกประการ
>คำสั่ง INSERT กับ Trigger
เมื่อมีการเพิ่มแถวข้อมูลให้กับตารางข้อมูลที่ Trigger อาศัยอยู่
ระบบจะเพิ่มแถวข้อมูลเดียวกันนี้ไปยังตาราง Inserted ด้วย
>คำสั่ง DELETE กับ Trigger
เมื่อมีการลบแถวข้อมูลออกจากตารางข้อมูลที่ Trigger อาศัยอยู่
ระบบจะเพิ่มแถวข้อมูลที่ถูกลบออกนี้ไปยังตาราง Deleted ด้วย
>คำสั่ง UPDATE กับ Trigger
เมื่อมีการแก้ไขข้อมูลในตารางข้อมูลที่ Trigger อาศัยอยู่
ระบบจะทำงาน 2 ขั้นตอน คือ
จะนำแถวข้อมูลก่อนการ update ไปเพิ่มไว้ในตาราง Deleted
และจะนำแถวข้อมูลที่ได้หลังการ update ไปเพิ่มไว้ในตาราง Inserte

BEFOR TRIGGERS หรือ AFTER TRIGGERS
เป็น Trigger ซึ่งจะทำงานภายหลังจากที่มีคำสั่ง SQL ที่เกี่ยวข้อง
กับการเปลี่ยนแปลงข้อมูล ( INSERT, DELETE, UPDATE )
โดยมีขั้นตอนการทำงาน ดังนี้
1) ทำคำสั่ง SQL ตามปกติ
2) ที่ Table ใดๆ จะมีการตรวจสอบความถูกต้องของข้อมูลด้วย Constraint ต่างๆ
3) ระบบทำการสร้างตาราง Inserted และ Deleted
4) เริ่มทำงาน BEFOR TRIGGERS หรือ AFTER TRIGGERS
โดยมีข้อจำกัดทางด้านการใช้งาน ดังนี้
1) ใช้ได้กับ Table เท่านั้น
2) สามารถสร้าง Trigger ได้หลายตัว เพื่อทำงานสำหรับคำสั่ง INSERT, DELETE, UPDATE
3) สามารถทำงานร่วมกับ Table ที่ถูกควบคุมความถูกต้องด้วย CASCADING REFERENCES
4) ในกรณีที่มีหลาย Trigger สามารถกำหนดลำดับแรกสุด และท้ายสุด ที่จะให้ Trigger ทำงานได้
5) ไม่อนุญาติให้ใช้กับคอลัมน์ประเภท text, ntext, image ที่อยู่ในตาราง Inserted และ Deleted

example:

CREATE TABLE test1(a1 INT);
CREATE TABLE test2(a2 INT);
CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE test4(
  a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  b4 INT DEFAULT 0
);

delimiter |

CREATE TRIGGER testref BEFORE INSERT ON test1
  FOR EACH ROW BEGIN
    INSERT INTO test2 SET a2 = NEW.a1;
    DELETE FROM test3 WHERE a3 = NEW.a1;
    UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
  END;
|

delimiter ;

INSERT INTO test3 (a3) VALUES
  (NULL), (NULL), (NULL), (NULL), (NULL),
  (NULL), (NULL), (NULL), (NULL), (NULL);

INSERT INTO test4 (a4) VALUES
  (0), (0), (0), (0), (0), (0), (0), (0), (0), (0);

Suppose that you insert the following values into table test1 as shown here:

mysql> INSERT INTO test1 VALUES 
    -> (1), (3), (1), (7), (1), (8), (4), (4);
Query OK, 8 rows affected (0.01 sec)
Records: 8  Duplicates: 0  Warnings: 0

As a result, the data in the four tables will be as follows:

mysql> SELECT * FROM test1;
+------+
| a1   |
+------+
|    1 |
|    3 |
|    1 |
|    7 |
|    1 |
|    8 |
|    4 |
|    4 |
+------+
8 rows in set (0.00 sec)

mysql> SELECT * FROM test2;
+------+
| a2   |
+------+
|    1 |
|    3 |
|    1 |
|    7 |
|    1 |
|    8 |
|    4 |
|    4 |
+------+
8 rows in set (0.00 sec)

mysql> SELECT * FROM test3;
+----+
| a3 |
+----+
|  2 |
|  5 |
|  6 |
|  9 |
| 10 |
+----+
5 rows in set (0.00 sec)

mysql> SELECT * FROM test4;
+----+------+
| a4 | b4   |
+----+------+
|  1 |    3 |
|  2 |    0 |
|  3 |    1 |
|  4 |    2 |
|  5 |    0 |
|  6 |    0 |
|  7 |    1 |
|  8 |    1 |
|  9 |    0 |
| 10 |    0 |
+----+------+
10 rows in set (0.00 sec)

example:

CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
CREATE TRIGGER upd_check BEFORE UPDATE ON account
FOR EACH ROW
BEGIN
IF NEW.amount 100 THEN
SET NEW.amount = 100;
END IF;
END;
INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);
UPDATE account SET amount = 299 WHERE acct_num =141;

example:

CREATE TRIGGER tb4
BEFORE INSERT ON `questionaire`
FOR EACH ROW
BEGIN

IF NEW.QN_WORK_STATUS < 3 THEN
UPDATE grad_tb4 SET as0 = as0 + 1
WHERE FAC_ID = (SELECT `FAC_ID` FROM `uoc_stdgraduate` WHERE STD_ID = NEW.STD_ID )
AND YEAR = (SELECT `G_CONFIRM` FROM `uoc_stdgraduate` WHERE STD_ID = NEW.STD_ID );

ELSEIF NEW.QN_TIME_FINDWORK = 01 THEN
UPDATE grad_tb4 SET as1 = as1 + 1
WHERE FAC_ID = (SELECT `FAC_ID` FROM `uoc_stdgraduate` WHERE STD_ID = NEW.STD_ID )
AND YEAR = (SELECT `G_CONFIRM` FROM `uoc_stdgraduate` WHERE STD_ID = NEW.STD_ID );

ELSEIF NEW.QN_TIME_FINDWORK = 02 THEN
UPDATE grad_tb4 SET as2 = as2 + 1
WHERE FAC_ID = (SELECT `FAC_ID` FROM `uoc_stdgraduate` WHERE STD_ID = NEW.STD_ID )
AND YEAR = (SELECT `G_CONFIRM` FROM `uoc_stdgraduate` WHERE STD_ID = NEW.STD_ID );

ELSEIF NEW.QN_TIME_FINDWORK = 03 THEN
UPDATE grad_tb4 SET as3 = as3 + 1
WHERE FAC_ID = (SELECT `FAC_ID` FROM `uoc_stdgraduate` WHERE STD_ID = NEW.STD_ID )
AND YEAR = (SELECT `G_CONFIRM` FROM `uoc_stdgraduate` WHERE STD_ID = NEW.STD_ID );

ELSEIF NEW.QN_TIME_FINDWORK = 04 THEN
UPDATE grad_tb4 SET as4 = as4 + 1
WHERE FAC_ID = (SELECT `FAC_ID` FROM `uoc_stdgraduate` WHERE STD_ID = NEW.STD_ID )
AND YEAR = (SELECT `G_CONFIRM` FROM `uoc_stdgraduate` WHERE STD_ID = NEW.STD_ID );

ELSEIF NEW.QN_TIME_FINDWORK = 05 THEN
UPDATE grad_tb4 SET as5 = as5 + 1
WHERE FAC_ID = (SELECT `FAC_ID` FROM `uoc_stdgraduate` WHERE STD_ID = NEW.STD_ID )
AND YEAR = (SELECT `G_CONFIRM` FROM `uoc_stdgraduate` WHERE STD_ID = NEW.STD_ID );

ELSEIF NEW.QN_TIME_FINDWORK = 06 THEN
UPDATE grad_tb4 SET as6 = as6 + 1
WHERE FAC_ID = (SELECT `FAC_ID` FROM `uoc_stdgraduate` WHERE STD_ID = NEW.STD_ID )
AND YEAR = (SELECT `G_CONFIRM` FROM `uoc_stdgraduate` WHERE STD_ID = NEW.STD_ID );

ELSEIF NEW.QN_OCCUP_TYPE =04 THEN
UPDATE grad_tb4 SET as7 = as7 + 1
WHERE FAC_ID = (SELECT `FAC_ID` FROM `uoc_stdgraduate` WHERE STD_ID = NEW.STD_ID )
AND YEAR = (SELECT `G_CONFIRM` FROM `uoc_stdgraduate` WHERE STD_ID = NEW.STD_ID );

ELSEIF NEW.QN_TIME_FINDWORK = 07 THEN
UPDATE grad_tb4 SET as8 = as8 + 1
WHERE FAC_ID = (SELECT `FAC_ID` FROM `uoc_stdgraduate` WHERE STD_ID = NEW.STD_ID )
AND YEAR = (SELECT `G_CONFIRM` FROM `uoc_stdgraduate` WHERE STD_ID = NEW.STD_ID );

ELSEIF NEW.QN_WORK_STATUS = 4 THEN
UPDATE grad_tb4 SET as9 = as9 + 1
WHERE FAC_ID = (SELECT `FAC_ID` FROM `uoc_stdgraduate` WHERE STD_ID = NEW.STD_ID )
AND YEAR = (SELECT `G_CONFIRM` FROM `uoc_stdgraduate` WHERE STD_ID = NEW.STD_ID );

END IF;
END|

Advertisements

~ โดย monrada บน มกราคม 8, 2011.

ใส่ความเห็น

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / เปลี่ยนแปลง )

Twitter picture

You are commenting using your Twitter account. Log Out / เปลี่ยนแปลง )

Facebook photo

You are commenting using your Facebook account. Log Out / เปลี่ยนแปลง )

Google+ photo

You are commenting using your Google+ account. Log Out / เปลี่ยนแปลง )

Connecting to %s

 
%d bloggers like this: