MySQL Trigger instance detailed

MySQL seems to be starting from 5.0.2 version to support the function of the trigger, this blog to introduce the trigger, first of all, or talk about the concept of something:

What is a trigger

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. This feature of triggers helps the application ensure data integrity on the database side.

For example, you now have two tables "user table" and "Log Table", when a user is created, you need to insert the log log in the journal table, if you do not use triggers, you need to write programming language logic to implement, but if you define a trigger, The purpose of a trigger is to insert a log message in the log table after you insert a piece of data into the user table. Of course, the trigger is not only to insert operations, but also to perform modifications, deletions.

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 trigger_stmttrigger_name: Name of the trigger Tirgger_ Time: Trigger, for before or aftertrigger_event: Trigger event, INSERT, delete, or updatetb_name: Indicates that a trigger is established, which is the table on which the trigger is created Trigger_ stmt: The program body of a trigger, which can be an SQL statement or multiple statements with begin and end, so you can say that MySQL creates the following six types of triggers: Before Insert,before delete,before updateafter Insert,after Delete,after UPDATE

Where the trigger name parameter refers to the name of the trigger to be created

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

The for every row indicates that the action on any one record satisfies the triggering event and triggers the trigger

To create a trigger with more than one execution statement

CREATE TRIGGER Trigger Name before| After trigger event on table name for each rowbegin    execution statement list end

Where the EXECUTE statement list parameter between Begin and end represents multiple statements that need to be executed, with separate statements separated by semicolons

tips: in general, MySQL defaults to; As an end execution statement, conflicts with the branch required in the trigger

To resolve this issue, you can use the DELIMITER, such as: DELIMITER | |, to turn the end symbol into a | |

When the trigger is created, you can use delimiter to turn the end symbol into;

Mysql> DELIMITER | | Mysql> CREATE TRIGGER Demo before DELETE--and on users for each    ROW and    BEGIN    INSERT into logs VAL UES (now ());    INSERT into Logs VALUES (now ());    END    | | Query OK, 0 rows affected (0.06 sec) mysql> DELIMITER;

The preceding statement, beginning with the end symbol defined as | |, defines a trigger in the middle, once a delete operation satisfies the condition

Executes the statements in begin and end, and then uses the | | End

finally use delimiter; Restore the end symbol

Tigger_event:

The load Data statement inserts the contents of the file into the table, which is equivalent to an INSERT statement, while the Replace statement is generally similar to insert, but if a primary or a unique index exists in the table, If the inserted data is the same as the original primary key or unique, the original data is deleted, and then a new data is added, so sometimes executing a replace statement is equivalent to executing a DELETE and insert statement.

The trigger can be either an SQL statement or multiple blocks of SQL code, how do you create it?

DELIMITER $  #将语句的分隔符改为 $BEGINsql 1;sql2;...sqlnend $DELIMITER;  #将语句的分隔符改回原来的分号 ";"

At the begin ... The variable can also be defined in the end statement, but only at the begin ... End internal use:

DECLARE var_name Var_type [default value] #定义变量, you can specify the default value set Var_name = value  #给变量赋值

Use of new and old:

Depending on the table above, you can use the appropriate data in the format:

New.columnname: A column of data for a new row old.columnname: Deleting a column of data from a row

Say so much now let's create a trigger!

Now there are the following tables:
User Users Table

