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

This article mainly explains how to create and use mysql triggers, the content is clear and clear, interested friends can learn about it, I believe you will be helpful after reading.

What's a trigger?

Triggers are used to perform certain actions "automatically" after/before certain actions. (For example, if new student information is inserted, the number of students should be modified in the class table). When insert delete update sets the trigger, executing insert delete update automatically triggers the set content. A table can have up to 6 triggers (3*2, three operations * two times (before| after))。create a trigger

Grammar:

create trigger name before| after event on table name for each row trigger statement;

Trigger name is recommended as trigger_xxx, which is easy to distinguish. Trigger name cannot be repeated. before| After represents the trigger statement execution time, if before, the trigger statement is executed before the insert delete update operation;after is after. An event is one of the insert delete update operations. For each row is a trigger that represents any record that performs the corresponding action. A trigger statement is the statement that executes when the trigger fires. Keyword explanation in MySQL creation syntax: field meaning Possible values DEFINER= optional parameter, specify creator, default to current login user (CURRENT_USER);

This trigger will be executed by the user specified with this parameter, so permission issues need 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 a certain event;BEFORE, AFTERtrigger_event trigger event, such as trigger when inserting, trigger when deleting;

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

UPDATE: update operation trigger, triggered when UPDATE operation;

Delete: delete action trigger, triggered during Delete, REPLACE operation;INSERT, UPDATE, DELTEtable_name Table name of trigger action 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 order is consistent with the trigger creation order. You can

Use this parameter to change their firing order. MySQL 5.7.2 supports this parameter.

FOLLOWS: The current creation trigger activates after an existing trigger;

PRECEDES: The trigger currently created is activated before the existing trigger;FOLLOWS, PRECEDETrigger_body The content of the SQL statement triggered for execution, generally beginning with begin and ending with begin.. end

Mysql trigger usage example:

1. MySQL trigger Insert triggers updating the same table:

Below we have a table "tmp1", tmp1 table has two integer fields: n1, n2. We want to implement this with a trigger that automatically sets the value of the n2 field to 5 times the value of the n1 field when tmp inserts a 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_insert before insert on tmp1 for each row begin set new.n2 = new.n1*5; end$ DELIMITER ;

Test trigger update effect:

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 updating another table:

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, you update the name of another table.

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(128)) default charset='utf8'; create table tmp2 (fid int, name varchar(128)) 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_update after update on tmp1 for each row begin update tmp2 set name=new.name where fid=new.id; end$ DELIMITER ;

Test trigger update effect:

mysql> select * from tmp1; +------+---------+ | id | name | +------+---------+ | 1 |Love E| +------+---------+ 1 row in set (0.00 sec) mysql> select * from tmp2; +------+---------+ | fid | name | +------+---------+ | 1 |Love E| +------+---------+ 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 further understanding of how to create and use mysql triggers? If you want to learn more, please pay attention to 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.