The use of MySQL trigger trigger

Q: What is a trigger?

A:

A trigger is a database object that is related to a table, fires when a condition is met, and executes a collection of statements defined in the trigger.

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/1113510/201705/ 1113510-20170507093019586-2137207217.png "width=" 519 "height=" 318 "style=" margin:0px;padding:0px;border:0px; "/ >

Characteristics of the trigger:

1, there is a BEGIN end body, begin end, the statement between can be written simple or complex

2, what conditions will trigger: I, D, U

3. When to trigger: before or after adding or deleting a change

4, Trigger frequency: For each line of execution

5, the trigger definition on the table, attached to the table.

That is, an action is triggered by an event that includes an INSERT statement, an UPDATE statement, and a DELETE statement, which assists the application to ensure the integrity of the data on the database side.

Note: Cannot associate a trigger with a temporary table or a view.

！！ Use as few triggers as possible, not recommended.

Assuming that the trigger triggers each execution of the 1s,insert table 500 data, the trigger will need to be triggered 500 times, the trigger execution time is spent 500s, and the Insert 500 data is 1s, then this insert is very inefficient. So one of our special considerations is that the trigger's begin end, the execution of the statement between the efficiency must be high, the resource consumption is small.

Triggers use as little as possible, because anyway, it consumes resources, and if used with caution, make sure it is very efficient: the triggers are for each row, and the tables on which the additions and deletions change very frequently do not need to use triggers because it consumes resources very much.

First, create a trigger

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

CREATE [definer = {User | Current_User}]trigger trigger_nametrigger_time Trigger_eventon tbl_name for each ROW [trigger_order]trigger_ Bodytrigger_time: {before | After}trigger_event: {INSERT | UPDATE | DELETE}trigger_order: {follows | Precedes} other_trigger_name

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

The > before and after parameters specify the time to trigger execution, either before or after the event.

> For each row means that the trigger is triggered by an operation on any record that satisfies the trigger, which means that the trigger is triggered once per row of data.

> tigger_event Detailed:

①insert trigger: Activates the trigger when inserting a row, which may be triggered by the INSERT, load DATA, REPLACE statement (the load DAT statement is used to load a file into a data table, rather than a series of insert operations);

②update trigger: Activates a trigger when a row is changed and may be triggered by an UPDATE statement;

③delete trigger: Activates a trigger when a row is deleted and may be triggered by a delete, replace statement.

> Trigger_order is a feature after MySQL5.7 that defines multiple triggers, using follows (trailing) or precedes (in ... First) to select the order in which the trigger executes.

1. Create a trigger with only one execution statement

CREATE TRIGGER Trigger Name before| After trigger event on table name for each ROW execution statement;

Example 1: Create a trigger named TRIG1 that, once inserted in the work table, will automatically insert the current time into the

Mysql> CREATE TRIGGER trig1 after insert, on work for each ROW, INSERT into Time VALUES (now ());

2. Create a trigger with multiple execution statements

CREATE TRIGGER Trigger Name before| After Trigger event

On table name for each ROW

BEGIN

Execute statement List

END;

Example 2: Define a trigger that executes the statement in begin and end once a delete operation satisfies a condition

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

Mysql> DELIMITER | | Mysql> CREATE TRIGGER Trig2 before DELETE--on work for each ROW, BEGIN, INSERT into time VALU    ES (now ());    INSERT into Time VALUES (now ()); end| | Mysql> DELIMITER;

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

3, new and old detailed

MySQL defines NEW and old, which is used to denote the table in which the trigger is located, the row of data that triggered the trigger, to refer to the record content that has changed in the trigger, specifically:

① in the Insert trigger, new is used to represent the data that will be (before) or have been inserted (after);

② in an update trigger, old is used to represent the original data that will or has been modified, and new is used to represent the data that will or has been modified;

③ in a Delete-type trigger, old is used to denote the original data that will or has been deleted;

How to use:

New.columnname (ColumnName is a column name for the corresponding data table)

In addition, old is read-only, and new can use SET assignment in a trigger so that the trigger is not triggered again, causing a circular call (such as adding "2013" to the student's number before each insert).

Example 3:

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

mysql> create table account  (Acct_num int, amount decimal (10,2)); MySQL > insert into account values (137,14.98), (141,1937.50), (97,-100.00);mysql>  delimiter $ $mysql > CREATE TRIGGER upd_check BEFORE UPDATE ON  account    -> for each row    -> begin 　　　　   ->  if new.amount < 0 then    ->  set new.amount = 0;    ->  elseif new.amount > 100  THEN    ->  set new.amount = 100;    ->   end if;    -> end$ $mysql > delimiter ;mysql> update  account set amount=-10 where acct_num=137;mysql> select * from  account;+----------+---------+| acct_num | amount  |+----------+---------+|       137 |    0.00 | |       141 | 1937.50 | |        97 | -100.00 |+----------+---------+mysql>  update account set amount=200 where acct_num=137;mysql> select *  from account;+----------+---------+| acct_num | amount  |+----------+---------+|       137 |  100.00 | |       141 | 1937.50 | |        97 | -100.00 |+----------+---------+

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

Second, view the trigger

1. Show triggers statement view trigger information

Mysql> SHOW Triggers\g;

......

As a result, the basic information for all triggers is displayed, and the specified trigger cannot be queried.

2. View the trigger information in the Information_schema.triggers table

Mysql> SELECT * from Information_schema.triggers\g

......

As a result, the details of all the triggers are displayed, and the method can query the details for making the trigger.

Mysql> SELECT * from Information_schema.triggers, where trigger_name= ' Upd_check ' \g;

Tips:

All trigger information is stored in the Triggers table under the INFORMATION_SCHEMA database and can be queried using the SELECT statement, preferably through the trigger_name field if there is too much information on the trigger.

Third, delete the trigger

DROP TRIGGER [IF EXISTS] [schema_name.] Trigger_name

It is best to use the method above to view the trigger after deleting it, or you can use Database.trig to specify a trigger in a database.

Tips:

This is critical if you do not need a trigger when you must remove the trigger to avoid unintended actions.

The use of MySQL trigger trigger