mysql数据库触发器详解

  • 时间:
  • 浏览:8

  创建触发器。创建触发器语法如下:

  实验如下:

trigger_time标识触发时机,用before和after替换;

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

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

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

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;

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

Empty set (0.00 sec)

1) 以后before型触发器执行失败,直接goto跳到err2位置,不需要执行后续sql语录;

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

|

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

|

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=500 where planid= 1’

DROP TRIGGER [schema_name.]trigger_name

  【是因为】是因为当然与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以后更新完毕,所以出去得到的是新值。

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

4) 删除wnegative表;

5) 使用select TRIGGER_NAME from information_schema.TRIGGERS查看所有触发器,wnegative表上触发器以后不位于了;一并到var/FC_Word目录下,对应触发器的.TRN文件所以位于了;

6) 重新创建wnegative表,并增加二根wnegative中记录;如此 了wnegative表上触发器,自然所以能触发任何结果。

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

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

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

CREATE TRIGGER trigger_name trigger_time trigger_event

ON tbl_name FOR EACH ROW trigger_stmt

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

5000223 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 /!50000 ALTER TABLE wnegative DISABLE KEYS /

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

3)以后dbB上如此 FC_Output.abc表,触发器会执行失败,这时,检查一下同步情况汇报:

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

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

  还还还可以 看过IO守护进程运行正常,sql守护进程运行失败,并提示触发器运行失败的错误信息。

2.1 Mysql触发器的基本使用

1)在dbB上建立触发器:

原文网址:http://www.2cto.com/database/201202/120797.html

其中trigger_name标识触发器名称,用户自行指定;

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

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

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

tbl_name标识建立触发器的表名,即在哪张表上建立触发器;

+—————————-+————–+——+—–+———+——-+

| 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 | | | |

+—————————-+————–+——+—–+———+——-+

  以后不了解触发器的具体的信息,随还还还可以 否查看数据库上所有触发器,如下:

CREATE /!500017 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;

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;

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

  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语录触发;

trigger_event标识触发事件,用insert,update和delete替换;

再执行下面这人sql2:

mysql> select showprob from planinfo where planid=1;

+———-+

| showprob |

+———-+

| 2 |

+———-+

2) 以后sql执行失败,直接goto跳到err位置,不需要执行或许的after型触发器;

执行如下sql1:

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

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

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

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

  这人触发器守护进程有点硬长,还还还可以 单看飘黄的两句,即更新操作满足第四个多条件执行飘黄语录时,触发器的行为。触发器是建立在test_info表上的,飘黄语录中还还还可以 看过,要还还还可以 查询wext2表。

4)以后不位于showprob1列,提示错误:

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

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) 首先在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表上触发器,自然所以能触发任何结果。

  当四个多表既有before类型的触发器,又有after类型的触发器时;当二根sql语录涉及多个表的update时,sql、触发器的执行顺序经过mysql源码包装过,有时比较僵化 。

触发表中如此 planid=1的记录,sql在执行失败时,after型触发器不需要执行。

  这还还还可以 否说明一下,上述实验所使用的mysql引擎是innodb,innodb引擎也是目前线上凤巢系统、北斗系统以及哥伦布系统所使用的引擎,在innodb上所建立的表是事务性表,也所以事务安全的。“对于事务性表,以后触发守护进程失败(以及由此是因为的整个语录的失败),该语录所执行的所有更改将回滚。对于非事务性表,还还还可以 执行这人回滚”(摘自mysql使用手册)。因而,即使语录失败,失败以后所作的任何更改依然有效,也所以说,对于innodb引擎上的数据表,以后触发器中的sql或引发触发器的sql执行失效,则事务回滚,所有操作会失效。

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

2)查看触发表:

CREATE /!500017 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;

3.1 以后before类型的触发器守护进程执行失败,sql会执行成功吗?

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

+———-+

| showprob |

