mysql中的trigger

mysql中trigger的语法跟procedure和function类似。
1 创建

    CREATE
        [DEFINER = { user | CURRENT_USER }]
        TRIGGER trigger_name
        trigger_time trigger_event
        ON tbl_name FOR EACH ROW
        trigger_body

    trigger_time: { BEFORE | AFTER }

    trigger_event: { INSERT | UPDATE | DELETE }

具体操作中也和function类似，例如：

    mysql> delimiter //
    mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account
        -> FOR EACH ROW
        -> BEGIN
        ->     IF NEW.amount < 0 THEN
        ->         SET NEW.amount = 0;
        ->     ELSEIF NEW.amount > 100 THEN
        ->         SET NEW.amount = 100;
        ->     END IF;
        -> END;//
    mysql> delimiter ;

若只有一句trigger语句，则begin...end复合句式可以不用，如：

    mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account
        -> FOR EACH ROW SET @sum = @sum + NEW.amount;

不同的地方是：
a trigger不能用call来调用
b trigger的语句中不能有事务的开启和结束，如 START TRANSACTION, COMMIT, or ROLLBACK等等。
2 查看trigger

    SHOW TRIGGERS [{FROM | IN} db_name][LIKE 'pattern' | WHERE expr]
    或者从information_schema.TRIGGERS中查看
    SELECT * FROM INFORMATION_SCHEMA.TRIGGERSWHERE condition;

3 删除

    DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name