昨天討論了Cursor, 今天我們來看看與Stored Procedure相關的另兩個議題: Trigger和Event Scehdule
我們先談TRIGGER, 這是個應用在資料表(Table)的一種控制機制, 主要是在當我們在資料表INSERT、UPDATE、DELETE資料時, '觸發'執行某些程預設的程序.
TRIGGER相關的指令有CREATE TRIGGER及DROP TRIGGER, 我們今天就先討論CREATE TRIGGER, DROP TRIGGER指令只列出語法, 而且DROP TRIGGER也很簡單.

CREATE
    [DEFINER = { user | CURRENT_USER }]
    TRIGGER trigger_name trigger_time trigger_event
    ON tbl_name FOR EACH ROW trigger_stmt
trigger_time: BEFORE | AFTER, 在執行INSERT、UPDATE、DELETE指令之前或之後
trigger_event: INSERT | UPDATE | DELETE, 能觸發TRIGGER的資料表資料處理的指令
trigger_stmt: 可以是單一SQL指令, 或是由BEGIN ... END包含的多行SQL指令

舉例來說, 我們先建立下列各物件:

CREATE TABLE test1(a1 INT);
CREATE TABLE test2(a2 INT);
CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
<- test3.a3的值會由1自動增值...
CREATE TABLE test4(
  a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  b4 INT DEFAULT 0
);
<- test4.a5的值會由1自動增值...

delimiter |

CREATE TRIGGER testref BEFORE INSERT ON test1 <- 用了BEFORE表示在INSERT動作之前 ->
  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);
<- test3的內容為 1, 2, 3, 4, 5, 6, 7, 8, 9, 10

INSERT INTO test4 (a4) VALUES
  (0), (0), (0), (0), (0), (0), (0), (0), (0), (0);
<- test4.a4的值為 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
<- test4.b4的值為 0, 0, 0, 0, 0, 0, 0, 0, 0, 0

然後再執行下列SQL指令:

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

我們可以看看結果:

mysql> SELECT * FROM test1;
+------+
| a1   |  <- a1的內容就是INSERT指令的結果...
+------+
|    1 |
|    3 |
|    1 |
|    7 |
|    1 |
|    8 |
|    4 |
|    4 |
+------+
8 rows in set (0.00 sec)

mysql> SELECT * FROM test2;
+------+
| a2   |
+------+
|    1 | <- INSERT INTO test2 SET a2 = NEW.a1; a1的值同樣INSERT到test2中
|    3 | <- 所以和test1一樣
|    1 |
|    7 |
|    1 |
|    8 |
|    4 |
|    4 |
+------+
8 rows in set (0.00 sec)

mysql> SELECT * FROM test3;
+----+
| a3 |  <- 內容為 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
+----+
|  2 |  <- DELETE FROM test3 WHERE a3 = NEW.a1;
|  5 |  <- test1.a1的值其實只有1, 3, 4, 7, 8
|  6 |  <- 所以結果就如左, 去掉1, 3, 4, 7, 8的其他值
|  9 |
| 10 |
+----+
5 rows in set (0.00 sec)

mysql> SELECT * FROM test4;
+----+------+ <- test4.a4的值為 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
| a4 | b4   | <- test4.b4的值為 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
+----+------+ <- UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
|  1 |    3 | <- test1.a1的值為 (1), (3), (1), (7), (1), (8), (4), (4);
|  2 |    0 | <- 有三個1, 一個3, 2個4, 1個7, 1個8
|  3 |    1 | <- 所以結果如左
|  4 |    2 |
|  5 |    0 |
|  6 |    0 |
|  7 |    1 |
|  8 |    1 |
|  9 |    0 |
| 10 |    0 |
+----+------+
10 rows in set (0.00 sec)

上面的TRIGGER範例其實只是簡單運用, 主要讓大家對TRIGGER的應用有初步認識.
接下來, 我們討論Event Schedule, 這是排程指令, 語法如下:

CREATE
    [DEFINER = { user | CURRENT_USER }]
    EVENT
    [IF NOT EXISTS]
    event_name
    ON SCHEDULE schedule
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'comment']
    DO sql_statement;

schedule:
    AT timestamp [+ INTERVAL interval] ...
  | EVERY interval
    [STARTS timestamp [+ INTERVAL interval] ...]
    [ENDS timestamp [+ INTERVAL interval] ...]

interval:
    quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
              WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
              DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

我們看第一個例子:

CREATE EVENT myevent
    ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
    DO
      UPDATE myschema.mytable SET mycol = mycol + 1;

由建立EVENT的那一刻開時第一小時的時候執行UPDATE指令, 這是只執行一次的EVENT.

mysql> CREATE EVENT e_totals
    ->     ON SCHEDULE AT '2009-10-31 23:59:59'
    ->     DO INSERT INTO test.totals VALUES (NOW());

在2009年10月31日晚間23點59分59秒新增一筆資料錄到test.totals欄位內, 其值也是當時的現在時刻....猜一下會INSERT什麼值? (答案在最後一行)
這個也是只執行一次的排程, 下一個例子則是週期不斷執行的例子

CREATE EVENT e_hourly
    ON SCHEDULE
      EVERY 1 HOUR
    COMMENT 'Clears out sessions table each hour.'
    DO
      DELETE FROM site_activity.sessions;

從建立排程的當時開始, 每一小時執行DELETE指令一次, 沒有停止時候...要有停止的時候的範例如下....

CREATE EVENT e_hourly
    ON SCHEDULE
      EVERY 1 HOUR ENDS '2010-12-31 23:59:59'
    COMMENT 'Clears out sessions table each hour.'
    DO
      DELETE FROM site_activity.sessions;

從建立排程的當時開始, 每一小時執行DELETE指令一次, 直到2010年12月31日晚間23點59分59秒為止.
以上, 就是我們今天的分享....