1 Introduction
Mysql triggers and stored procedures are both programs embedded in mysql. Triggers are a new function of mysql5. Currently, the databases used by the online Fengchao system, Beidou system, and Columbus system are all mysql5.0.45 versions. Many programs such as fc-star management terminal, sfrd (das), and dorado will use triggers. The server program implements associated operations on events caused by database additions, deletions, and modifications. This article introduces the types and basic usage of triggers, describes the misunderstandings that are easy to occur in the use of triggers, and draws conclusions about the execution order of triggers from the mysql source code. The end of this article is a classic case of triggers encountered in actual combat. Unless otherwise specified, the experiments in this article are based on mysql5.0.45 version.
2 Types of Mysql triggers
2.1 Basic use of Mysql triggers
Create a trigger. The syntax for creating a trigger is as follows:
CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt
Among them, trigger_name identifies the trigger name, which is specified by the user;
trigger_time identifies the triggering time and is replaced with before and after;
trigger_event identifies the trigger event and is replaced by insert, update and delete;
tbl_name identifies the table name on which the trigger is created, that is, on which table the trigger is created;
trigger_stmt is the trigger program body; the trigger program can use begin and end as the beginning and end, and contains multiple statements in the middle;
An example of a trigger for sfrd is given below:
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;
The above trigger examples use the OLD keyword and NEW keyword. OLD and NEW can refer to a certain column in the table where the trigger is located. In the above example, OLD.ulevelid represents the value of the ulevelid column before the table SF_User.useracct is modified, and NEW.ulevelid represents the value of the ulevelid column after the table SF_User.useracct is modified. In addition, if it is an insert type trigger, NEW.ulevelid also represents the ulevelid column value of the newly added row in the table SF_User.useracct; if it is a delete type trigger, OLD.ulevelid also represents the original value of the ulevelid column of the deleted row in the table SF_User.useracct.
In addition, the OLD column is read-only, and the NEW column can be assigned again in the trigger program.
The above examples also use keywords such as IF, THEN, ELSE, END IF, etc. In the trigger program body, between begin and end, you can use sequence, judgment, loop and other statements to implement the logical functions required by general programs.
View triggers. Check the trigger syntax as follows. If you know the database where the trigger is located, as well as specific information such as the trigger name:
SHOW TRIGGERS from SF_User like "usermaps%"; //View the triggers on the SF_User library whose names match usermaps%
If you don’t know the specific information of triggers, or you need to view all triggers on the database, as follows:
SHOW TRIGGERS; //View all triggers
You can view all triggers in the database by viewing triggers in the above way. However, if there are too many triggers on a library, it may not be possible to view all trigger programs because the screen will refresh. At this time, you can use the following methods:
There is an information_schema.TRIGGERS table in Mysql, which stores all triggers in all libraries, desc information_schema.TRIGGERS, you can see the table structure:
+ ---------------------------- + --------------- + -- ---- + ----- + --------- + ------- + | 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 | | | | + --------------------------- + -------------- + ----- - + ----- + --------- + ------- +
In this way, users can view the triggers according to their own needs, such as using the following statement to view the above triggers:
select * from information_schema. TRIGGERS where TRIGGER_NAME= 'trig_useracct_update'\G;
Delete the trigger. The delete trigger syntax is as follows:
DROP TRIGGER [schema_name.]trigger_name
2.2 Trigger_time and trigger_event of Msyql trigger
Now, notice trigger_time and trigger_event again. As mentioned above, trigger_time can be replaced with before and after, indicating whether the trigger program is executed before or after SQL execution; trigger_event can be replaced with insert, update, delete, indicating the trigger program. Under what type of sql will it be triggered.
A maximum of 6 triggers can be created on a table, namely 1) before insert type, 2) before update type, 3) before delete type, 4) after insert type, 5) after update type, 6) after delete type.
One limitation of triggers is that you cannot create two triggers of the same type on a table at the same time. One source of this limitation is that “multiple statements are allowed to run between begin and end” in the trigger program body (extracted from the MySQL User Manual).
Another thing to note is that in addition to defining the basic operations of insert, update, and delete, msyql also defines load data and replace statements, and load data and replace statements can also cause the triggering of the above 6 types of triggers.
The Load data statement is used to load a file into a data table, which is equivalent to a series of insert operations. The replace statement is generally very similar to the insert statement, except that when there is a primary key and a unique index in the table, if the inserted data is consistent with the original primary key and unique index, the original data will be deleted first, and then a new piece of data will be added; In other words, a replace sql is sometimes equivalent to an insert sql, and sometimes it is equivalent to a delete sql plus an insert sql. That is:
? Insert type trigger: may be triggered by insert statement, load data statement, replace statement;
? Update trigger: may be triggered by update statement;
? Delete type trigger: may be triggered by delete statement or replace statement;
3 Mysql trigger execution order
Let’s first raise some issues related to triggers
3.1 If the before type trigger program fails to execute, will SQL execute successfully?
The experiment is as follows:
1) Create a before trigger in FC_Word.planinfo:
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) View:
mysql> select showprob from planinfo where planid=1; + ---------- + | showprob | + ---------- + | 2 | + ---------- +
3) Execute sql:
update planinfo set showprob=200 where planid=1; // Trigger the trigger program;
4) Since FC_Output.abc does not exist, the before trigger fails to execute, prompting:
ERROR 1146 (42S02): Table ‘FC_Output.abc’ doesn’t exist
5) Check again:
mysql> select showprob from planinfo where planid=1; + ---------- + | showprob | + ---------- + | 2 | + ---------- +
That is, the modified sql was not successfully executed. That is, if the before trigger fails to execute, the sql will also fail to execute.
3.2 If SQL execution fails, will the after type trigger program be executed?
The experiment is as follows:
1) Create an after trigger in FC_Word.planinfo:
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) View the trigger table:
mysql> select * from FC_Output.fcevent where planid=1; Empty set (0.00 sec)
There is no record of planid=1
3) Execute sql:
mysql> update planinfo set showprob1=200 where planid=1;
4) Since the showprob1 column does not exist, an error is prompted:
ERROR 1054 (42S22): Unknown column ‘showprob1’ in ‘field list’
5) View the trigger table again:
mysql> select * from FC_Output.fcevent where planid=1; Empty set (0.00 sec)
There is no record with planid=1 in the trigger table. When SQL fails to execute, the after-type trigger will not be executed.
3.3 If the after type trigger program fails to execute, will SQL roll back?
The experiment is as follows:
1) Create an after trigger in FC_Word.planinfo:
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) View:
mysql> select showprob from planinfo where planid=1; + ---------- + | showprob | + ---------- + | 2 | + ---------- +
3) Execute sql:
update planinfo set showprob=200 where planid=1; #Trigger trigger program;
4) Since FC_Output.abc does not exist, the after trigger fails to execute, prompting:
ERROR 1146 (42S02): Table ‘FC_Output.abc’ doesn’t exist
5) Check again:
mysql> select showprob from planinfo where planid=1; + ---------- + | showprob | + ---------- + | 2 | + ---------- +
That is, the modified sql was not successfully executed. That is, if the after trigger fails to execute, SQL will roll back.
It needs to be explained here that the MySQL engine used in the above experiments is innodb. The innodb engine is also the engine currently used by the online Fengchao system, Beidou system and Columbus system. The tables created on innodb are transactional tables, that is, transactions safe. “For transactional tables, if the trigger fails (and the resulting failure of the entire statement), all changes performed by the statement will be rolled back. For non-transactional tables, this type of rollback cannot be performed” (excerpt from mysql usage manual). Therefore, even if the statement fails, any changes made before the failure are still valid. That is to say, for the data table on the InnoDB engine, if the SQL in the trigger or the SQL that triggers the trigger fails, the transaction will be rolled back and all operations will Invalid.
3.4 The order of execution of mysql trigger program
When a table has both before-type triggers and after-type triggers; when a SQL statement involves updates to multiple tables, the execution order of SQL and triggers is packaged by MySQL source code, which is sometimes more complicated.
You can first look at a piece of mysql source code. When updating multiple tables in SQL, the execution process of Mysql is as follows (omitting irrelevant code):
/* Traverse all tables to be updated */
for (cur_table= update_tables; cur_table; cur_table= cur_table->next_local)
{
?org_updated = updated
/* If there is a BEFORE trigger, execute it; if the execution fails, jump to the err2 position */
?if (table->triggers & amp; & amp; table->triggers->process_triggers(thd, TRG_EVENT_UPDATE,TRG_ACTION_BEFORE, TRUE))
goto err2;
/*Execute update, if update fails, jump to err position*/
?if(local_error=table->file->update_row(table->record[1], table->record[0])))
goto err;
updated + + ; // Update counter
/* If there is an AFTER trigger, execute it; if the execution fails, jump to the err2 position */
?if (table->triggers & amp; & amp;table->triggers->process_triggers(thd, TRG_EVENT_UPDATE, TRG_ACTION_AFTER, TRUE))
goto err2;
Error:
{
/*Mark error information, write logs, etc.*/
}
Error2:
{
/*Restore the performed operations*/
check_opt_it.rewind();
/*If an update is performed and the table has a transaction, mark it*/
if (updated != org_updated)
{
if (table->file->has_transactions())
transactional_tables= 1;
}
}
}
The answer to the question posed at the beginning of this chapter can be found in the code above. 1) If the execution of the before type trigger fails, goto will directly jump to the err2 position, and subsequent sql statements will not be executed; 2) If the sql execution fails, goto will directly jump to the err position, and no subsequent sql statements will be executed. Execute Subsequent after-type trigger; 3) If the after trigger fails to execute, goto to the err2 position, restore the executed operation, and on the transactional table to mark.
In addition, when using complex SQL, since some complex SQL is defined by MySQL itself, there is uncertainty. Using simple SQL is more controllable.
4 Performance of Mysql triggers in database synchronization
4.1 When the trigger fails to run, will database synchronization fail?
There is a synchronization relationship as follows dbA?dbB. Synchronization is normal initially.
1) Create a trigger on 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) Execute sql on dbA and the execution is successful;
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) Since there is no FC_Output.abc table on dbB, the trigger will fail to execute. At this time, check the synchronization status:
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'
You can see that the IO thread runs normally, but the sql thread fails, and an error message indicating that the trigger fails to run is prompted.
Recall parts 3.1 and 3.3, whether it is a before part trigger or an after type trigger, for the innodb engine, when the trigger fails to execute, the corresponding SQL will also fail to execute, so database synchronization will also fail.
4.2 Create and delete triggers and write bin-log
Statements for creating and deleting triggers will also be written to the bin-log, so they will be synchronized to the downstream database like normal insert, update, and delete statements. That is, when the trigger is created upstream, it will also be created downstream.
Here are two more small questions: there is a synchronization relationship dbA?dbB,
1) Create a trigger on dbA. If there is already a trigger of the same table and type on dbB, what is the synchronization status?
2) Delete a trigger on dbB. If there is no corresponding trigger on dbB, what is the synchronization status?
These two questions can be compared to the insert statement and delete statement in synchronization. The answer is1) Synchronization fails because repeated creation of triggers of the same type in the same table is not allowed;2) Synchronization is normal. Because dropping a non-existent trigger does not affect the running results;
5 classic cases of Mysql triggers
5.1 Case 1 When a SQL statement involves the update of multiple tables, the old value before the update is triggered.
[Phenomena] There are triggers built on the table test_info as follows:
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; /*. . . The rest of the logic is omitted*/ END IF; END;
This trigger program is a bit long. You can just look at the two sentences of Piaohuang, that is, the behavior of the trigger when the update operation satisfies the first condition and executes the Piaohuang statement. The trigger is built on the test_info table, which can be seen in the piaohuang statement, and the wext2 table also needs to be queried.
Execute the following 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;
It can be seen that both the test_info2 table and the wext2 table have been modified in the sql. The original intention of the program is to trigger the modified new value (i.e. NULL) of the wext2 table wl field; however, the experiment shows that after executing the above sql, the trigger program queries The wurl is the old value before the sql modification.
Then execute sql2 similar to the following:
Update wext2 a, test_info2 b set b.th=(b.th + 1), b.w4=(b.w4 & amp;8), a.wl=NULL where a.wid=b.wid and a.wid=142394379;
Experiments show that after executing the above sql, the wurl queried by the trigger program is the new value after the sql is modified.
[Cause] The reason is of course not related to the aliases a and b in sql, but to the writing order of wext2 table and test_info table. As mentioned in Section 3.4 of this article, when a SQL statement involves update operations on multiple tables, the data table fields and trigger execution order are packaged by the MySQL source code. When executing the above sql1, the update of test_info is first executed, then the after trigger, and finally the update of wext2. That is to say, when the after trigger is executed, wext2 has not been updated, so the old value is obtained by triggering. When executing sql2, the wext2 update is executed first, then the test_info update, and finally the after trigger. That is to say, when the after trigger is executed, wext2 has been updated, so the new value is obtained.
The above phenomenon is caused by a sequential relationship, regardless of whether the table supports transactions. When using complex SQL, because some complex SQL is defined by MySQL itself, there are uncertainties and risks. Using simple SQL is more controllable.
5.2 Case 2 The trigger fails after the table structure is modified in mysql5.0.19 version
[Phenomena] There is an after type trigger built on the userpref table. After modifying the foreign key association of the userpref table, the new record in the userpref table is not triggered, that is, the trigger fails.
[Cause] The trigger disappears when mysql5.0.19 modifies the table structure. This is a bug in mysql5.0.19. When creating a trigger, the contents of the trigger will be saved in the information_schema.TRIGGERS table, and a trigger name will be prefixed in the database directory where the trigger is created in the var directory. For files with a TRN suffix, when the trigger table is modified, the contents of the information_schema.TRIGGERS table will be deleted, causing the trigger to disappear.
In mysql5.0.45 version, this bug has been fixed. The trigger of Mysql5.0.45 version will not expire whether it is modifying the table’s index, foreign key, or changing the table field.
5.3 Case 3 The trigger fails after deleting the data table
[Phenomena] There is dbA?dbB in the joint debugging environment. There is no trigger on the main database dbA. There are triggers on the FC_Word.wnegative table and FC_Word.wbuget table on the slave database dbB. The trigger starts to run normally, but there is no trigger during the period. Any direct operation from the library will one day find that modifications to the wnegative table cannot be triggered. Check the status of the slave library, and the synchronization is normal; use select TRIGGER_NAME from information_schema.TRIGGERS to find that the triggers on the wnegative table have disappeared; there is no wnegative.TRN file in the var/FC_Word directory, and the triggers on the wnegative table have disappeared.
[Analysis] Search the query log of dbB and find one:
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);
It can be seen that at 100223 18:27:45, the table wnegative was deleted, and then the table wnegative was created; looking up the trigger table, it was found that the modification to wnegative was not triggered after 100223 18:27:45, and after This previous modification to wnegative was triggered normally. Therefore, it is suspected that the deletion of the wnegative table will also delete the triggers on the wnegative table. The deletion of the wnegative table is performed on the main database dbA and then synchronized to dbB. [Cause] When deleting the wnegative table, mysql also deleted the trigger on the wegative table.
The above guess can be proven through the following experiment:
1) First create an after insert type trigger in wnegative;
2) Add a wnegative record;
3) Check the results and find that the trigger is triggered correctly;
4) Delete the wnegative table;
5) Use select TRIGGER_NAME from information_schema.TRIGGERS to view all triggers. The triggers on the wnegative table no longer exist; at the same time, go to the var/FC_Word directory and the .TRN file corresponding to the trigger does not exist either;
6) Re-create the wnegative table and add a record in wnegative; without the trigger on the wnegative table, naturally no results can be triggered.
6 Conclusion
The trigger function in Mysql has been widely used in various modules of the Fengchao system. Looking into the details, there are many things worth noting; this article is based on experiments and cases, and the database is based on mysql5 used in the online system. Version 0.45 analyzes the handling of msyql in some special cases related to triggers.
Reprint address: http://blog.csdn.net/aaa1117a8w5s6d/article/details/8513220