MySQL Trigger Trigger

August 15, 2018, today because of the previous error (because the company did not understand the business caused) so that a field should not be assigned to the assignment, the magic is actually on the production, and then a series of data errors, in order to suppress the serious situation, can only write triggers, because the code is not realistic, The general situation is this, there is a table inside an order number and an order status, due to Excel imported data, Excel does not have these two fields, when the time is not long, see the table has an order number, feel an important field, asked a team leader, he said with the UUID assigned to the line, Later on the assignment, the assignment will continue to adjust the interface of Huatai over there, because this order number is our generation, Huatai did not, then is a variety of mistakes, and then the leader said who let you assign value, you have seen that business field can be randomly assigned value, in the mind 10,000 MMP, at that time is not you say, Then wash a trigger stop!!!

The original address reproduced: Https://www.cnblogs.com/phpper/p/7587031.html through this asked the great God's guidance to write

On the trigger I wrote first:

DROP TRIGGER if EXISTS upd_che;
CREATE TRIGGER Upd_che before INSERT on Wl_ser_info for each ROW
BEGIN
IF (
NEW. Service_order_status = ' '
OR NEW. Service_order_status is NULL
) Then
SET NEW. Service_orderno = NULL;
End If;
END;

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.

To create a trigger:

Forupdateupdate 

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

TRIGGER trigger name before|  After trigger event for each  ROWBEGIN EXECUTE 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
On the users for each ROW
BEGIN
INSERT into Logs VALUES (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 $  #将语句的分隔符改为 $BEGINsql1;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

CREATETABLE' Users ' (' ID 'intOne) unsignedNotNullAuto_increment, ' name 'varchar255)character set utf8mb4  DEFAULT null, ' add_time '  Int (11) default null, primary key  (' id '), key ' name ' (' Name ' (250)) USING BTREE) engine=myisam auto_ Increment=1000001 default charset=latin1;         

Log Logs table:

CREATETABLE' Logs ' (' Id ')int11)not NULL auto_increment, ' log' varchar (255) DEFAULT NULL COMMENT ' log Description ' , PRIMARY 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 $CREATETRIGGER User_log AfterINSERTOn usersForEach ROWBEGINDECLARE S1VARCHAR (40) Character Set Utf8;declare s2 varchar ( 20) character Set UTF8, #后面发现中文字符编码出现乱码, set the character set set s2 Span style= "COLOR: #808080" >= "is created"; set s1 = CONCAT (NEW.NAME,S2); The function concat can concatenate strings insert into logs ( log) values (S1);  End $DELIMITER;           

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

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 $CREATETRIGGER User_log AfterINSERTOn usersForEach ROWBEGINDECLARE S1varchar (40 ) character set UTF8; declare s2 varchar (20) 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

From Information_schema.triggers;

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

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:

VALUES (' botong-pass ', now ());  

MySQL Trigger Trigger