精华内容
下载资源
问答
  • mysql 触发器
    2021-01-28 07:11:44

    1   引言Mysql的触发器和存储过程一样,都是嵌入到mysql的一段程序。触发器是mysql5新增的功能,目前线上凤巢系统、北斗系统以及哥伦布系统使用的数据库均是mysql5.0.45版本,很多程序比如fc-star管理端,sfrd(das),dorado都会用到触发器程序,实现对于数据库增、删、改引起事件的关联操作。本文介绍了触发器的类型和基本使用方法,讲述了触发器使用中容易产生的误区,从mysql源码中得到触发器执行顺序的结论,本文最后是实战遭遇的触发器经典案例。没有特殊说明时,本文的实验均基于mysql5.0.45版本。

    2   Mysql触发器的类型

    2.1   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是触发器程序体;触发器程序可以使用begin和end作为开始和结束,中间包含多条语句;

    下面给出sfrd一个触发器实例:

    CREATE /*!50017 DEFINER = 'root'@'localhost' */ TRIGGER trig_useracct_update

    AFTER UPDATE

    ON SF_User.useracct FOR EACH ROW

    BEGIN

    IF OLD.ulevelid = 10101 OR OLD.ulevelid = 10104 THEN

    IF NEW.ulevelid = 10101 OR NEW.ulevelid = 10104 THEN

    if NEW.ustatid != OLD.ustatid OR NEW.exbudget != OLD.exbudget THEN

    INSERT into FC_Output.fcevent set type = 2, tabid = 1, level = 1, userid = NEW.userid, ustatid = NEW.ustatid, exbudget = NEW.exbudget;

    end if;

    ELSE

    INSERT into FC_Output.fcevent set type = 1, tabid = 1, level = 1, userid = NEW.userid, ustatid = NEW.ustatid, exbudget = NEW.exbudget;

    END IF;

    END IF;

    END;

    上述触发器实例使用了OLD关键字和NEW关键字。OLD和NEW可以引用触发器所在表的某一列,在上述实例中,OLD.ulevelid表示表 SF_User.useracct修改之前ulevelid列的值,NEW.ulevelid表示表SF_User.useracct修改之后 ulevelid列的值。另外,如果是insert型触发器,NEW.ulevelid也表示表SF_User.useracct新增行的 ulevelid列值;如果是delete型触发器OLD.ulevelid也表示表SF_User.useracct删除行的ulevelid列原值。

    另外,OLD列是只读的,NEW列则可以在触发器程序中再次赋值。

    上述实例也使用了IF,THEN ,ELSE,END IF等关键字。在触发器程序体中,在beigin和end之间,可以使用顺序,判断,循环等语句,实现一般程序需要的逻辑功能。

    查看触发器。查看触发器语法如下,如果知道触发器所在数据库,以及触发器名称等具体信息:

    SHOW TRIGGERS from SF_User like "usermaps%";       //查看SF_User库上名称和usermaps%匹配的触发器

    如果不了解触发器的具体的信息,或者需要查看数据库上所有触发器,如下:

    SHOW TRIGGERS;       //查看所有触发器

    用上述方式查看触发器可以看到数据库的所有触发器,不过如果一个库上的触发器太多,由于会刷屏,可能没有办法查看所有触发器程序。这时,可以采用如下方式:

    Mysql中有一个information_schema.TRIGGERS表,存储所有库中的所有触发器,desc information_schema. TRIGGERS,可以看到表结构:

    +----------------------------+--------------+------+-----+---------+-------+

    | Field                      | Type         | Null | Key | Default | Extra |

    +----------------------------+--------------+------+-----+---------+-------+

    | TRIGGER_CATALOG            | varchar(512) | YES |     | NULL    |       |

    | TRIGGER_SCHEMA             | varchar(64) | NO   |     |         |       |

    | TRIGGER_NAME               | varchar(64) | NO   |     |         |       |

    | EVENT_MANIPULATION         | varchar(6)   | NO   |     |         |       |

    | EVENT_OBJECT_CATALOG       | varchar(512) | YES |     | NULL    |       |

    | EVENT_OBJECT_SCHEMA        | varchar(64) | NO   |     |         |       |

    | EVENT_OBJECT_TABLE         | varchar(64) | NO   |     |         |       |

    | ACTION_ORDER               | bigint(4)    | NO   |     | 0       |       |

    | ACTION_CONDITION           | longtext     | YES |     | NULL    |       |

    | ACTION_STATEMENT           | longtext     | NO   |     |         |       |

    | ACTION_ORIENTATION         | varchar(9)   | NO   |     |         |       |

    | ACTION_TIMING              | varchar(6)   | NO   |     |         |       |

    | ACTION_REFERENCE_OLD_TABLE | varchar(64) | YES |     | NULL    |       |

    | ACTION_REFERENCE_NEW_TABLE | varchar(64) | YES |     | NULL    |       |

    | ACTION_REFERENCE_OLD_ROW   | varchar(3)   | NO   |     |         |       |

    | ACTION_REFERENCE_NEW_ROW   | varchar(3)   | NO   |     |         |       |

    | CREATED                    | datetime     | YES |     | NULL    |       |

    | SQL_MODE                   | longtext     | NO   |     |         |       |

    | DEFINER                    | longtext     | NO   |     |         |       |

    +----------------------------+--------------+------+-----+---------+-------+

    这样,用户就可以按照自己的需要,查看触发器,比如使用如下语句查看上述触发器:

    select * from information_schema. TRIGGERS where TRIGGER_NAME= 'trig_useracct_update'\G;

    删除触发器。删除触发器语法如下:

    DROP TRIGGER [schema_name.]trigger_name

    2.2   Msyql触发器的trigger_time和trigger_event

    现在,重新注意到trigger_time和trigger_event,上文说过, trigger_time可以用before和after替换,表示触发器程序的执行在sql执行的前还是后;trigger_event可以用 insert,update,delete替换,表示触发器程序在什么类型的sql下会被触发。

    在一个表上最多建立6个触发器,即1)before insert型,2)before update型,3)before delete型,4)after insert型,5)after update型,6)after delete型。

    触发器的一个限制是不能同时在一个表上建立2个相同类型的触发器。这个限制的一个来源是触发器程序体的“begin和end之间允许运行多个语句”(摘自mysql使用手册)。

    另外还有一点需要注意,msyql除了对insert,update,delete基本操作进行定义外,还定义了load data和replace语句,而load data和replace语句也能引起上述6中类型的触发器的触发。

    Load data语句用于将一个文件装入到一个数据表中,相当与一系列insert操作。replace语句一般来说和insert语句很像,只是在表中有 primary key和unique索引时,如果插入的数据和原来primary key和unique索引一致时,会先删除原来的数据,然后增加一条新数据;也就是说,一条replace sql有时候等价于一条insert sql,有时候等价于一条delete sql加上一条insert sql。即是:

    ?   Insert型触发器:可能通过insert语句,load data语句,replace语句触发;

    ?   Update型触发器:可能通过update语句触发;

    ?   Delete型触发器:可能通过delete语句,replace语句触发;

    3   Mysql触发器的执行顺序

    先抛出触发器相关的几个问题

    3.1   如果before类型的触发器程序执行失败,sql会执行成功吗?

    实验如下:

    1)在FC_Word.planinfo中建立before触发器:

    DELIMITER |

    create trigger trigger_before_planinfo_update

    before update

    ON FC_Word.planinfo FOR EACH ROW

    BEGIN

    insert into FC_Output.abc (planid) values (New.planid);

    END

    |

    2)查看:mysql> select showprob from planinfo where planid=1;

    +----------+

    | showprob |

    +----------+

    |        2 |

    +----------+

    3)执行sql:

    update planinfo set showprob=200 where planid=1;      触发触发器程序;

    4)由于不存在FC_Output.abc,before触发器执行失败,提示:

    ERROR 1146 (42S02): Table 'FC_Output.abc' doesn't exist

    5)再次查看:

    mysql> select showprob from planinfo where planid=1;

    +----------+

    | showprob |

    +----------+

    |        2 |

    +----------+

    即修改sql未执行成功。即如果before触发器执行失败,sql也会执行失败。

    3.2   如果sql执行失败,会执行after类型的触发器程序吗?

    实验如下:

    1)在FC_Word.planinfo中建立after触发器:

    DELIMITER |

    create trigger trigger_after_planinfo_update

    after update

    ON FC_Word.planinfo FOR EACH ROW

    BEGIN

    INSERT INTO FC_Output.fcevent set level = 2, type = 2, tabid = 5, userid = NEW.userid, planid = NEW.planid, planstat2 = NEW.planstat2, showprob = NEW.showprob, showrate = NEW.showrate, showfactor = NEW.showfactor, planmode = NEW.planmode;

    END

    |

    2)查看触发表:

    mysql> select * from FC_Output.fcevent where planid=1;

    Empty set (0.00 sec)

    没有planid=1的记录

    3)执行sql:

    mysql> update planinfo set showprob1=200 where planid=1;

    4)由于不存在showprob1列,提示错误:

    ERROR 1054 (42S22): Unknown column 'showprob1' in 'field list'

    5)再次查看触发表:

    mysql> select * from FC_Output.fcevent where planid=1;

    Empty set (0.00 sec)

    触发表中没有planid=1的记录,sql在执行失败时,after型触发器不会执行。

    3.3   如果after类型的触发器程序执行失败,sql会回滚吗?

    实验如下:

    1)在FC_Word.planinfo中建立after触发器:

    DELIMITER |

    create trigger trigger_after_planinfo_update

    after update

    ON FC_Word.planinfo FOR EACH ROW

    BEGIN

    insert into FC_Output.abc (planid) values (New.planid);

    END

    |

    2)查看:mysql> select showprob from planinfo where planid=1;

    +----------+

    | showprob |

    +----------+

    |        2 |

    +----------+

    3)执行sql:

    update planinfo set showprob=200 where planid=1;触发触发器程序;

    4)由于不存在FC_Output.abc,after触发器执行失败,提示:

    ERROR 1146 (42S02): Table 'FC_Output.abc' doesn't exist

    5)再次查看:

    mysql> select showprob from planinfo where planid=1;

    +----------+

    | showprob |

    +----------+

    |        2 |

    +----------+

    即修改sql未执行成功。即如果after触发器执行失败,sql会回滚。

    这里需要说明一下,上述实验所使用的mysql引擎是innodb,innodb引擎也是目前线上凤巢系统、北斗系统以及哥伦布系统所使用的引擎,在 innodb上所建立的表是事务性表,也就是事务安全的。“对于事务性表,如果触发程序失败(以及由此导致的整个语句的失败),该语句所执行的所有更改将回滚。对于非事务性表,不能执行这类回滚”(摘自mysql使用手册)。因而,即使语句失败,失败之前所作的任何更改依然有效,也就是说,对于 innodb引擎上的数据表,如果触发器中的sql或引发触发器的sql执行失效,则事务回滚,所有操作会失效。

    3.4   mysql触发器程序执行的顺序

    当一个表既有before类型的触发器,又有after类型的触发器时;当一条sql语句涉及多个表的update时,sql、触发器的执行顺序经过mysql源码包装过,有时比较复杂。

    可以先看一段mysql的源代码,当SQL中update多表的时候,Mysql的执行过程如下(省去了无关代码):

    /* 遍历要更新的所有表 */

    for (cur_table= update_tables; cur_table; cur_table= cur_table->next_local)

    {

    org_updated = updated

    /* 如果有 BEFORE 触发器,则执行;如果执行失败,跳到err2位置 */

    if (table->triggers &&

    table->triggers->process_triggers(thd, TRG_EVENT_UPDATE,TRG_ACTION_BEFORE, TRUE))

    goto err2;

    /*执行更新,如果更新失败,跳到err位置*/

    if(local_error=table->file->update_row(table->record[1], table->record[0])))

    goto err;

    updated++; // 更新计数器

    /* 如果有 AFTER 触发器,则执行;如果执行失败,跳到err2位置*/

    if (table->triggers &&

    table->triggers->process_triggers(thd, TRG_EVENT_UPDATE, TRG_ACTION_AFTER, TRUE))

    goto err2;

    err:

    {

    /*标志错误信息,写日志等*/

    }

    err2:

    {

    /*恢复执行过的操作*/

    check_opt_it.rewind();

    /*如果执行了更新,且表是有事务的,做标志*/

    if (updated != org_updated)

    {

    if (table->file->has_transactions())

    transactional_tables= 1;

    }

    }

    }

    从上面代码可以找到本章开始时抛出问题的答案。

    1)   如果before型触发器执行失败,直接goto跳到err2位置,不会执行后续sql语句;

    2)   如果sql执行失败,直接goto跳到err位置,不会执行或许的after型触发器;

    3)   如过after触发器执行失败,goto到err2位置,恢复执行过的操作,且在事务型的表上做标记。

    另外,在使用复杂的sql时,由于有些复杂的sql是mysql自己定义的,所以存在不确定性,使用简单的sql比较可控。

    4   Mysql触发器在数据库同步中的表现

    4.1   触发器运行失败时,数据库同步会失败吗?

    有同步关系如下dbA?dbB。初始时同步正常。

    1)在dbB上建立触发器:

    DELIMITER |

    create trigger trigger_after_planinfo_update

    after update

    ON FC_Word.planinfo FOR EACH ROW

    BEGIN

    insert into FC_Output.abc (planid) values (New.planid);

    END

    |

    2)在dbA上执行sql,执行成功;

    mysql> update planinfo set showprob=200 where planid= 1;

    Query OK, 1 row affected (0.00 sec)

    Rows matched: 1 Changed: 1 Warnings: 0

    3)由于dbB上没有FC_Output.abc表,触发器会执行失败,这时,检查一下同步状态:

    Slave_IO_Running: Yes

    Slave_SQL_Running: NO

    Last_Errno: 1146

    Last_Error: Error 'Table 'FC_Output.abc' doesn't exist' on query. Default database: 'FC_Word'. Query: 'update planinfo set showprob=200 where planid= 1'

    可以看到IO线程运行正常,sql线程运行失败,并提示触发器运行失败的错误信息。

    回忆一下3.1和3.3所述部分,无论是before部分的触发器还是after类型的触发器,对于innodb引擎,当触发器执行失败时,相应sql也会执行失败,所以数据库同步也会失败。

    4.2   创建、删除触发器写bin-log

    创建和删除触发器的语句也会写入bin-log里,所以也会如一般的insert,update,delete语句一样同步到下游数据库中,即上游创建触发器,下游也会创建。

    这里再引出两个小问题:有同步关系dbA?dbB,

    1)   在dbA上创建一个触发器,如果dbB上已经有同表同类型的触发器,同步状态如何?

    2)   在dbB上删除一个触发器,如果dbB上没有对应触发器,同步状态如何?

    这两个问题可以类比同步中的insert语句和delete语句,答案就是

    1)   同步失败,因为不允许重复创建同表同类型的触发器;

    2)   同步正常,因为drop一个不存在的触发器,不影响运行结果;

    5   Mysql触发器经典案例

    5.1   案例1 一条sql涉及多个表的update时,触发得到update之前的旧值

    【现象】表test_info上建有触发器如下:

    CREATE /*!50017 DEFINER = 'root'@'localhost' */ TRIGGER trig_test_info_update

    AFTER UPDATE

    ON FC_Word.test_info FOR EACH ROW

    BEGIN

    DECLARE tlevel INTEGER DEFAULT 0;

    DECLARE ttype INTEGER DEFAULT 0;

    SET tlevel = 4;

    SET ttype = 33;

    INSERT INTO TEST_Output.fcevent (te, le, uid, pid, uid, wid, bi, mbid, wl) SELECT ttype, tlevel, NEW.uid, NEW.pid, NEW.uid, NEW.wid, NEW.bi, NEW.mbid, wl FROM TEST_Word.wext2 where wid = NEW.wid;

    /*。。。其余部分逻辑省略*/

    END IF;

    END;

    这个触发器程序有点长,可以单看飘黄的两句,即更新操作满足第一个条件执行飘黄语句时,触发器的行为。触发器是建立在test_info表上的,飘黄语句中可以看到,也需要查询wext2表。

    执行如下sql1:

    Update test_info a, wext2 b set a.th=(a.th+1), a.w4=(a.w4&8), b.wl=NULL where a.wid=b.wid and a.wid=142394379;

    可以看到sql中既修改了test_info2表,同时修改了wext2表,程序原意是触发得到wext2表wl字段修改后的新值(即NULL);不过实验得到,执行上述sql后,触发器程序查询到的wurl是sql修改之前的旧值。

    再执行下面类似sql2:

    Update wext2 a, test_info2 b set b.th=(b.th+1), b.w4=(b.w4&8), a.wl=NULL where a.wid=b.wid and a.wid=142394379;

    实验的到,执行上述sql后,触发器程序查询到的wurl是sql修改之后的新值。

    【原因】原因当然与sql中的别名a,b无关,而是和wext2表和test_info表的书写顺序有关。如本文3.4部分所述,一条sql涉及多个表的 update操作时,数据表字段、触发器执行顺序是mysql源码包装过的。在执行上述sql1时,先执行test_info的更新,然后是after触发器,最后是wext2的更新,也就是说,在执行after触发器时,wext2还没有进行更新,所以触发得到的是旧值。而执行sql2时,先执行 wext2更新,然后是test_info更新,最后是after触发器,也就是说,在执行after触发器时,wext2已经更新完毕,所以出去得到的是新值。

    引起上述现象是顺序关系的,无论该表是否支持事务。在使用复杂的sql时,由于有些复杂的sql是mysql自己定义的,所以存在不确定性,存在风险,使用简单的sql比较可控。

    5.2   案例2 mysql5.0.19版本修改表结构后触发器失效

    【现象】userpref表上建有after类型触发器,修改userpref表的外键关联后,在userpref表中的新增记录没有触发下来,即触发器失效。

    【原因】mysql5.0.19修改表结构是,触发器消失。这是mysql5.0.19的一个bug,在创建触发器时,会把触发器的内容保存在 information_schema.TRIGGERS表中,同时在var目录下创建触发器的数据库目录下创建一个触发器名称为前缀,以TRN为后缀的文件,当修改触发器的表时,information_schema.TRIGGERS表的内容会删除,导致触发器消失。

    在mysql5.0.45版本中,这个bug已经被修复。Mysql5.0.45版本的触发器,无论是修改表的索引、外键,还是改变表字段,触发器都不会失效。

    5.3   案例3 删除数据表后触发器失效

    【现象】联调环境中存在dbA?dbB,主库dbA上没有触发器,在从库dbB上的FC_Word.wnegative表,FC_Word.wbuget 表上建有触发器;触发器开始运行正常,期间没有对从库的任何直接操作,有一日发现对wnegative表上的修改无法触发。查看从库状态,同步正常;用 select TRIGGER_NAME from information_schema.TRIGGERS发现wnegative表上的触发器消失了;在var/FC_Word目录下也没有 wnegative的.TRN文件,wnegative表上的触发器不见了。

    【分析】查找dbB的查询日志,发现有一条:

    100223 18:27:45 135939 Query       DROP TABLE IF EXISTS `wnegative`

    135939 Query       CREATE TABLE `wnegative` (

    KEY `Index_wnegative_planid` (`planid`),

    KEY `Index_wnegative_unitid` (`unitid`)

    135939 Query       /*!40000 ALTER TABLE `wnegative` DISABLE KEYS */

    100223 18:27:46 135939 Query       INSERT INTO `wnegative` VALUES (614,1,289026,2911155,1848481);

    可以看到,在100223 18:27:45时,删除了表wnegative,紧接着有创建表wnegative;查找触发表发现,在100223 18:27:45时间后对wnegative的修改就没有触发了,而在这个之前对wnegative的修改是触发正常的。故,怀疑对wnegative表的删除使wnegative表上的触发器也被删除。对wnegative表的删除是在主库dbA上操作后,被同步到dbB上。

    【原因】在删除wnegative表时,mysql同时删除了wegative表上的触发器。

    可以通过下面实验证明上述猜测:

    1)   首先在wnegative建立after insert型触发器;

    2)   增加一条wnegative中记录;

    3)   查看结果发现触发器正确触发;

    4)   删除wnegative表;

    5)   使用select TRIGGER_NAME from information_schema.TRIGGERS查看所有触发器,wnegative表上触发器已经不存在了;同时到var/FC_Word目录下,对应触发器的.TRN文件也不存在了;

    6)   重新创建wnegative表,并增加一条wnegative中记录;没有了wnegative表上触发器,自然也不能触发任何结果。

    6   结束语

    Mysql中的触发器功能已经在凤巢系统的各个模块中有广泛应用,究其细节,还有很多值得注意的地方;本文建立在实验和案例的基础上,数据库基于线上系统使用的mysql5.0.45版本,分析了触发器相关的一些特殊情况下msyql的处理方式。

    (全文完)

    更多相关内容
  • MySQL触发器语法详解:  触发器 trigger是一种特殊的存储过程,他在插入(inset)、删除(delete)或修改(update)特定表中的数据时触发执行,它比数据本身标准的功能更精细和更复杂的数据控制能力。触发器不是由...
  • 本文实例讲述了mysql触发器之触发器的增删改查操作。分享给大家供大家参考,具体如下: 我们在创建触发器后,可以在包含触发器定义文件的数据文件夹中显示其定义。触发器作为纯文本文件存储在以下数据库文件夹中: /...
  • 本文实例讲述了mysql触发器之创建多个触发器操作。分享给大家供大家参考,具体如下: 这次记录的内容mysql 版本必须得是5.7.2+的哈,之前的会不好使的。废话不多说,咱们开始正文哈。 在mysql 5.7.2+版本之前,我们...
  • mysql通过触发器实现两个表的同步 目前,在本地测试成功。 假设本地的两个数据库a和b,a下有表table1(id, val) b下有表table2(id, val) 假设希望当table1中数据更新,table2中数据同步更新。 代码: DELIMITER $...
  • 主要介绍了MySQL触发器运用于迁移和同步数据的实例教程,分别是SQL Server数据迁移至MySQL以及同步备份数据表记录的两个例子,需要的朋友可以参考下
  • mysql触发器实例

    2018-01-12 16:46:10
    内含mysql的trigger触发器实例,修改即可使用。。。。。。
  •  在MySQL数据库中,数据库对象表是存储和操作数据的逻辑结构,而数据库对象触发器则用来实现由一些表事件触发的某个操作。在数据库系统中,当执行表事件时,则会激活触发器,从而执行器包含的操作。触发器的操作...
  • mysql触发器

    2019-07-27 05:03:20
    NULL 博文链接:https://fangyong2006.iteye.com/blog/768289
  • 本文实例讲述了mysql触发器之创建使用触发器。分享给大家供大家参考,具体如下: 我们可以可以使用CREATE TRIGGER语句创建一个新的触发器,来看下具体的语法: CREATE TRIGGER trigger_name trigger_time trigger_...
  • MySQL 触发器

    2021-02-27 16:03:08
    Re: MySQL 触发器 ============================= # 触发器概念 触发器,顾名思义,就是当执行某个事件的时候触发另一个事件的执行!数据库中的触发器是和表关联的特殊的存储过程,它可以在插入、删除或修改该表表...
  • 主要介绍了MySQL触发器基本用法,结合实例形式分析了mysql触发器的基本创建、查看、删除等相关使用方法与注意事项,需要的朋友可以参考下
  • Mysql 触发器

    2021-01-18 19:31:58
    概述文章内容主要讲述的是insert,update,delete触发器,如果之前有使用其它产品数据库产品还是有的地方需要注意,例如sqlserver使用inserted获取新插入的值,而mysql是使用NEW.COLUMN来获取。只能对永久表创建触发器...

    概述

    文章内容主要讲述的是insert,update,delete触发器,如果之前有使用其它产品数据库产品还是有的地方需要注意,例如sqlserver使用inserted获取新插入的值,而mysql是使用NEW.COLUMN来获取。

    只能对永久表创建触发器,不能在临时表中创建触发器;同一个表不能存在两个相同类型的触发器,例如不能存在两个insert触发器,对应update触发器可以通过IF 不同的字段执行不一样的操作。

    当前测试版本:mysql 5.6.21。

    目录

    步骤

    #产品表CREATE TABLEProduct

    (proIDINT AUTO_INCREMENT NOT NULL PRIMARY KEY COMMENT '商品表主键',

    priceDECIMAL(10,2) NOT NULL COMMENT '商品价格',

    typeINT NOT NULL COMMENT '商品类别(0生鲜,1食品,2生活)',

    dtimeDATETIME NOT NULL COMMENT '创建时间')AUTO_INCREMENT=1 COMMENT='商品表';

    #商品类别汇总表CREATE TABLEProductType

    (IDINT NOT NULL COMMENT '商品类别(0生鲜,1食品,2生活)',

    amountINT NOT NULL COMMENT '每种类别商品总金额',PRIMARY KEY(ID)

    )COMMENT='商品类别资金汇总表';

    #产品价格变动表CREATE TABLEProduct_log

    (IDINT AUTO_INCREMENT NOT NULL COMMENT '主键',

    productidINT NOT NULL COMMENT '产品id',

    newpriceDECIMAL(10,2) COMMENT '更改后的价格',

    oldpriceDECIMAL(10,2) COMMENT '更改前的价格',PRIMARY KEY(ID)

    )AUTO_INCREMENT=1 COMMENT='产品价格变动表';

    #插入测试数据INSERT INTO ProductType VALUES(1,0.00),(2,0.00),(3,0.00);

    INSERT触发器

    insert触发器只有NEW.Column.

    在Product表中建立INSERT触发器,当往Product表中插入产品时,更新ProductType表对应的分类商品价格。

    DELIMITER $$CREATE TRIGGER TR_Product_insert AFTER INSERT ON Product FOREACH ROWBEGIN

    UPDATEProductTypeSET amount=amount+NEW.priceWHERE ID=NEW.type;END$$

    DELIMITER ;

    插入测试数据

    INSERT INTO Product(price,type,dtime) VALUES(10.00,1,NOW()),(10.00,1,NOW()),(10.00,2,NOW()),(10.00,3,NOW());SELECT * FROMProduct;SELECT * FROM ProductType;

    055934c4f5f6394c375b079446481ef4.png

    UPDATE触发器

    update触发器中NEW.column代表更新后的值,OLD.column代表更新前的值。

    #UPDATE触发器

    DELIMITER $$CREATE TRIGGER TR_Product_updat AFTER UPDATE ON Product FOREACH ROWBEGIN

    IF NEW.price<>OLD.price THEN#当价格发生变化时生成一条价格变动的日志信息插入Product_log表INSERT INTO Product_log(productid,newprice,oldprice) VALUES(NEW.proID,NEW.price,OLD.price);ELSE IF NEW.type<>OLD.type THEN#当产品类型发生改变时更新ProductType表对应的类别UPDATEProductTypeSET amount=amount+(SELECT price FROM Product WHERE proID=NEW.proid)WHERE ID=NEW.type;UPDATEProductTypeSET amount=amount-(SELECT price FROM Product WHERE proID=NEW.proid)WHERE ID=OLD.type;END IF;END IF;END$$

    DELIMITER ;

    测试数据

    UPDATEProductSET price=40.00

    WHERE proid=4;

    1ec77064ebbe4cef4ef2730942227d52.png

    UPDATEProductSET type=2

    WHERE proid=4;

    2faa539382e2233e2c81d41af53cdc9d.png

    DELETE触发器

    DELIMITER $$CREATE TRIGGER TR_product_delete BEFORE DELETE ON product FOREACH rowBEGIN

    UPDATEproducttypeSET amount=amount-(SELECT price FROM product WHERE proID=OLD.proID)WHERE ID=OLD.type;END$$

    DELIMITER ;

    测试数据

    DELETE FROM product WHERE proID=4;

    f31a68bfee21d07849d6c037ad88641a.png

    完善语句

    #NEW.Column:获取新插入的列值,OLD.column:获取更新列的旧值

    #产品表CREATE TABLEProduct

    (proIDINT AUTO_INCREMENT NOT NULL PRIMARY KEY COMMENT '商品表主键',

    priceDECIMAL(10,2) NOT NULL COMMENT '商品价格',

    typeINT NOT NULL COMMENT '商品类别(0生鲜,1食品,2生活)',

    dtimeDATETIME NOT NULL COMMENT '创建时间')AUTO_INCREMENT=1 COMMENT='商品表';

    #商品类别汇总表CREATE TABLEProductType

    (IDINT NOT NULL COMMENT '商品类别(0生鲜,1食品,2生活)',

    amountINT NOT NULL COMMENT '每种类别商品总金额',PRIMARY KEY(ID)

    )COMMENT='商品类别资金汇总表';

    #产品价格变动表CREATE TABLEProduct_log

    (IDINT AUTO_INCREMENT NOT NULL COMMENT '主键',

    productidINT NOT NULL COMMENT '产品id',

    newpriceDECIMAL(10,2) NOT NULL COMMENT '更改后的价格',

    oldpriceDECIMAL(10,2) NOT NULL COMMENT '更改前的价格',

    remarkVARCHAR(20) NOT NULL COMMENT '价格更改备注',

    dtimeDATETIME NOT NULL DEFAULT current_timestamp COMMENT '记录插入的时间',PRIMARY KEY(ID)

    )AUTO_INCREMENT=1 COMMENT='产品价格变动表';

    #插入测试数据INSERT INTO ProductType VALUES(1,0.00),(2,0.00),(3,0.00);

    DELIMITER $$CREATE TRIGGER TR_Product_insert AFTER INSERT ON Product FOREACH ROWBEGIN

    UPDATEProductTypeSET amount=amount+NEW.priceWHERE ID=NEW.type;END$$

    DELIMITER ;

    #UPDATE触发器DROP TRIGGER IF EXISTSTR_Product_updat;

    DELIMITER $$CREATE TRIGGER TR_Product_updat AFTER UPDATE ON Product FOREACH ROWBEGIN

    IF NEW.price>OLD.price THEN#当价格发生变化时生成一条价格变动的日志信息插入Product_log表INSERT INTO Product_log(productid,newprice,oldprice,remark) VALUES(NEW.proID,NEW.price,OLD.price,'加价');UPDATEProductTypeSET amount=amount+NEW.price-OLD.priceWHERE ID=NEW.type;ELSE IF NEW.price

    INSERT INTO Product_log(productid,newprice,oldprice,remark) VALUES(NEW.proID,NEW.price,OLD.price,'减价');UPDATEProductTypeSET amount=amount-OLD.price+NEW.priceWHERE ID=NEW.type;END IF;END IF;IF NEW.type<>OLD.type THEN#当产品类型发生改变时更新ProductType表对应的类别INSERT INTO Product_log(productid,newprice,oldprice,remark) VALUES(NEW.proID,NEW.price,OLD.price,'产品类型变更');UPDATEProductTypeSET amount=amount+NEW.priceWHERE ID=NEW.type;UPDATEProductTypeSET amount=amount-NEW.priceWHERE ID=OLD.type;END IF;END$$

    DELIMITER ;

    #DELETE触发器

    DELIMITER $$CREATE TRIGGER TR_product_delete BEFORE DELETE ON product FOREACH rowBEGIN

    UPDATEproducttypeSET amount=amount-(SELECT price FROM product WHERE proID=OLD.proID)WHERE ID=OLD.type;END$$

    DELIMITER ;INSERT INTO product(price,type,dtime) VALUES(10.00,1,NOW()),(10.00,1,NOW()),(10.00,1,NOW()),(10.00,2,NOW()),(10.00,3,NOW()),(10.00,4,NOW());

    #测试数据UPDATEproductSET price=30

    WHERE proID=5;

    #测试价格变动UPDATEproductSET price=20

    WHERE proID=5;

    #测试产品类型变动UPDATEproductSET type=2

    WHERE proID=5;

    总结

    文章在INSTER和UPDATE触发器中用的是AFTER方式的触发,AFTER触发就是在执行命令操作之后执行触发操作;在DELETE触发器中用到的是BEFORE触发,BEFORE触发就是在执行操作命令之前执行触发操作。

    文章如果对大家有帮助,请帮忙推荐,谢谢!!!

    备注:

    本站点所有随笔都是原创,欢迎大家转载;但转载时必须注明文章来源,且在文章开头明显处给明链接,否则保留追究责任的权利。

    《欢迎交流讨论》

    展开全文
  • 主要介绍了MySQL 触发器定义与用法,结合简单实例形式总结分析了mysql触发器的语法、原理、定义及使用方法,需要的朋友可以参考下
  • MySQL触发器学习总结

    2020-12-15 12:38:38
    代码如下: #创建触发器,当往order表中添加记录是,更新goods表 delimiter $ CREATE TRIGGER trigger1 AFTER INSERT ON `order` FOR EACH ROW BEGIN UPDATE goods SET num=num-new.much WHERE id=new.gid; END$ 执行 ...
  • MySQL触发器

    2021-02-01 09:10:43
    (HeidiSQL中执行Trigger语句会有bug) 由于MySQL中的每个触发器只能针对一个动作,所以本次移植就需要创建两个触发器。对于发生变更的行,在触发器中可以用 NEW 来代替。 下边的触发器有什么问题吗? delimiter && ...

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_NULLS ON

    GO

    ALTER TRIGGER [trg_risks] ON dbo.projectrisk

    FOR INSERT, UPDATE

    AS

    BEGIN

    UPDATE projectrisk

    SET classification =

    case

    when calc>= 9 then 3

    when calc <9 and calc>=4 then 2

    when calc <4 then 1

    end

    from (select inserted.id, inserted.possibility*inserted.severity as calc from inserted) as T1

    where projectrisk.id = T1.id

    END

    GO

    SET QUOTED_IDENTIFIER OFF

    GO

    SET ANSI_NULLS ON

    GO

    简单了解了下MySQL中,Trigger的语法。

    # 创建

    CREATE TRIGGER

    { BEFORE | AFTER }

    { INSERT | UPDATE | DELETE }

    ON

    FOR EACH ROW

    # 删除

    DROP TRIGGER

    注:创建触发器需要CREATE TRIGGER权限。(HeidiSQL中执行Trigger语句会有bug)

    由于MySQL中的每个触发器只能针对一个动作,所以本次移植就需要创建两个触发器。对于发生变更的行,在触发器中可以用 NEW 来代替。

    下边的触发器有什么问题吗?

    delimiter &&

    CREATE TRIGGER trg_risks_insert

    AFTER INSERT ON `projectrisk`

    FOR EACH ROW

    UPDATE projectrisk SET classification = CASE

    WHEN possibility*severity>=9 THEN 3

    WHEN possibility*severity <9 AND possibility*severity>=4 THEN 2

    WHEN possibility*severity <4 THEN 1

    END

    WHERE id = new.id;

    &&

    CREATE TRIGGER trg_risks_update

    AFTER UPDATE ON `projectrisk`

    FOR EACH ROW

    UPDATE projectrisk SET classification = CASE

    WHEN possibility*severity>=9 THEN 3

    WHEN possibility*severity <9 AND possibility*severity>=4 THEN 2

    WHEN possibility*severity <4 THEN 1

    END

    WHERE id = new.id;

    &&

    delimiter ;

    问题就是,没有考虑到触发器中的修改也会触发触发器,进入了死循环。做了如下修改后,终于OK了。

    delimiter &&

    CREATE TRIGGER trg_risks_insert

    BEFORE INSERT ON `projectrisk`

    FOR EACH ROW

    BEGIN

    SET new.classification = CASE

    WHEN new.possibility*new.severity>=9 THEN 3

    WHEN new.possibility*new.severity <9 AND new.possibility*new.severity>=4 THEN 2

    WHEN new.possibility*new.severity <4 THEN 1

    END;

    END

    &&

    CREATE TRIGGER trg_risks_update

    BEFORE UPDATE ON `projectrisk`

    FOR EACH ROW

    BEGIN

    SET new.classification = CASE

    WHEN new.possibility*new.severity>=9 THEN 3

    WHEN new.possibility*new.severity <9 AND new.possibility*new.severity>=4 THEN 2

    WHEN new.possibility*new.severity <4 THEN 1

    END;

    END

    &&

    delimiter ;

    分享到:

    18e900b8666ce6f233d25ec02f95ee59.png

    72dd548719f0ace4d5f9bca64e1d7715.png

    2011-09-09 16:54

    浏览 5848

    分类:数据库

    评论

    展开全文
  • 主要介绍了MySQL触发器简单用法,结合实例形式分析了mysql触发器的创建、执行、查看、删除等相关操作技巧,需要的朋友可以参考下
  • 主要介绍了mysql 触发器语法与应用,结合实例形式详细分析了mysql 触发器的基本语法与插入、更细、删除等相关操作技巧,需要的朋友可以参考下
  • Mysql触发器

    千次阅读 2021-01-25 10:36:05
    触发器是一种特殊类型的存储过程,它不同于存储过程,主要是通过事件触发而被执行的,即不是主动调用而执行的;而存储过程则需要主动调用其名字执行 触发器:trigger,是指事先为某张表绑定一段代码,当表中的某些...

    一、基本概念

    触发器是一种特殊类型的存储过程,它不同于存储过程,主要是通过事件触发而被执行的,即不是主动调用而执行的;而存储过程则需要主动调用其名字执行

    触发器:trigger,是指事先为某张表绑定一段代码,当表中的某些内容发生改变(增、删、改)的时候,系统会自动触发代码并执行。
     

    二、作用

    1. 可在写入数据前,强制检验或者转换数据(保证护数据安全)
    2. 触发器发生错误时,前面用户已经执行成功的操作会被撤销,类似事务的回滚
       

    三、创建触发器

    基本语法

    delimiter 自定义结束符号
    create trigger 触发器名字 触发时间 触发事件 on 表 for each row
    begin
        -- 触发器内容主体,每行用分号结尾
    end
    自定义的结束符合
    
    delimiter ;
    

    on 表 for each:触发对象,触发器绑定的实质是表中的所有行,因此当每一行发生指定改变时,触发器就会发生
     

    触发时间

    当 SQL 指令发生时,会令行中数据发生变化,而每张表中对应的行有两种状态:数据操作前和操作后

    • before:表中数据发生改变前的状态
    • after:表中数据发生改变后的状态

    PS:如果 before 触发器失败或者语句本身失败,将不执行 after 触发器(如果有的话)
     

    触发事件

    触发器是针对数据发送改变才会被触发,对应的操作只有

    • INSERT
    • DELETE
    • UPDATE
       

    注意事项

    • 在 MySQL 5 中,触发器名必须在每个表中唯一,但不是在每个数据库中唯一,即同一数据库中的两个表可能具有相同名字的触发器
    • 每个表的每个事件每次只允许一个触发器,因此,每个表最多支持 6 个触发器,before/after insert、before/after delete、before/after update
       

    例子

    1.首先创建两张表,商品表和订单表

    2.如果订单表发生数据插入,对应的商品库存应该减少。因此这里对订单表创建触发器

    语法

    delimiter ##
    -- 创建触发器
    create trigger after_insert_order after insert on orders for each row
    begin
        -- 更新商品表的库存,这里只指定了更新第一件商品的库存
        update goods set goods_num = goods_num - 1 where id = 1;
    end
    ##
    
    delimiter ;
    

    成功创建触发器
    image
     

    四、查看触发器

    1.查看全部触发器

    语法:show triggers;
     

    2.查看触发器的创建语句

    语法:show create trigger 触发器名字;

    我们来查看刚才创建的触发器
    在这里插入图片描述
     

    五、触发触发器

    基本语法:drop trigger 触发器名字

    触发不是自动手动触发的,而是在对应的事件发生后才会触发。比如我们创建的触发器,只有在对订单表进行数据操作的时候,触发器才会执行

    我们对 orders 表进行数据插入,看看是否触发了触发器
    在这里插入图片描述
    可以看到,在我们对 orders 表进行数据插入的时候,确实 goods 表 id 为 1 的商品的库存发生了改变。但是这是有问题的,即使我们买了 5 个 id 为 1 的商品,对应的 goods 表却只减了 1

    如果我们买 5 个 id 为 2 的商品,也只是 goods 表 id 为 1 的商品的发生改变,也是不正确的
    在这里插入图片描述
     

    六、删除触发器

    触发器不能修改,只能删除

    语法:drop trigger + 触发器名字

     

    七、触发器应用

    触发器针对的是数据库中的每一行记录,每行数据在操作前后都会有一个对应的状态,触发器将没有操作之前的状态保存到 old 关键字中,将操作后的状态保存到 new 中

    语法:old/new.字段名

    需要注意的是,old 和 new 不是所有触发器都有

    触发器类型new和old的使用
    INSERT型触发器没有 old,只有 new,new 表示将要(插入前)或者已经增加(插入后)的数据
    UPDATE型触发器既有 old 也有 new,old 表示更新之前的数据,new 表示更新之后的数据
    DELETE型触发器没有 new,只有 old,old 表示将要(删除前)或者已经被删除(删除后)的数据

    我们根据这个重新创建根据订单数据改变自动修改库存的触发器

    delimiter ##
    -- 创建触发器
    create trigger after_insert_order after insert on orders for each row
    begin
        -- new 代表 orders 表中新增的数据
        update goods set goods_num = goods_num - new.goods_num where id = new.goods_id;
    end
    ##
    delimiter ;
    

    PS:对于 auto_increment 列,new 在 insert 执行之前包括 0,在 insert 执行之后包括新的自动生成的值

    这里我们可以根据新插入的 orders 表中的数据来修改 goods 表的库存,此时新插入的数据用 new 来表示
    image
    如果买 5 个 id 为 1 的商品,此时 id 为 1 的商品的库存得到正确的修改。当然,如果买其他种类的商品,最后得到的结果也是正确的,这里就不一一演示了

    PS

    当然我们还需要考虑一种情况:如果此时商品的库存不够了,该怎么处理?

    delimiter ##
    -- 创建触发器
    create trigger before_insert_order before insert on orders for each row
    begin
        -- 取出 goods 表中对应 id 的库存
        -- new 代表 orders 表中新增的数据
        select goods_num from goods where id = new.goods_id into @num;
        
        -- 用即将插入的 orders 表中的库存和 goods 表中的库存进行比较
        -- 如果库存不够,中断操作
        if @num < new.goods_num then
            -- 中断操作:暴力解决,主动出错
            insert into xxx values(xxx);
        end if;
    end
    ##
    delimiter ;
    

    直接创建这个触发器

    如果我们买 id 为 3 的商品 100 件,可以看到,此时报错,同时 orders 表和 goods 表的数据并没有得到更新

    可以看到,数据连 orders 表都未能插入,那么肯定就不会执行 insert after 这个触发器了

    同时,如果在触发器中出现错误,那么前面的已经执行的操作也会全部清空
     

    八、其他

    ① mysql触发器不能对同一张表进行修改操作

    假如我在 before update 的时候作一条更新语句,随便将里面哪个字段进行更新

    delimiter //
    create trigger up before update on orders for each row
    begin
        update orders set goods_id = 10 where id = new.id;
    end;
    //
    delimiter ;
    

    触发器创建成功

    接下来我用 update 语句对 orders 表进行更新

    此时报错了,提示不能进行更新。之后,我又尝试在触发器中进行 insert 和 delete 操作,之后更新的时候还是报同样的错误

    因此说明:MySQL 的触发器中不能对本表进行 insert、update 和 delete 操作,否则会报错
     

    九、优缺点

    优点

    • 触发器可以通过数据库中的关联表实现级联更改,即一张表数据的改变会影响其他表的数据
    • 可以保证数据安全,并进行安全校验

    缺点

    • 过分依赖触发器,影响数据库的结构,增加数据库的维护成本
       

    十、参考

    https://blog.csdn.net/chl191623691/article/details/79607761
    https://blog.csdn.net/qq_35246620/article/details/78946070
    https://www.cnblogs.com/phpper/p/7587031.html

    展开全文
  • 主要介绍了MySQL触发器概念、原理与用法,结合实例形势详细分析了mysql触发器相关概念、原理、创建、用法及操作注意事项,需要的朋友可以参考下

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 86,214
精华内容 34,485
关键字:

my sql 触发器