Database trigger details _ MySQL

Database trigger details bitsCN. com1 introduction

Like the stored procedure, Mysql triggers are embedded into a mysql program. The trigger is a new function of mysql5. Currently, the online Fengchao system, Beidou system, and Columbus system use the database of mysql5.0.45. many programs such as fc-star Management terminal, sfrd (das ), all dorado uses the trigger program to associate the events caused by database addition, deletion, and modification. This article introduces the trigger type and basic usage methods, describes the mistakes that may occur when using the trigger, and obtains the trigger execution sequence conclusion from the mysql source code, the last part of this article is a typical trigger case. Unless otherwise specified, this experiment is based on MySQL 5.0.45.

2. Mysql trigger type

2.1 Basic usage of Mysql triggers

Create a trigger. The trigger creation syntax is as follows:

Create trigger trigger_name trigger_time trigger_event
ON tbl_name for each row trigger_stmt

Trigger_name indicates the trigger name, which you can specify;

Trigger_time indicates the Trigger Time, which must be replaced by before and after;

Trigger_event identifies the trigger event and replaces it with insert, update, and delete;

Tbl_name identifies the table name on which the trigger is created;

Trigger_stmt is the trigger program Body. the trigger program can use begin and end as the start and end, with multiple statements in the middle;

An sfrd trigger instance is provided 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 instance uses the OLD and NEW keywords. OLD and NEW can reference a column in the table where the trigger is located. in the above example, OLD. ulevelid indicates the value of the ulevelid column before the table SF_User.useracct is modified, NEW. ulevelid indicates the value of the ulevelid column after the table SF_User.useracct is modified. In addition, if it is an insert trigger, NEW. ulevelid also indicates the ulevelid column value of the new row in the table SF_User.useracct; if it is a delete trigger OLD. ulevelid also indicates the original value of the ulevelid column in the table SF_User.useracct to delete rows.

In addition, the OLD column is read-only, and the NEW column can be assigned again in the trigger program.

The above example also uses keywords such as IF, THEN, ELSE, and end if. In the trigger Program body, you can use the sequence, judgment, loop, and other statements between beigin and end to implement the logical functions required by the general program.

View the trigger. View the trigger syntax as follows:

Show triggers from SF_User like "usermaps %"; // view the trigger whose name matches usermaps % on the SF_User Library

If you do not know the trigger details, or you need to view all the triggers in the database, as follows:

Show triggers; // view all TRIGGERS

You can view all the triggers in the database by using the above method. However, if there are too many triggers in a database, you may not be able to view all the trigger programs due to screen flushing. In this case, you can use the following method:

Mysql has a information_schema.TRIGGERS table that stores all the TRIGGERS in all databases and desc information_schema. TRIGGERS. the table structure is displayed:

+ ---------------------------- + -------------- + ------ + ----- + --------- + ------- +
| 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, you can view the trigger as needed. for example, you can use the following statement to view the trigger:

Select * from information_schema. TRIGGERS where TRIGGER_NAME = 'Trig _ useracct_update '/G;

Delete a trigger. The delete trigger syntax is as follows:

Drop trigger [schema_name.] trigger_name

2.2 trigger_time and trigger_event of the Msyql trigger

Now, I have noticed the trigger_time and trigger_event again. as mentioned above, trigger_time can be replaced by before and after, indicating whether the trigger program is executed before or after the SQL statement is executed; trigger_event can be insert, update, delete replacement, which indicates the type of SQL that triggers the trigger program.

A maximum of six triggers can be created on a table, namely, 1) before insert, 2) before update, 3) before delete, 4) after insert, 5) after update, 6) after delete type.

One limitation of a trigger is that two triggers of the same type cannot be created on a table at the same time. One source of this restriction is the "allow multiple statements between begin and end" of the trigger Program body (from the mysql User Manual ).

In addition, in addition to defining basic insert, update, and delete operations, msyql also defines load data and replace statements, the load data and replace statements can also trigger the triggers of the above 6 types.

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 similar to the insert statement, but when the table has a primary key and unique index, if the inserted data is consistent with the original primary key and unique index, the original data will be deleted first, then add a new data. that is to say, a replace SQL statement is sometimes equivalent to an insert SQL statement, and sometimes equivalent to a delete SQL statement plus an insert SQL statement. That is:
? Insert trigger: it may be triggered by an insert statement, a load data statement, or a replace statement;
? Update trigger: it may be triggered by an update statement;
? Delete trigger: it may be triggered by the delete statement and the replace statement;

3. execution sequence of Mysql triggers

Several issues related to the trigger first

3.1 if the before Trigger fails to be executed, will the SQL statement be executed 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) because the FC_Output.abc does not exist, the before Trigger fails to be executed. the prompt is:

ERROR 1146 (42S02): Table 'FC _ Output. ABC' doesn' t exist

5) Check again:

Mysql> select showprob from planinfo where planid = 1;
+ ---------- +
| Showprob |
+ ---------- +
| 2 |
+ ---------- +

The SQL statement is not successfully modified. That is, if the before Trigger fails to be executed, the SQL statement also fails.

3.2 If SQL execution fails, will the after 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)

No records with planid = 1

3) execute SQL:

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

4) because 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 planid = 1 record in the trigger table. If an SQL statement fails to be executed, the after trigger will not be executed.

3.3 if an after-type trigger fails to be executed, will the SQL be rolled 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 the trigger program;

4) because FC_Output.abc does not exist, the after trigger fails to be executed. the following message is displayed:

ERROR 1146 (42S02): Table 'FC _ Output. ABC' doesn' t exist

5) Check again:

Mysql> select showprob from planinfo where planid = 1;
+ ---------- +
| Showprob |
+ ---------- +
| 2 |
+ ---------- +

The SQL statement is not successfully modified. That is, if the after trigger fails to be executed, the SQL statement will be rolled back.

The mysql engine used in the above experiment is innodb, and the innodb engine is also used by the online Fengchao system, Beidou system, and Columbus system, the table created on innodb is a transactional table, that is, transaction security. "For a transaction table, if the triggering program fails (and the entire statement fails as a result), all changes executed by the statement will be rolled back. This type of rollback is not allowed for non-transactional tables (from the mysql User Manual ). Therefore, any changes made before the statement fails are valid even if the statement fails. that is to say, if the SQL statement in the trigger or the SQL statement that triggers the trigger fails to be executed for the data table on the innodb engine, the transaction is rolled back, and all operations will become invalid.

3.4 mysql trigger execution sequence

When a table has both before triggers and after triggers, when an SQL statement involves update of multiple tables, the execution sequence of SQL and trigger is encapsulated by mysql source code and sometimes complicated.

Let's take a look at the source code of mysql. when updating multiple tables in SQL, the execution process of Mysql is as follows (no irrelevant code is saved ):

/* Traverse all tables to be updated */
For (cur_table = update_tables; cur_table = cur_table-> next_local)
{
Org_updated = updated
/* If a BEFORE trigger exists, it will be executed. if the execution fails, it will jump to the err2 position */
If (table-> triggers &&
Table-> triggers-> process_triggers (thd, TRG_EVENT_UPDATE, TRG_ACTION_BEFORE, TRUE ))
Goto err2;
/* Execute the update. if the update fails, jump to the err location */
If (local_error = table-> file-> update_row (table-> record [1], table-> record [0])
Goto err;
Updated ++; // updates the counter.
/* If an AFTER trigger exists, it will be executed. if the execution fails, it will jump to the err2 position */
If (table-> triggers &&
Table-> triggers-> process_triggers (thd, TRG_EVENT_UPDATE, TRG_ACTION_AFTER, TRUE ))
Goto err2;
Err:
{
/* Mark error messages, write logs, etc */
}
Err2:
{
/* Restore the executed operation */
Check_opt_it.rewind ();
/* If an update is executed and the table has Transactions, mark it */
If (updated! = Org_updated)
{
If (table-> file-> has_transactions ())
Transactional_tables = 1;
}
}
}

From the code above, you can find the answer to the question thrown at the beginning of this chapter.

1) if the before Trigger fails to be executed, go directly to the err2 position without executing subsequent SQL statements;

2) If SQL execution fails, go directly to the err location without executing a possible after trigger;

3) If an after trigger fails to be executed, go to the err2 location, restore the executed operation, and mark the transaction table.

In addition, when complicated SQL statements are used, some complicated SQL statements are defined by mysql itself, so there is uncertainty. simple SQL statements are controllable.

4 Performance of Mysql triggers in database synchronization

4.1 when the trigger fails to run, will database synchronization fail?

Is there a synchronization relationship between the following DBAs? DbB. The synchronization is normal at the beginning.

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) the SQL statement is successfully executed on dbA;

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) because the table FC_Output.abc does not exist on dbB, the trigger fails to execute. in this case, 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, the SQL thread fails to run, and the error message indicating the trigger failed to run is displayed.

Recall the parts described in sections 3.1 and 3.3. for both before and after triggers, when the trigger fails, the corresponding SQL statement fails to be executed for the innodb engine, therefore, database synchronization also fails.

4.2 create or delete a trigger to write bin-log

The statement for creating and deleting a trigger will also be written to the bin-log, so it will be synchronized to the downstream database like the insert, update, and delete statements, that is, the upstream creation trigger, the downstream will also be created.

There are two small questions: Is there a synchronization relationship between DBAs? DbB,