+——- 5)再次查看:

  这里再引出四个多小难题:有同步关系dbA?dbB,

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

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

2) 同步正常,以后drop四个多不位于的触发器,不影响运行结果;

4)以后不位于FC_Output.abc,before触发器执行失败,提示:

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

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

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

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

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

  实验如下:

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

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

  【是因为】在删除wnegative表时,mysql一并删除了wegative表上的触发器。

3.3 以后after类型的触发器守护进程执行失败,sql会回滚吗?

6. 开始语

1) 同步失败,以后不允许重复创建同表这人型的触发器;

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

trigger_stmt是触发器守护进程体;触发器守护进程还还还可以 使用begin和end作为开始和开始,上端饱含多条语录;

3)执行sql:

  【难题】联调环境中位于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表上的触发器不见了。

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

3)执行sql:

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

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

另外,在使用僵化 的sql时,以后一点僵化 的sql是mysql个人定义的,所以位于不选折 性,使用简单的sql比较可控。

5)再次查看:

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

5.1 案例1 二根sql涉及多个表的update时,触发得到update以后的旧值

  【是因为】mysql5.0.19修改表特性是,触发器消失。这是mysql5.0.19的四个多bug,在创建触发器时,会把触发器的内容保位于information_schema.TRIGGERS表中,一并在var目录下创建触发器的数据库目录下创建四个多触发器名称为前缀,以TRN为后缀的文件,当修改触发器的表时,information_schema.TRIGGERS表的内容会删除,是因为触发器消失。

3. Mysql触发器的执行顺序

3.4 mysql触发器守护进程执行的顺序

如此 planid=1的记录

【难题】表test_info上建有触发器如下:

2) 在dbB上删除四个多触发器,以后dbB上如此 对应触发器,同步情况汇报如何?

  实验如下:

  Mysql中的触发器功能以后在凤巢系统的各个模块饱含广泛应用,究其细节,还有所以值得注意的地方;本文建立在实验和案例的基础上,数据库基于线上系统使用的mysql5.0.45版本,分析发器相关的一点特殊情况汇报下msyql的防止最好的办法。

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

5)再次查看触发表:

  上述触发器实例使用了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列原值。

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

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

+———-+

| showprob |

+———-+

| 2 |

+———-+

  触发器的四个多限制是还还还可以 一并在四个多表上建立四个多相这人型的触发器。这人限制的四个多来源是触发器守护进程体的“begin和end之间允许运行多个语录”(摘自mysql使用手册)。

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.2 Msyql触发器的trigger_time和trigger_event

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

  曾经,用户就还还还可以 按照个人的还还还可以 ,查看触发器,比如使用如下语录查看上述触发器:

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

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

Empty set (0.00 sec)

Mysql饱含四个多information_schema.TRIGGERS表,存储所有库中的所有触发器,desc information_schema. TRIGGERS,还还还可以 看过表特性:

  用上述最好的办法查看触发器还还还可以 看过数据库的所有触发器,不过以后四个多库上的触发器过多,之回会刷屏,以后如此 最好的办法查看所有触发器守护进程。这时,还还还可以 采用如下最好的办法:

update planinfo set showprob=500 where planid=1; 触发触发器守护进程;

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

|

这人四个多难题还还还可以 虚实结合 同步中的insert语录和delete语录,答案所以

1) 在dbA上创建四个多触发器,以后dbB上以后有同表这人型的触发器,同步情况汇报如何?

1. 引言



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

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

2. Mysql触发器的类型

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

mysql> select showprob from planinfo where planid=1;

+———-+

| showprob |

+———-+

| 2 |

+———-+

  引起上述难题是顺序关系的,无论该表是与非 支持事务。在使用僵化 的sql时,以后一点僵化 的sql是mysql个人定义的,所以位于不选折 性,位于风险,使用简单的sql比较可控。

3.2 以后sql执行失败,会执行after类型的触发器守护进程吗?

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

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

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

5. Mysql触发器经典案例