Shulou(Shulou.com)06/01 Report--

This article mainly explains how to use MySQL trigger, the content is clear, interested friends can learn, I believe it will be helpful after reading.

1. MySQL trigger creation:

1. Syntax for creating MySQL triggers:

CREATE [DEFINER= {'user' | CURRENT_USER}] TRIGGER trigger_nametrigger_time trigger_eventON table_nameFOR EACH row [trigger _ order] trigger_body2, MySQL creation syntax keywords explanation: field meaning possible value DEFINER= optional parameter, specify creator, default is currently logged in user (CURRENT_USER)

The trigger will be executed by the user specified by this parameter, so the permission issue needs to be considered; DEFINER='root@%'

DEFINER=CURRENT_USERtrigger_name trigger name, preferably composed of table name + trigger event keyword + trigger time keyword; trigger_time trigger time, before or after an event; BEFORE and AFTERtrigger_event trigger events, such as trigger on insert or delete

INSERT: insert operation trigger, triggered when INSERT, LOAD DATA, REPLACE

UPDATE: update operation trigger, triggered during UPDATE operation

DELETE: delete operation trigger, triggered during DELETE or REPLACE operation; table name of INSERT, UPDATE, DELETEtable_name trigger operation time; trigger_order optional parameter, if multiple triggers with the same trigger event and trigger time are defined (

For example, BEFORE UPDATE), the default trigger sequence is the same as the trigger creation order, and you can

Use this parameter to change their trigger order. This parameter has been supported by mysql since 5.7.2.

FOLLOWS: the current creation trigger is activated after the existing trigger

PRECEDES: the current creation trigger is activated before the existing trigger; the content of the SQL statement triggered by FOLLOWS and PRECEDEStrigger_body usually starts with begin and ends with end begin.. End

3. Trigger OLD,NEW in the content of the execution statement (trigger_body):

In trigger_body, we can use NEW to represent the new row to be inserted (equivalent to MS SQL's INSERTED), and OLD to represent the old row to be deleted (equivalent to MS SQL's DELETED). Obtain their field contents from OLD,NEW to facilitate their use in trigger operations. The following is the corresponding relationship of whether OLD and NEW are supported for corresponding events:

Event OLDNEWINSERT × √ DELETE √ × UPDATE √√

Because UPDATE is equivalent to deleting the old row (OLD) and then inserting a new row (NEW), UPDATE supports both OLD and NEW

4. MySQL delimiter (DELIMITER):

MySQL defaults to ";" as the delimiter, and the SQL statement commits when it encounters ";". However, there may be multiple ";" characters in our triggers. In order to prevent the trigger creation statement from being submitted prematurely, we need to temporarily modify the MySQL delimiter and then change the delimiter back after creation. You can modify the delimiter using DELIMITER, as follows:

DELIMITER $...-trigger creation statement; $- submit creation statement; DELIMITER; second, MySQL trigger creation advanced:

1. Use variables in MySQL triggers:

The variable variable in the MySQL trigger is preceded by'@'. There is no need to define it, and you can directly use:

-- variable direct assignment set @ num=999;-- assignment using the data queried by the select statement requires parentheses: set @ name = (select name from table). 2. If language is used in MySQL triggers to determine the condition:

-- simple if statement: set sex= if (new.sex=1, 'male', 'female');-- Multi-conditional if statement: if old.type=1 then update table...; elseif old.type=2 then update table...; end if; III. MySQL view trigger:

You can use "show triggers;" to view triggers. Because triggers created by MySQL are saved in the triggers table in the information_schema library, you can also view triggers by querying this table:

-- View the trigger through the information_ schema.triggers table: select * from information_schema.triggers;-- mysql view the trigger of the current database show triggers;-- mysql view the trigger of the specified database "aiezu". Fourth, MySQL delete trigger:

1. You can use drop trigger to delete triggers:

Drop trigger trigger_name;2, determine whether the trigger exists before deletion:

Examples of drop trigger if exists trigger_ name V and Msql trigger usage:

1. MySQL trigger Insert triggers to update the same table:

Below we have a table "tmp1". The tmp1 table has two integer fields: N1 and N2. We will use a trigger to automatically set the value of the N2 field to 5 times that of the N1 field when tmp inserts the record.

Create test tables and triggers:

-- create test table drop table if exists tmp1;create table tmp1 (N1 int, N2 int);-- create trigger DELIMITER $drop trigger if exists tmp1_insert$create trigger tmp1_insertbefore insert on tmp1for each rowbegin set new.n2 = new.n1*5;end$DELIMITER

Test the effect of triggering updates:

Mysql > insert tmp1 (N1) values (18); Query OK, 1 row affected (0.01 sec) mysql > insert tmp1 (N1) values (99); Query OK, 1 row affected (0.00 sec) mysql > select * from tmp1;+-+-+ | N1 | N2 | +-+ | 18 | 90 | 99 | 495 | +-+ 2 rows in set (0.00 sec) 2, MySQL trigger Update triggers to update another table:

Below, there are two tables, tmp1 and tmp2, both of which have the same field name. When you update the name of one table using a trigger implementation, the name of another table is also updated.

Create test tables and triggers:

-- create test tables and insert test data drop table if exists tmp1;drop table if exists tmp2;create table tmp1 (id int, name varchar) default charset='utf8';create table tmp2 (fid int, name varchar) default charset='utf8';insert into tmp1 values (1, 'love E'); insert into tmp2 values (1, 'love E');-- create trigger DELIMITER $drop trigger if exists tmp1_update$create trigger tmp1_updateafter update on tmp1for each rowbegin update tmp2 set name=new.name where fid=new.id End$DELIMITER

Test the effect of triggering updates:

Mysql > select * from tmp1;+-+-+ | id | name | +-+-+ | 1 | Love E family | +-+-+ 1 row in set (0.00 sec) mysql > select * from tmp2 +-+-+ | fid | name | +-+-+ | 1 | Love E family | +-+-+ 1 row in set (0.00 sec) mysql > update tmp1 set name='aiezu.com' where id=1;Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql > select * from tmp1 +-+-+ | id | name | +-+-+ | 1 | aiezu.com | +-+-+ 1 row in set (0.00 sec) mysql > select * from tmp2 +-+-+ | fid | name | +-+-+ | 1 | aiezu.com | +-+-+ 1 row in set (0.00 sec) after reading the above content, do you have a better understanding of how to use MySQL triggers? if you want to learn more, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.