1) create a trigger on dbA. if dbB already has a trigger of the same type as the table, 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 analogous to the insert statement and delete statement in synchronization. The answer is:

1) synchronization failed because repeated triggers of the same table type cannot be created;

2) synchronization is normal, because drop a non-existent trigger does not affect the running result;

5 Typical Cases of Mysql triggers

5.1 Case 1 when an SQL statement involves update of multiple tables, the old value before update is obtained.

[Symptom] a trigger is created on 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 logic is Omitted */
End if;
END;

This trigger program is a little long. you can view the two yellow statements, that is, the trigger action when the update operation meets the first condition to execute the yellow statements. The trigger is created on the test_info table, which can be seen in the yellow statements. you also need to query the wext2 table.

Execute the following sql1:

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

We can see that the SQL statement modifies both the test_info2 table and the wext2 table. The program is designed to trigger the new wext2 table wl field modified value (that is, NULL). However, the experiment shows that, after the preceding SQL statement is executed, the wurl queried by the trigger program is the old value before the SQL statement is modified.

Then execute the following statement similar to sql2:

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

After the preceding SQL statement is executed, the wurl queried by the trigger program is the new value after the SQL statement is modified.

[Cause] The reason is of course not related to the alias a and B in SQL, but to the writing sequence of wext2 and test_info tables. As described in section 3.4, when an SQL statement involves update operations on multiple tables, the execution sequence of data table fields and triggers is packaged by mysql source code. When SQL 1 is executed, the test_info update, the after trigger, and the wext2 update are executed first. that is to say, wext2 is not updated when the after trigger is executed, therefore, the old value is triggered. When SQL 2 is executed, wext2 update, test_info update, and after trigger are executed first. that is to say, wext2 has been updated when the after trigger is executed, so what we get is a new value.

The above phenomenon is caused by a sequential relationship, whether or not the table supports transactions. When complicated SQL statements are used, some complicated SQL statements are defined by mysql, so there are uncertainties and risks. simple SQL statements are controllable.

5.2 Case 2: The Trigger becomes invalid after the table structure is modified in mysql5.0.19

[Symptom] an after trigger is created on the userpref table. after the foreign key association of the userpref table is modified, the new record in the userpref table is not triggered, that is, the trigger is invalid.

[Cause] mysql5.0.19 modifying the table structure is that the trigger disappears. This is a bug in mysql5.0.19. when creating a trigger, the trigger content is saved in the information_schema.TRIGGERS table, and a trigger name prefix is created under the database directory where the trigger is created under the var directory, objects suffixed with TRN. when the trigger table is modified, the content of the information_schema.TRIGGERS table is deleted, causing the trigger to disappear.

In MySQL 5.0.45, this bug has been fixed. The trigger of MySQL 5.0.45 does not expire, whether it is to modify the table index, foreign key, or change the table field.

5.3 Case 3: trigger failure after data table deletion

[Symptom] is there a dbA in the joint debugging environment? DbB: no trigger exists on the dbA of the master database. in the FC_Word.wnegative table on the slave database dbB, a trigger is built on the FC_Word.wbuget table. the trigger starts to run normally and does not perform any direct operations on the slave database, one day, we found that the modification to the wnegative table could not be triggered. Check the Slave Database Status. the synchronization is normal. you can use select TRIGGER_NAME from information_schema.TRIGGERS to find that the trigger on the wnegative table disappears. wnegative is not available in the var/FC_Word directory. TRN file. the trigger on the wnegative table is missing.

[Analysis] find 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 );

We can see that the wnegative table was deleted at 100223 18:27:45, followed by the wnegative table created. we found that the modification to wnegative was not triggered after 100223 18:27:45, before that, modification to wnegative is triggered normally. Therefore, we suspect that the deletion of the wnegative table will also delete the trigger on the wnegative table. Deleting a wnegative table is performed on the dbA of the master database and synchronized to dbB.

[Cause] when deleting the wnegative table, mysql also deletes the trigger on the wegative table.

The following experiment proves the above speculation:

1) create an after insert trigger in wnegative;
2) add a record in wnegative;
3) check the result and find that the trigger is correctly triggered;
4) delete the wnegative table;
5) Use select TRIGGER_NAME from information_schema.TRIGGERS to view all Triggers. the triggers on the wnegative table do not exist. at the same time, the. TRN file of the corresponding trigger does not exist in the var/FC_Word directory;
6) re-create the wnegative table and add a record in wnegative. without the wnegative table trigger, no results can be triggered.

6 Conclusion

The trigger function in Mysql has been widely used in various modules of the Fengchao system. The details are worth noting. This article is based on experiments and cases, based on MySQL 5.0.45 used by the online system, the database analyzes the msyql processing methods related to triggers in some special cases.


From Lei. Y's blog bitsCN.com