• 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
    
  • 相关阅读:
    钉钉机器人Golang版本
    Selenium接管已打开的浏览器
    Transformer总结
    机器学习笔记总结
    DSL 和 reactive 噩梦
    简单软件架构的一些好处zz
    使用C#编写ANTLR
    批量编译VB6和VC6工程
    Python数据挖掘银行分控模型的建立
    Linux: grub修复
  • 原文地址:https://www.cnblogs.com/noway-neway/p/5218763.html
Copyright © 2020-2023  润新知