CREATE TABLE ' users ' (  ' id ' int (one) unsigned not NULL auto_increment,  ' name ' varchar (255) CHARACTER SET utf8mb4 DE FAULT null,  ' add_time ' int (one) DEFAULT NULL,  PRIMARY key (' id '),  key ' name ' (' name ') ' USING BTREE) ENGINE =myisam auto_increment=1000001 DEFAULT charset=latin1;

Log Logs table:

CREATE TABLE ' logs ' (  ' Id ' int (one) not null auto_increment,  ' log ' varchar (255) DEFAULT NULL COMMENT ' log description ',  PR Imary KEY (' Id ')) engine=innodb DEFAULT charset=utf8mb4 comment= ' Log table ';

The requirement is that when a piece of data is inserted in the users, a log message is generated in the logs.

To create a trigger:

DELIMITER $CREATE TRIGGER User_log after inserts on the users for each rowbegindeclare S1 VARCHAR (+ character Set Utf8;declare S2 VARCHAR (character) set UTF8; #后面发现中文字符编码出现乱码, set the charset setting S2 = "is created"; SET S1 = CONCAT (NEW.NAME,S2);     #函数CONCAT可以将字符串连接INSERT into logs (log) values (S1); END $DELIMITER;

Here I use the navicat:

View trigger SHOW Triggers statement view trigger information

TIP:

Above I use Navicat directly create, if everyone use MySQL front,name here will have a difference, we delete just the trigger, in the MySQL front test

Drop trigger  User_log; #删除触发器

Open MySQL Front:

MySQL front when compiling sql, you do not have to define the trailing delimiter, and the modified SQL is straightforward:

#DELIMITER $CREATE TRIGGER User_log after inserts on the users for each rowbegindeclare S1 VARCHAR (All) Character Set Utf8;declar E S2 VARCHAR (character) set UTF8; SET s2 = "is created"; SET S1 = CONCAT (NEW.NAME,S2);     #函数CONCAT可以将字符串连接INSERT into logs (log) values (S1); END #$ #DELIMITER;

Here's a few more words:

Tips: The SHOW triggers statement cannot query the specified trigger

Viewing trigger information in the Triggers table

SELECT * from Information_schema.triggers;

The results show the details of all the triggers, and the method can query the details of making the trigger

SELECT * from Information_schema.triggers WHERE trigger_name= ' User_log ';

tips: All trigger information is stored in the Triggers table under the INFORMATION_SCHEMA database

You can use the SELECT statement to query, if the trigger information is too large, it is best to specify the query through the Trigger_name field

Back up, we created the trigger and continued to insert the data into the users and view the data:

Insert into users (name,add_time) VALUES (' Botong-pass ', now ());

OK, let's check out the logs table again!

From the above example, you can see that only need to insert the user's information in the users, the log will be automatically recorded in the logs table, this is perhaps the trigger to bring me the convenience of it!

Limitations and Precautions

There are two limitations to the trigger:

1. The trigger cannot invoke the stored program that returns the data to the client, nor can it use a dynamic SQL statement with the call statement, but allows the stored program to return the data to the trigger via parameters, that is, if the stored procedure or function returns data to the trigger via an out or inout type parameter, However, the process of returning data directly cannot be called.

2. Statements, such as Start trans-action,commit or rollback, can no longer be used in triggers to start or end transactions in a display or an implicit manner.

Note: The MySQL trigger is executed in the order of the before trigger, row operation, after trigger, in which case the error does not continue to perform the remainder of the operation, if the operation on the transaction table, if an error occurs, then will be rolled back, if it is a non-transactional table operation, Then you cannot roll back, and the data may go wrong.

Summarize

Triggers are triggered based on rows, so deleting, adding, or modifying operations can trigger triggers, so don't write overly complex triggers or add up-to-date triggers, which can have a more serious impact on inserting, modifying, or deleting data, and it can result in poor portability. So when designing a trigger, be sure to consider it.

A trigger is a special kind of stored procedure that triggers execution when inserting, deleting, or modifying data in a particular table, which has finer and more complex data control than the standard functionality of the database itself.

Database triggers have the following effects:

1. Security. You can make a user have some right to manipulate the database based on the value of the database.

# You can limit user actions based on time, such as not allowing database data to be modified after work and holidays.

# You can limit the user's actions based on data in the database, such as not allowing the price of the stock to increase by more than 10% at a time.

2. Audit. You can track user actions on a database.

# Audit the statements of the user operations database.

# writes the user's updates to the database to the audit table.

3. Implementing complex data integrity rules

# implements non-standard data integrity checks and constraints. Triggers can produce more complex restrictions than rules. Unlike rules, triggers can reference columns or database objects. For example, a trigger can rewind any futures that attempt to eat more than their own margin.

# provides a variable default value.

4. Implement complex non-standard database-related integrity rules. Triggers can be used for serial updates of related tables in the database. For example, a delete trigger on the Auths table author_code column causes the corresponding row to be deleted in the other table.

# cascade Modify or delete rows in other tables that match them when modified or deleted.

# Sets the row in the other table to a null value when modified or deleted.

# Sets the row cascade in the other table to be the default value when modified or deleted.

# triggers can reject or rollback changes that disrupt related integrity and cancel transactions that attempt to update data. This trigger works when you insert a foreign key that does not match its primary health. For example, an INSERT trigger can be generated on the Books.author_code column, and if the new value does not match a value in the Auths.author_code column, the insert is rolled back.

5. Synchronously replicates the data in the table in real time.

6. Automatically calculates the data value, if the value of the data meets certain requirements, the specific processing. For example, if the company's account has less than $50,000 in money, it immediately sends warning data to the financial officer.

MySQL Trigger instance detailed