mysql自学笔记(二十四)之触发器

发布时间:2018-12-04 20:27:10 阅读:2176

    触发器是一种与表操作有关的数据库对象,当触发器所在表上出现指定事件时,将调用该对象,即表的操作事件触发表上的触发器的执行。


        在MySQL中,创建触发器语法如下:

    CREATE TRIGGER trigger_name
    trigger_time
    trigger_event ON tbl_name
    FOR EACH ROW
    trigger_stmt


    trigger_name:标识触发器名称,用户自行指定;
        trigger_time:标识触发时机,取值为 BEFORE 或 AFTER;
        trigger_event:标识触发事件,取值为 INSERT、UPDATE 或 DELETE;
        tbl_name:标识建立触发器的表名,即在哪张表上建立触发器;
        trigger_stmt:触发器程序体,可以是一句SQL语句,或者用 BEGIN 和 END 包含的多条语句。

    一共可以建立6种触发器,即:BEFORE INSERT、BEFORE UPDATE、BEFORE DELETE、AFTER INSERT、AFTER UPDATE、AFTER DELETE。

    另外有一个限制是不能同时在一个表上建立2个相同类型的触发器,因此在一个表上最多建立6个触发器。


    创建有多个执行语句的触发器

    CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件
    ON 表名 FOR EACH ROW
    BEGIN
        执行语句列表
    END


    trigger_event 触发事件详解
        MySQL 除了对 INSERT、UPDATE、DELETE 基本操作进行定义外,还定义了 LOAD DATA 和 REPLACE 语句,这两种语句也能引起上述6中类型的触发器的触发。

    LOAD DATA 语句用于将一个文件装入到一个数据表中,相当与一系列的 INSERT 操作。

    REPLACE 语句一般来说和 INSERT 语句很像,只是在表中有 primary key 或 unique 索引时,如果插入的数据和原来 primary key 或 unique 索引一致时,会先删除原来的数据,然后增加一条新数据,也就是说,一条 REPLACE 语句有时候等价于一条。

    INSERT 语句,有时候等价于一条 DELETE 语句加上一条 INSERT 语句。

    INSERT 型触发器:插入某一行时激活触发器,可能通过 INSERT、LOAD DATA、REPLACE 语句触发;
        UPDATE 型触发器:更改某一行时激活触发器,可能通过 UPDATE 语句触发;
        DELETE 型触发器:删除某一行时激活触发器,可能通过 DELETE、REPLACE 语句触发;


    其中,BEGIN与END之间的执行语句列表参数表示需要执行的多个语句,不同语句用分号隔开

    tips:一般情况下,mysql默认是以 ;(分号) 作为结束执行语句,与触发器中需要的分行起冲突

         为解决此问题可用DELIMITER,如:DELIMITER ||,可以将结束符号变成||

         当触发器创建完成后,可以用DELIMITER ;来将结束符号变成;

    实例:

    MariaDB [test]> DELIMITER ;;						// 将结束符定义为 ;; 
    MariaDB [test]> CREATE TRIGGER `order` BEFORE INSERT ON `gc_order` FOR EACH ROW begin
        ->      DECLARE c BIGINT;						// 定义一个局部变量
        ->      DECLARE b INT;
        ->      
        ->      SET c=UNIX_TIMESTAMP() + ROUND(RAND() * 10000000);	// 生成随机数,给变量赋值
        ->      SET b = (SELECT COUNT(0)AS c0 FROM gc_order WHERE order_no=c);// 查随机数是否存在
        ->      WHILE b>0 DO						// 如果存在,进入循环,重新生成
        ->         SET c=UNIX_TIMESTAMP() + ROUND(RAND() * 10000000);// 生成随机数,给变量赋值
        -> SET b = (SELECT COUNT(0)AS c0 FROM gc_order WHERE order_no=c);// 查随机数是否存在
        ->      END WHILE; 							// 结束循环
        ->      SET new.order_no = c;					// 将生成的随机数赋值给订单号字段
        -> end
        -> ;;								// 结束触发器
    Query OK, 0 rows affected (0.01 sec)
    	
    MariaDB [test]> DELIMITER ;						// 结束触发器


    MySQL 中使用 DECLARE 来定义一局部变量,该变量只能在 BEGIN … END 复合语句中使用,并且应该定义在复合语句的开头

    可以同时定义多个同类型的变量,用逗号隔开;变量初始值为 NULL,如果需要,可以使用 DEFAULT 子句提供默认值,值可以被指定为一个表达式。

    也可对变量赋值采用 SET 语句

    DECLARE c BIGINT;


    NEW 与 OLD 详解

    触发器的所在表中,触发了触发器的那一行数据。
        具体地:
        在 INSERT 型触发器中,NEW 用来表示将要(BEFORE)或已经(AFTER)插入的新数据;
        在 UPDATE 型触发器中,OLD 用来表示将要或已经被修改的原数据,NEW 用来表示将要或已经修改为的新数据;
        在 DELETE 型触发器中,OLD 用来表示将要或已经被删除的原数据;
        使用方法: 

    NEW.order_no(order_no 为gc_order表中的订单号列)

    另外,OLD 是只读的,而 NEW 则可以在触发器中使用 SET 赋值,这样不会再次触发触发器,造成循环调用(如每插入一个学生前,都在其学号前加“2013”)。


    查看触发器

    SHOW TRIGGERS [FROM 数据库名称];   // 使用这个语句之前,不需要 use 数据库;
    MariaDB [test]> SHOW   TRIGGERS;
    +---------+--------+----------+-------------------------------------------------------------------
    | Trigger | Event  | Table    | Statement                                                         
    +---------+--------+----------+-------------------------------------------------------------------
         DECLARE c BIGINT;
         DECLARE b INT;
        
         SET c=UNIX_TIMESTAMP() + ROUND(RAND() * 10000000);
         SET b = (SELECT COUNT(0)AS c0 FROM gc_order WHERE order_no=c);
         WHILE b>0 DO
            SET c=UNIX_TIMESTAMP() + ROUND(RAND() * 10000000);
    SET b = (SELECT COUNT(0)AS c0 FROM gc_order WHERE order_no=c);
         END WHILE;
         SET new.order_no = c;
    end | BEFORE | NULL    |          | root@localhost | utf8                 | utf8_general_ci      | 
    +---------+--------+----------+-------------------------------------------------------------------


    删除触发器

    和删除数据库、删除表格一样,删除触发器的语法如下:

    DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name

    实例:

    MariaDB [test]> DROP TRIGGER IF EXISTS `order`;
    Query OK, 0 rows affected (0.00 sec)


    触发器的执行顺序

    我们建立的数据库一般都是 InnoDB 数据库,其上建立的表是事务性表,也就是事务安全的。这时,若SQL语句或触发器执行失败,MySQL 会回滚事务,有:

    ①如果 BEFORE 触发器执行失败,SQL 无法正确执行。
        ②SQL 执行失败时,AFTER 型触发器不会触发。
        ③AFTER 类型的触发器执行失败,SQL 会回滚


    限制和注意事项

    触发器会有以下两种限制:

    1.触发程序不能调用将数据返回客户端的存储程序,也不能使用采用CALL语句的动态SQL语句,但是允许存储程序通过参数将数据返回触发程序,也就是存储过程或者函数通过OUT或者INOUT类型的参数将数据返回触发器是可以的,但是不能调用直接返回数据的过程。

    2.不能再触发器中使用以显示或隐式方式开始或结束事务的语句,如START TRANS-ACTION,COMMIT或ROLLBACK。

    注意事项:MySQL的触发器是按照BEFORE触发器、行操作、AFTER触发器的顺序执行的,其中任何一步发生错误都不会继续执行剩下的操作,如果对事务表进行的操作,如果出现错误,那么将会被回滚,如果是对非事务表进行操作,那么就无法回滚了,数据可能会出错。


    总结

    触发器是基于行触发的,所以删除、新增或者修改操作可能都会激活触发器,所以不要编写过于复杂的触发器,也不要增加过得的触发器,这样会对数据的插入、修改或者删除带来比较严重的影响,同时也会带来可移植性差的后果,所以在设计触发器的时候一定要有所考虑。

    触发器是一种特殊的存储过程,它在插入,删除或修改特定表中的数据时触发执行,它比数据库本身标准的功能有更精细和更复杂的数据控制能力。

    数据库触发器有以下的作用:

    1.安全性。可以基于数据库的值使用户具有操作数据库的某种权利。

      # 可以基于时间限制用户的操作,例如不允许下班后和节假日修改数据库数据。

      # 可以基于数据库中的数据限制用户的操作,例如不允许股票的价格的升幅一次超过10%。

    2.审计。可以跟踪用户对数据库的操作。   

      # 审计用户操作数据库的语句。

      # 把用户对数据库的更新写入审计表。

    3.实现复杂的数据完整性规则

      # 实现非标准的数据完整性检查和约束。触发器可产生比规则更为复杂的限制。与规则不同,触发器可以引用列或数据库对象。例如,触发器可回退任何企图吃进超过自己保证金的期货。

      # 提供可变的缺省值。

    4.实现复杂的非标准的数据库相关完整性规则。触发器可以对数据库中相关的表进行连环更新。例如,在auths表author_code列上的删除触发器可导致相应删除在其它表中的与之匹配的行。

      # 在修改或删除时级联修改或删除其它表中的与之匹配的行。

      # 在修改或删除时把其它表中的与之匹配的行设成NULL值。

      # 在修改或删除时把其它表中的与之匹配的行级联设成缺省值。

      # 触发器能够拒绝或回退那些破坏相关完整性的变化,取消试图进行数据更新的事务。当插入一个与其主健不匹配的外部键时,这种触发器会起作用。例如,可以在books.author_code 列上生成一个插入触发器,如果新值与auths.author_code列中的某值不匹配时,插入被回退。

    5.同步实时地复制表中的数据。

    6.自动计算数据值

    如果数据的值达到了一定的要求,则进行特定的处理。例如,如果公司的帐号上的资金低于5万元则立即给财务人员发送警告数据。


    触发器尽量少的使用,因为不管如何,它还是很消耗资源,如果使用的话要谨慎的使用,确定它是非常高效的:触发器是针对每一行的;对增删改非常频繁的表上切记不要使用触发器,因为它会非常消耗资源。 


    有好的建议,请在下方输入评论。

关键字mariadb mysql