精华内容
下载资源
问答
  • 今天偶然看到一个面试题,同一张表下如果有多个触发器,那么这些触发器的执行顺序是什么?一张表多个触发器的现象可能也是存在的,关系型数据库也没有禁用这种方式,但是还是建议不用或者少用触发器,更加不要出现...

    今天偶然看到一个面试题,同一张表下如果有多个触发器,那么这些触发器的执行顺序是什么?

    一张表多个触发器的现象可能也是存在的,关系型数据库也没有禁用这种方式,但是还是建议不用或者少用触发器,更加不要出现一张表多个触发器的情况。

    下面主要对dml触发器进行测试验证,实验环境:oracle 11.2.0.4

    基础准备

    CREATE TABLE tr_log(

    tr_id INT,

    tr_name VARCHAR2(30),

    tr_date DATE

    );

    CREATE SEQUENCE tr_seq;

    CREATE TABLE tr_table(ID INT);

    创建触发器,验证before insert

    -- tr1

    CREATE OR REPLACE TRIGGER ar1

    BEFORE INSERT ON tr_table

    FOR EACH ROW

    BEGIN

    INSERT INTO tr_log VALUES(tr_seq.nextval,'tr1',SYSDATE);

    END;

    /

    -- tr2

    CREATE OR REPLACE TRIGGER cr2

    BEFORE INSERT ON tr_table

    FOR EACH ROW

    BEGIN

    INSERT INTO tr_log VALUES(tr_seq.nextval,'tr2',SYSDATE);

    END;

    /

    -- tr3

    CREATE OR REPLACE TRIGGER br3

    BEFORE INSERT ON tr_table

    FOR EACH ROW

    BEGIN

    INSERT INTO tr_log VALUES(tr_seq.nextval,'tr3',SYSDATE);

    END;

    /

    查看执行情况

    SQL> INSERT INTO tr_table VALUES(1);

    SQL> COMMIT;

    SQL> SELECT * FROM tr_log;

    TR_ID TR_NAME TR_DATE

    ---------- ------------------------------ -----------------

    1 tr3 20-08-26 10:45:50

    2 tr2 20-08-26 10:45:50

    3 tr1 20-08-26 10:45:50

    由执行结果可以看出,功能一致的触发器是按照创建顺序逆序执行,即后创建的先执行。继续验证delete 和 update 结果是一样的。

    那么 如果是不同功能的触发器,执行情况是怎么样的?

    创建触发器

    -- tr1

    DROP TRIGGER ar1;

    CREATE OR REPLACE TRIGGER ar1

    AFTER insert ON tr_table

    FOR EACH ROW

    BEGIN

    INSERT INTO tr_log VALUES(tr_seq.nextval,'tr1',SYSDATE);

    END;

    /

    -- tr2

    DROP TRIGGER cr2;

    CREATE OR REPLACE TRIGGER cr2

    AFTER INSERT OR DELETE ON tr_table

    FOR EACH ROW

    BEGIN

    INSERT INTO tr_log VALUES(tr_seq.nextval,'tr2',SYSDATE);

    END;

    /

    -- tr3

    DROP TRIGGER br3;

    CREATE OR REPLACE TRIGGER br3

    AFTER INSERT OR DELETE OR UPDATE ON tr_table

    FOR EACH ROW

    BEGIN

    INSERT INTO tr_log VALUES(tr_seq.nextval,'tr3',SYSDATE);

    END;

    /

    查看执行情况

    SQL> INSERT INTO tr_table VALUES(1);

    SQL> COMMIT;

    SQL> SELECT * FROM tr_log;

    TR_ID TR_NAME TR_DATE

    ---------- ------------------------------ -----------------

    1 tr3 20-08-26 11:01:41

    2 tr2 20-08-26 11:01:41

    3 tr1 20-08-26 11:01:41

    看起来依然是按照创建顺序逆序执行。

    如果是创建的触发器先drop掉再重新创建,会不会改变执行顺序?

    如下:

    -- tr1

    CREATE OR REPLACE TRIGGER tr_1

    AFTER insert ON tr_table

    FOR EACH ROW

    BEGIN

    INSERT INTO tr_log VALUES(tr_seq.nextval,'tr1',SYSDATE);

    END;

    /

    -- tr2

    CREATE OR REPLACE TRIGGER tr_2

    AFTER INSERT OR DELETE ON tr_table

    FOR EACH ROW

    BEGIN

    INSERT INTO tr_log VALUES(tr_seq.nextval,'tr2',SYSDATE);

    END;

    /

    -- tr3

    CREATE OR REPLACE TRIGGER tr_3

    AFTER INSERT OR DELETE OR UPDATE ON tr_table

    FOR EACH ROW

    BEGIN

    INSERT INTO tr_log VALUES(tr_seq.nextval,'tr3',SYSDATE);

    END;

    /

    INSERT INTO tr_table VALUES(1);

    COMMIT;

    SELECT * FROM tr_log;

    DROP TRIGGER tr_1;

    DROP TRIGGER tr_2;

    DROP TRIGGER tr_3;

    -- tr2

    CREATE OR REPLACE TRIGGER tr_2

    AFTER INSERT OR DELETE ON tr_table

    FOR EACH ROW

    BEGIN

    INSERT INTO tr_log VALUES(tr_seq.nextval,'tr2',SYSDATE);

    END;

    /

    -- tr1

    CREATE OR REPLACE TRIGGER tr_1

    AFTER insert ON tr_table

    FOR EACH ROW

    BEGIN

    INSERT INTO tr_log VALUES(tr_seq.nextval,'tr1',SYSDATE);

    END;

    /

    -- tr3

    CREATE OR REPLACE TRIGGER tr_3

    AFTER INSERT OR DELETE OR UPDATE ON tr_table

    FOR EACH ROW

    BEGIN

    INSERT INTO tr_log VALUES(tr_seq.nextval,'tr3',SYSDATE);

    END;

    /

    INSERT INTO tr_table VALUES(1);

    COMMIT;

    SELECT * FROM tr_log;

    TR_ID TR_NAME TR_DATE

    ---------- ------------------------------ -----------------

    1 tr3 20-08-26 11:13:05

    2 tr2 20-08-26 11:13:05

    3 tr1 20-08-26 11:13:05

    4 tr3 20-08-26 11:13:06

    5 tr2 20-08-26 11:13:06

    6 tr1 20-08-26 11:13:06

    看起来好像跟创建顺序无关啊。那上面得出的结论看来是有问题。

    通过跟踪10046可以发现如下的sql

    SELECT DECODE(U.TYPE#, 2, U.EXT_USERNAME, U.NAME),

    O.NAME,

    T.UPDATE$,

    T.INSERT$,

    T.DELETE$,

    T.ENABLED,

    DECODE(BITAND(T.PROPERTY, 8192), 8192, 1, 0),

    DECODE(BITAND(T.PROPERTY, 65536), 65536, 1, 0),

    DECODE(BITAND(T.PROPERTY, 131072), 131072, 1, 0),

    (SELECT O.NAME

    FROM OBJ$ O

    WHERE O.OBJ# = U.SPARE2

    AND O.TYPE# = 57)

    FROM SYS.OBJ$ O, SYS.USER$ U, SYS.TRIGGER$ T, SYS.OBJ$ BO

    WHERE T.BASEOBJECT = BO.OBJ#

    AND BO.NAME = :1

    AND BO.SPARE3 = :2

    AND BO.NAMESPACE = 1

    AND T.OBJ# = O.OBJ#

    AND O.OWNER# = U.USER#

    AND O.TYPE# = 12

    AND BITAND(PROPERTY, 16) = 0

    AND BITAND(PROPERTY, 8) = 0

    ORDER BY O.OBJ#

    所以归根到底是触发器对象在SYS.OBJ$.OBJ#的排序情况决定触发器的执行顺序,通过实验发现,虽然drop掉相关触发器,并再次创建,只要触发器名一致,OBJ#并没有改变,所以最终触发器的顺序都没有改变。

    综上,触发器的执行顺序是根据SYS.OBJ$.OBJ#的顺序执行的。

    展开全文
  • oracle_form_常用Trigger的触发时机-执行顺序???? 常用Trigger的触发时机????????? Form Level:Trigger触发时机PRE-FORM启动Form, 第一BlockWHEN-NEW-FORM-INSTANCEPRE-xxx初始化结束后,焦点导航到Form时POST-...

    oracle_form_常用Trigger的触发时机-执行顺序

    ???? 常用Trigger的触发时机

    ????????? Form Level:

    Trigger触发时机PRE-FORM启动Form, 第一个BlockWHEN-NEW-FORM-INSTANCEPRE-xxx初始化结束后,焦点导航到Form时POST-FORM焦点离开Form时POST-DATABASE-COMMITForm上有数据库数据变化要提交时WHEN-TABCHANGED当Form上有Tab Canvas,Tab Page变化时触发KEY-EXITForm. WHEN-WINDOW-CLOSED点击Windowclose图示. 如果通过F4关闭则不会触发这个triggerCLOSE-WINDOW点击Windowclose图示, After WHEN-WINDOW-CLOSE

    ????????? Block Level:

    Trigger触发时机PRE-BLOCKBLOCK得到焦点,初始化之前PRE-RECORDRecord. After PRE-BLOCK, Before WHEN-NEW-BLOCK-INSTANCEWHEN-NEW-BLOCK-INSTANCEBlock获得焦点时触发WHEN-NEW-RECORD-INSTANCERecord获得焦点时触发. After WHEN-NEW-BLOCK-INSTANCEWHEN-VALIDATE-RECORDRecorditem validate通过后,block commit前触发POST-RECORD焦点离开Record触发POST-BLOCK焦点离开Block触发. After POST-RECORDPRE-INSERTInsert操作确认后commit前触发PRE-UPDATEUpdate操作确认后commit前触发PRE-DELETEDelete操作确认后commit前触发ON-INSERTInsert 操作写入DB时触发ON-UPDATEUpdate 操作写入DB时触发ON-DELETEDelete 操作写入DB时触发ON-LOCK从DB中查询出的record, 在第一个发生值变化的操作时触发,lock之后其它的值变化操作不会再触发;直到commit后自动lockPRE-QUERY在执行SQL前触发。可以用来设置数据块default_where, order_by等属性POST-QUERY执行SQL后,将查询的数据集提取到窗体记录各项之前触发????????? Item Level:

    TriggerItem Type触发时机PRE-TEXT-ITEMTextItemTextItem WHEN-NEW-ITEM-INSTANCE之前触发。隐藏的text item没有这个triggerWHEN-NEW-ITEM-INSTANCEAll可见Item得到焦点时触发

    Canvas上显示的object item是没有这个triggerPOST-CHANGEItem如果Item状态或值有变化,在焦点离开item时触发WHEN-VALIDATE-ITEM所有可编辑的Item有变化的item焦点移开时,在POST-CHANGE trigger之后触发POST-TEXT-ITEMTextItemValidate item之后触发KEY-LISTVALTextItemLOV的TextItem选择value时触发WHEN-LIST-CHANGEDLIST下拉列表选项变化时触发WHEN-RADIO-CHANGEDRadio GroupRadio group的选中radio button项变化时触发WHEN-BUTTON-PRESSEDBUTTONButton点击或确定时触发

    ?? 常见的Trigger触发顺序

    ActionSequenceTriggerLevelOpen Form(当打开FORM时)1PRE-FORMForm2PRE-BLOCKBlock3PRE-RECORDBlock4PRE-TEXT-ITEMItem5WHEN-NEW-FORM-INSTANCEForm6WHEN-NEW-BLOCK-INSTANCEBlock7WHEN-NEW-RECORD-INSTANCEBlock8WHEN-NEW-ITEM-INSTANCEItemPage Down

    /Up

    (查询出记录后翻页到前/后记录)1POST-RECORDBlock

    展开全文
  • I'm new to triggers and am getting "multiple triggers with the same action time and event for one table" error.I have created an AFTER Update and an AFTER Delete which are two separate action time/eve...

    I'm new to triggers and am getting "multiple triggers with the same action time and event for one table" error.

    I have created an AFTER Update and an AFTER Delete which are two separate action time/events so I am not really sure why I would be getting the error.

    Here is my query:

    CREATE TRIGGER `new_enrolment` AFTER INSERT ON `mdl_user_enrolments` FOR EACH ROW BEGIN

    INSERT INTO c_master (

    ud,

    firstname,

    lastname,

    email,

    username,

    cid,

    course

    )

    SELECT

    mdl_user.id AS uid,

    mdl_user.firstname,

    mdl_user.lastname,

    mdl_user.email,

    mdl_user.suspended,

    mdl_user.username,

    mdl_enrol.courseid AS cid,

    mdl_course.fullname AS course

    FROM mdl_user_enrolments INNER JOIN mdl_enrol ON mdl_user_enrolments.enrolid = mdl_enrol.id

    INNER JOIN mdl_course ON mdl_enrol.courseid = mdl_course.id

    INNER JOIN mdl_user ON mdl_user.id = mdl_user_enrolments.userid

    WHERE userid = NEW.userid;

    END;

    CREATE TRIGGER `remove_enrolment` AFTER DELETE ON `mdl_user_enrolments` FOR EACH ROW BEGIN

    SELECT mdl_enrol.courseid,

    mdl_user_enrolments.userid,

    mdl_user_enrolments.enrolid

    FROM mdl_user_enrolments INNER JOIN mdl_enrol ON mdl_user_enrolments.enrolid = mdl_enrol.id

    WHERE mdl_user_enrolments.enrolid = OLD.enrolid

    DELETE FROM c_master

    WHERE uid = OLD.userid AND mdl_enrol.courseid;

    END;

    Since I am new to all of this I am probably missing something simple.

    解决方案

    Brian, probably you've been adding and removing the same trigger a few times in your table and you have forgotten to remove it last time before create it again. Get sure you've removed it before create again using

    DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name

    and then

    CREATE TRIGGER ....

    Documentation here.

    展开全文
  • 触发器是mysql5新增的功能,目前线上凤巢系统、北斗系统以及哥伦布系统使用的数据库均是mysql5.0.45版本,很程序比如fc-star管理端,sfrd(das),dorado都会用到触发器程序,实现对于数据库增、删、改引起事件的...

    1   引言

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

    2   Mysql触发器的类型

    2.1   Mysql触发器的基本使用

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

    CREATE TRIGGERtrigger_name trigger_time trigger_eventON 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'*/ TRIGGERtrig_useracct_update

    AFTERUPDATE

    ON SF_User.useracct FOREACH ROWBEGIN

    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 triggertrigger_before_planinfo_update

    beforeupdate

    ON FC_Word.planinfo FOREACH ROWBEGIN

    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 triggertrigger_after_planinfo_update

    afterupdate

    ON FC_Word.planinfo FOREACH ROWBEGIN

    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;

    Emptyset (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;

    Emptyset (0.00 sec)

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

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

    实验如下:

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

    DELIMITER |

    create triggertrigger_after_planinfo_update

    afterupdate

    ON FC_Word.planinfo FOREACH ROWBEGIN

    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))gotoerr2;/*执行更新,如果更新失败,跳到err位置*/

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

    updated++; //更新计数器/*如果有 AFTER 触发器,则执行;如果执行失败,跳到err2位置*/

    if (table->triggers &&table->triggers->process_triggers(thd, TRG_EVENT_UPDATE, TRG_ACTION_AFTER, TRUE))gotoerr2;

    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 triggertrigger_after_planinfo_update

    afterupdate

    ON FC_Word.planinfo FOREACH ROWBEGIN

    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.00sec)

    Rows matched:1 Changed: 1 Warnings: 0

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

    Slave_IO_Running: Yes

    Slave_SQL_Running: NO

    Last_Errno:1146Last_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'*/ TRIGGERtrig_test_info_update

    AFTERUPDATE

    ON FC_Word.test_info FOREACH ROWBEGIN

    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触发器使用详解

    2021-05-11 14:43:36
    触发器是一种与表操作有关的数据库对象,当触发器所在表上出现指定事件时,将调用该对象,即表的操作事件触发表上的触发器执行。 创建触发器 在MySQL中,创建触发器语法如下: CREATE TRIGGER trigger_name ...
  • Oracle一事务中的Insert和Update执行顺序今天碰到了一奇怪的问题,是关于Oracle一事务中的Insert和Update语句的执行顺序的问题。首先详细说明下整个过程:有三张表:A,B,C,Java代码中有一段代码是先在表A中...
  • 触发器是mysql5新增的功能,目前线上凤巢系统、北斗系统以及哥伦布系统使用的数据库均是mysql5.0.45版本,很程序比如fc-star管理端,sfrd(das),dorado都会用到触发器程序,实现对于数据库增、删、改引起事件的...
  • 这里直接上结果测试结果:默认情况是触发时间先后顺序排列,触发时间比较前的先执行任务,但如果一个或多个任务同时在相同时间触发下,触发器设置优先级越高越先执行。如果优先级相同,则跟任务的存储方式有关,R...
  • Oracle DML触发器

    2021-05-04 01:29:52
    DML触发器的要点DML触发器是定义在表上的触发器,由DML事件引发。...触发的时间有BEFORE和AFTER两种,分别表示触发动作发生在DML语句执行之前和语句执行之后。* 确定触发级别,有语句级触发器和行级触发器两...
  • 安利一个多线程连续触发器的框架,项目地址ContinuousTrigger用于按序执行一系列任务,可随时绑定(如接口返回),可对每步骤设置超时响应时间。使用起来非常简单,首先注册任务ContinuousTrigger.Builder().with() ...
  • 查询分析器里好像是按顺序一条一条的执行,但在数据库内部呢?我用的生产系统有一BUG,就是物品档案里的编码进行统一转换时,会把所以数据表里用到某个编码的地方,全转换为新的编码...查询分析器里好像是按顺序一...
  • 建表以及插入数据的sql语句. 命名为:create_table.sql 放在script目录下: Set ECHO ON set define off SPOOL create_... 然后在cmd命令里面运行: C:\DDL批处理多个sql文件>install_table.bat scott/tiger .就行了..
  • MySQL之触发器

    2021-01-25 16:35:11
    触发器介绍用途:当我们希望用户在使用数据库时(增删改查),数据库会自动执行一些命令,而不是人为的去敲命令,或者是开发人员在代码里写命令。场景:我们希望用户在对表进行修改时,可以把修改同步到另一张表实现:...
  • 触发器

    2021-04-19 10:22:58
    5.7 触发器 触发器(trigger)是用户定义在关系表上的一类由事件驱动的特殊过程。一旦定义,触发器将被保存在数据库服务器中。...当特定的系统事件(如子对一表的增、删、改操作,事务的结束等)发生时,
  • oracle触发器的种类和触发事件,DML触发器,DDL事件触发器,替代触发器,查看触发器触发器的种类和触发事件触发器必须由事件才能触发。触发器的触发事件分可为3类,分别是DML事件、DDL事件和数据库事件。每类事件...
  • mysql 触发器

    2021-01-28 07:11:44
    触发器是mysql5新增的功能,目前线上凤巢系统、北斗系统以及哥伦布系统使用的数据库均是mysql5.0.45版本,很程序比如fc-star管理端,sfrd(das),dorado都会用到触发器程序,实现对于数据库增、删、改引起事件的...
  • 第十七章 使用触发器

    2021-04-06 07:51:36
    文章目录 第十七章 使用触发器定义触发器触发器的类型AFTER Triggers递归触发器Trigger Code%ok, %msg, and %oper 系统变量{fieldname}语法触发器代码中的宏`{name*O}`, `{name*N}`和`{name*C}`触发代码语法附加...
  • 数据库的触发器

    2021-01-21 18:15:29
    可以在这里创建一个触发器,每次增加一个学生的记录,就执行一次计算学生总数的操作。这样就可以保证每次增加学生的记录后,学生总数是与记录数是一致的。触发器触发的执行语句可能只有一个,也可能有多个。 创建...
  • 根据前面文章的测试,可以发现,是由于用户引发的SUSPEND事件导致一个存在编译错误的触发器执行,从这个触发器执行以后,当前会话不在尝试调用触发器。下面通过TRACE来比较三种不同的情况:SQL> DROP TABLE T_...
  • 第九章 触发器定义

    2021-07-23 08:36:33
    文章目录 第九章 触发器定义介绍...每个事件可以有多个触发器,只要它们被分配了执行顺序。 可以向持久类添加触发器定义。它们在其他类中没有意义。 详情 触发器定义具有以下结构: /// description Trigger name [ key
  • 一、 外键外键创建就为 两表创建了约束, 外键字段不能填写另一表不存在的字段值。另一表称为 字典表1、MySQL中“键”和“索引”的定义相同,所以外键和主键一样也是索引的一种。不同的是MySQL会自动为所有表...
  • 事务 :多个sql语句 作为原子性操作,只要某一个sql语句出错,则回滚到最初的状态,保证数据库的完整性 包含事务的存储过程:类似于try except delimiter $$drop procedure if existsp1;create PROCEDUREp1( OUT p_...
  • PostgreSQL中的触发器

    2021-04-05 23:11:18
    触发器函数可使用大部分可用的过程语言编写,包括PL/pgSQL(参见第42章),PL/Tcl(参见第43章),PL/Perl(参见第44章),和PL/Python(参见第45章)。阅读完本章后,可根据自己的喜好,到相应章节查看对应语言编写...
  • Oracle触发器

    2021-03-05 17:56:01
    触发器是一种特殊类型的存储计程,编译后存储在数据库服务器中,当特定事件发生时,由系统自动调用执行,而不能由应用程序显式地调用执行。此外,触发器不接受任何参数。主要用于护那些通过创建表时的声明约束不可能...
  • Oracle触发器的使用

    2021-05-04 06:29:42
    Oracle触发器的使用触发器是指存放在数据库中,并被隐藏执行的存储过程。在Oracle8i之前,只允许基于表或视图的DML操作(insert,update,delete)建立触发器,在oracle8i之后,不仅支持DML操作,也允许基于系统事件...
  • PostgreSQL触发器

    2020-12-22 13:12:12
    PostgreSQL的触发器是数据库自动执行\指定的数据库事件发生时调用的回调函数。以下是有关PostgreSQL的触发器的要点: www.yiibai.comPostgreSQL的触发可以指定触发操作前尝试一行(在检查约束之前INSERT,UPDATE或...
  • 系统触发器可以用系统触发器记录一些ddl的数据操作或者是数据库的登录 或者登出操作。语法:create or replace trigger 触发器名称before/after 触发器时机事件名称 例如logon ...举例:创建一系统触发器,记录sc...
  • 目录SQL Server触发器学习(一)一、创建新表并插入初始数据二、触发器概述三、触发器语法四、前触发器示例五、激活触发器六、删除/修改触发器七、后触发器示例 SQL Server触发器学习(一) 一、创建新表并插入初始...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 51,917
精华内容 20,766
关键字:

多个触发器执行顺序