MySQL trigger and mysql trigger

MySQL trigger and mysql trigger

MySQL from 5.0.2 version to support the function of the trigger, this blog to introduce the trigger, mysql version: mysql-5.7.19.

 

What is a trigger?

A trigger is a database object related to tables. It is triggered when the defined conditions are met and a set of statements defined in the trigger is executed. This feature helps applications ensure data integrity at the database end.

For example, you now have two tables, user table and log table. When a user is created, you need to insert the created log in the log table, if you do not use a trigger, you must use two insert statements. However, if you define a trigger, the trigger is used to insert a log information in the log table after you insert a data entry in the User table.

 

Of course, the trigger can not only be inserted, but also be modified or deleted.

 

Create a trigger

The syntax for creating a trigger is as follows:

Create trigger trigger_name trigger_time trigger_event ON tb_name for each row timing: TRIGGER name tirgger_time: TRIGGER time, BEFORE or AFTERtrigger_event: TRIGGER event, which is INSERT, DELETE, or UPDATEtb_name: indicates the trigger creation, indicating on which table the trigger_stmt trigger is created: the program body of the trigger, it can be an SQL statement or multiple statements contained by BEGIN and END. Therefore, MySQL creates the following six triggers: BEFORE INSERT, BEFORE DELETE, BEFORE UPDATEAFTER INSERT, AFTER DELETE, and AFTER UPDATE.

Tigger_event:

 

The load data statement inserts the file content into the table, which is equivalent to the insert statement. The replace statement is similar to the insert statement in general, but if the table has a primary or unique index, if the inserted data is the same as the original primary key or unique, the original data is deleted and a new data is added, therefore, sometimes executing a replace statement is equivalent to executing a delete and insert statement.

Trigger_stmt:

Trigger_stmt can be an SQL statement or multiple SQL code blocks. How can this problem be created?

DELIMITER $ # change the statement separator to $ BEGINsql1; sql2;... sqlnEND $ DELIMITER; # change the statement separator back to the original Semicolon ";"

Variables can also be defined in the BEGIN... END statement, but can only be used inside the BEGIN... END statement:

DECLARE var_name var_type [DEFAULT value] # define the variable. You can specify the DEFAULT value SET var_name = value # assign a value to the variable.

Use of NEW and OLD:

 

Based on the above table, you can use the corresponding data in the following format:

NEW. columnname: NEW row's column data OLD. columnname: delete a row's column data

Now let's create a trigger!

The table is as follows:

mysql> desc userinfo;+----------+-------------+------+-----+---------+----------------+| Field    | Type        | Null | Key | Default | Extra          |+----------+-------------+------+-----+---------+----------------+| id       | int(11)     | NO   | PRI | NULL    | auto_increment || username | varchar(20) | YES  |     | NULL    |                || passwd   | varchar(20) | YES  |     | NULL    |                |+----------+-------------+------+-----+---------+----------------+3 rows in set (0.00 sec)mysql> desc log;+-------+--------------+------+-----+---------+----------------+| Field | Type         | Null | Key | Default | Extra          |+-------+--------------+------+-----+---------+----------------+| id    | int(11)      | NO   | PRI | NULL    | auto_increment || log   | varchar(100) | YES  |     | NULL    |                |+-------+--------------+------+-----+---------+----------------+2 rows in set (0.00 sec)

The requirement is: when a piece of data is inserted in userinfo, a log information will be generated in the log.

Create a trigger:

DELIMITER $ create trigger user_log after insert on userinfo for each rowbegindeclare s1 VARCHAR (40); DECLARE s2 VARCHAR (20); SET s2 = "is created"; SET s1 = CONCAT (NEW. username, s2); # The function CONCAT can connect strings to insert into log (log) values (s1); END $ DELIMITER;

Insert data in userinfo and view the data:

mysql> insert into userinfo(username,passwd) values('frank','123');Query OK, 1 row affected (0.01 sec)mysql> select * from userinfo;+----+----------+--------+| id | username | passwd |+----+----------+--------+|  1 | frank    | 123    |+----+----------+--------+1 row in set (0.00 sec)

Okay. Let's take a look at the log table!

mysql> select * from log;+----+------------------+| id | log              |+----+------------------+|  1 | frank is created |+----+------------------+1 row in set (0.00 sec)

In the above example, we can see that you only need to insert user information in userinfo, and the log will be automatically recorded in the log table. This may be the convenience that the trigger brings to me!

 

Delete trigger

You can delete a trigger at a time. The syntax is as follows:

Drop trigger [db_name.] trigger_name # If db_name is not specified, the current database is used by default.

For example, delete the trigger in the above example:

mysql> drop trigger user_log;Query OK, 0 rows affected (0.00 sec)

 

View triggers

You can run the show triggers command to view the trigger status:

mysql> show triggers  \G;*************************** 1. row ***************************             Trigger: user_log               Event: INSERT               Table: userinfo           Statement: BEGINDECLARE s1 VARCHAR(40);DECLARE s2 VARCHAR(20);SET s2 = " is created";SET s1 = CONCAT(NEW.username,s2);INSERT INTO log(log) values(s1);END              Timing: AFTER             Created: 2017-09-22 21:12:46.02            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION             Definer: root@localhostcharacter_set_client: utf8collation_connection: utf8_general_ci  Database Collation: utf8_general_ci1 row in set (0.00 sec)ERROR:No query specified

 

Restrictions and precautions

The trigger has the following two restrictions:

1. the trigger program cannot CALL the storage program that returns data to the client, nor use a dynamic SQL statement that uses the CALL statement. However, it allows the storage program to return data to the trigger program through parameters, that is, it is possible for a stored procedure or function to return data to a trigger through an OUT or INOUT parameter, but the process of directly returning data cannot be called.

2. You cannot use statements in the trigger to START or end transactions in a display or implicit manner, such as START TRANS-ACTION, COMMIT, or ROLLBACK.

Note: The MySQL trigger is executed in the order of BEFORE trigger, row operation, and AFTER trigger. The remaining operations will not be executed if any step of the trigger fails, if an error occurs during operations on the transaction table, the transaction will be rolled back. If the operation is performed on a non-transaction table, the transaction cannot be rolled back and the data may fail.

 

Summary

A trigger is triggered Based on rows. Therefore, deleting, adding, or modifying a trigger may activate the trigger. Therefore, do not compile a trigger that is too complex or add a trigger, this will have a serious impact on data insertion, modification, or deletion, as well as poor portability. Therefore, you must consider it when designing a trigger.

 

Reference books: recommended books for MySQL! Reference: http://www.cnblogs.com/duodushu/p/5446384.html