MYSQL入门学习之十一：触发器的基本操作



  触发器是MySQL响应以下任意语句而自动执行的一条MySQL语句（或位于BEGIN和END语句之间的一组语句）：
            DELETE；
            INSERT；
            UPDATE；
            使用触发器，需要MySQL5或之后的版本支持。
一、触发器基本操作
1、创建触发器
        创建触发器时，需要给出4条信息：
        唯一的触发器名；(虽然MySQL5允许不同的表上的触发器名称相同，但一般最好不要这么做。)
        触发器关联的表；
        触发器响应的事件；
        触发器何时执行；
        语法结构：
        create trigger trigger_name (BEFORE|AFTER) (delete|update|insert) on table_name
        for each row
        BEGIN
        要触发的sql语句;
        END;
        示例：

[sql] view plain copy print ?

1. mysql> delimiter |  
2. mysql> create trigger t_trig before insert on t_goods for each row  
3.     -> begin  
4.     ->     set NEW.add_date = current_date();  
5.     -> end;  
6.     -> |  
7. mysql> delimiter ;  
8. mysql> insert into t_goods(id,goods_name,quantity)  
9.     -> values(1,'apple',50);  
10.     -> |  
11. mysql> select * from t_goods;  
12.     -> |  
13. +------+------------+----------+------------+  
14. | id   | goods_name | quantity | add_date   |  
15. +------+------------+----------+------------+  
16. |    1 | apple      |       50 | 2012-12-12 |  
17. +------+------------+----------+------------+  

        mysql> delimiter |
        mysql> create trigger t_trig before insert on t_goods for each row
            -> begin
            ->     set NEW.add_date = current_date();
            -> end;
            -> |
        mysql> delimiter ;
        mysql> insert into t_goods(id,goods_name,quantity)
            -> values(1,'apple',50);
            -> |
        mysql> select * from t_goods;
            -> |
        +------+------------+----------+------------+
        | id   | goods_name | quantity | add_date   |
        +------+------------+----------+------------+
        |    1 | apple      |       50 | 2012-12-12 |
        +------+------------+----------+------------+

2、删除触发器
        DROP TRIGGER [schema_name.]trigger_name；  
        示例：
        mysql> DROP TRIGGER t_trig;
3、查看触发器
        SHOW TRIGGERS [ FROM DBNAME [ like '' ] ];
        示例：

[sql] view plain copy print ?

1. mysql> show triggers;  
2. +---------+--------+---------+------------------------------------------------------------------------------------------  
3. | Trigger | Event  | Table   | Statement  
4. +---------+--------+---------+------------------------------------------------------------------------------------------  
5. | t_trig  | INSERT | t_goods | begin  
6.     set NEW.add_date = current_date();  
7. end | BEFORE | NULL    |          | root@localhost | latin1               | latin1_swedish_ci    | latin1_swedish_ci  |  
8. +---------+--------+---------+------------------------------------------------------------------------------------------  
9. mysql> show triggers from test;  
10. +---------+--------+---------+------------------------------------------------------------------------------------------  
11. | Trigger | Event  | Table   | Statement  
12. +---------+--------+---------+------------------------------------------------------------------------------------------  
13. | t_trig  | INSERT | t_goods | begin  
14.     set NEW.add_date = current_date();  
15. end | BEFORE | NULL    |          | root@localhost | latin1               | latin1_swedish_ci    | latin1_swedish_ci  |  
16. +---------+--------+---------+------------------------------------------------------------------------------------------  
17. mysql> show triggers from test like 't%';  
18. +---------+--------+---------+------------------------------------------------------------------------------------------  
19. | Trigger | Event  | Table   | Statement  
20. +---------+--------+---------+------------------------------------------------------------------------------------------  
21. | t_trig  | INSERT | t_goods | begin  
22.     set NEW.add_date = current_date();  
23. end | BEFORE | NULL    |          | root@localhost | latin1               | latin1_swedish_ci    | latin1_swedish_ci  |  
24. +---------+--------+---------+------------------------------------------------------------------------------------------  

        mysql> show triggers;
        +---------+--------+---------+------------------------------------------------------------------------------------------
        | Trigger | Event  | Table   | Statement
        +---------+--------+---------+------------------------------------------------------------------------------------------
        | t_trig  | INSERT | t_goods | begin
            set NEW.add_date = current_date();
        end | BEFORE | NULL    |          | root@localhost | latin1               | latin1_swedish_ci    | latin1_swedish_ci  |
        +---------+--------+---------+------------------------------------------------------------------------------------------
        mysql> show triggers from test;
        +---------+--------+---------+------------------------------------------------------------------------------------------
        | Trigger | Event  | Table   | Statement
        +---------+--------+---------+------------------------------------------------------------------------------------------
        | t_trig  | INSERT | t_goods | begin
            set NEW.add_date = current_date();
        end | BEFORE | NULL    |          | root@localhost | latin1               | latin1_swedish_ci    | latin1_swedish_ci  |
        +---------+--------+---------+------------------------------------------------------------------------------------------
        mysql> show triggers from test like 't%';
        +---------+--------+---------+------------------------------------------------------------------------------------------
        | Trigger | Event  | Table   | Statement
        +---------+--------+---------+------------------------------------------------------------------------------------------
        | t_trig  | INSERT | t_goods | begin
            set NEW.add_date = current_date();
        end | BEFORE | NULL    |          | root@localhost | latin1               | latin1_swedish_ci    | latin1_swedish_ci  |
        +---------+--------+---------+------------------------------------------------------------------------------------------

二、注意事项
1、只有表支持触发器，视图及临时表都不支持；
2、每个表最多支持6个触发器；
3、单一触发器不能与多个事件或多个表关联；
4、对于INSERT而言，新增的行用NEW来表示，行中的每一列的值，用NEW.列名来表示；
        对于DELETE而言，删除的行用OLD来表示；
        对于UPDATE而言，更新前的行用OLD来表示，更新后的行用NEW来表示；