(this article refers to: https://www.cnblogs.com/phpper/p/7587031.html )
1. What is a trigger
A trigger is a database object related to a table. It is triggered when the defined conditions are met and executes the statement set defined in the trigger. This feature of trigger can help the application to ensure the integrity of data 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 created log into the log table. If you do not use triggers,
You need to write program language logic to realize it, but if you define a trigger, the function of the trigger is to help you insert a log information in the log table after you insert a piece of data in the user table.
Of course, triggers can not only insert, but also modify and delete.
2. Create trigger
The syntax for creating triggers is as follows:
CREATE TRIGGER trigger_name trigger_time trigger_event ON tb_name FOR EACH ROW trigger_stmt trigger_name: The name of the trigger tirgger_time: Trigger time is BEFORE perhaps AFTER trigger_event: Trigger event, for INSERT,DELETE perhaps UPDATE tb_name: Indicates the table on which the trigger is created trigger_stmt: The program body of the trigger can be a SQL Statement or use BEGIN and END Contains multiple statements So it can be said MySQL Create the following six triggers: BEFORE INSERT,BEFORE DELETE,BEFORE UPDATE AFTER INSERT,AFTER DELETE,AFTER UPDATE
The trigger name parameter refers to the name of the trigger to be created
The BEFORE and AFTER parameters specify when execution is triggered, BEFORE or AFTER the event
FOR EACH ROW means that the trigger event will be triggered if the operation on any record meets the trigger event
Create a trigger with multiple execution statements
CREATE TRIGGER Trigger Name BEFORE|AFTER Trigger event ON Table name FOR EACH ROW BEGIN Execution statement list END
The execution statement list parameter between BEGIN and END represents multiple statements to be executed, and different statements are separated by semicolons
tips: in general, mysql defaults to; As an end execution statement, it conflicts with the branch required in the trigger
To solve this problem, you can use DELIMITER, such as DELIMITER |, which can turn the end symbol into||
When the trigger is created, delete can be used; To turn the end symbol into;
mysql> DELIMITER || mysql> CREATE TRIGGER demo BEFORE DELETE -> ON 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 ;
In the above statement, the end symbol is defined as 𞓜, and a trigger is defined in the middle. Once there is a deletion operation that meets the conditions
It will execute the statements in BEGIN and END, and then END with |
Finally, use DELIMITER; Restore end symbol
tigger_event:
The load data statement inserts the contents of the file into the table, which is equivalent to an insert statement. In general, the replace statement is similar to an insert statement, but if there is a primary or unique index in the table,
If the inserted data is the same as the original primary key or unique, the original data will be deleted and a new data will be added. Therefore, sometimes executing a replace statement is equivalent to executing a delete and insert statement.
Triggers can be one SQL statement or multiple SQL code blocks. How to create them?
DELIMITER $ #Change the delimiter of the statement to$ BEGIN sql1; sql2; ... sqln END $ 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 BEGIN...END:
DECLARE var_name var_type [DEFAULT value] #Define variables and specify default values SET var_name = value #Assign values to variables
Use of NEW and OLD:
(in my own words: data before modification) , use old, old. The column name refers to the value in the row before being modified
For the modified data, use new To show, New. Column name refers to the value in the row after being modified)
According to the above table, you can use the following format to use the corresponding data:
NEW.columnname: Data in a column of a new row OLD.columnname: Delete a column of data in a row
Having said so much, now let's create an update trigger!
There is now a table as follows:
users table:
CREATE TABLE `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name_lick` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL, `on_status` char(1) DEFAULT NULL, PRIMARY KEY (`id`), KEY `name` (`name_lick`(250)) USING BTREE ) ENGINE=MyISAM AUTO_INCREMENT=1000001 DEFAULT CHARSET=latin1;
logs table:
CREATE TABLE `logs` ( `id` int(11) NOT NULL AUTO_INCREMENT, `log_lick` varchar(255) DEFAULT NULL COMMENT 'Log description', `on_status` char(1) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COMMENT='Log table';
The requirement is that when a field is updated in users, it will be modified in logs according to the field you specify.
Create trigger (update): you can also update multiple tables at the same time according to individual needs
CREATE TRIGGER user_log BEFORE UPDATE ON users FOR EACH ROW BEGINRunning result: when the status of users is updated to 0, the two tables in ningwai are updated to 0:
#If (updated value = 0) if(new.on_status = 0) #new.on_statusRefers to( users The updated value of a field in the table is simply (new value) THEN update logs set on_status=0 where log_lick = old.name_lick; #old.name_lickRefers to( users Tabular name_lick Field), old:Represents (old value) update dept set on_status=0 where dept_lick = old.name_lick; elseif(new.on_status = 1) THEN update logs set on_status=1 where log_lick = old.name_lick; update dept set on_status=1 where dept_lick = old.name_lick; END IF; END;
Create trigger (insert): when a piece of data is inserted into users, a log message will be generated in logs.
DELIMITER $ CREATE TRIGGER user_log AFTER INSERT ON users FOR EACH ROW BEGIN DECLARE s1 VARCHAR(40)character set utf8; DECLARE s2 VARCHAR(20) character set utf8;#Later, it is found that the Chinese character code is garbled. Set the character set here SET s2 = " is created"; SET s1 = CONCAT(NEW.name_lick,s2); #The CONCAT function concatenates strings INSERT INTO logs(log_lick) values(s1); END $ DELIMITER ;View triggers SHOW TRIGGERS statementview trigger information
#Delete trigger
drop trigger user_log; # delete trigger
Here are a few more words:
tips: the SHOW TRIGGERS statement cannot query the specified trigger
View trigger information in the triggers tableSELECT * FROM information_schema.triggers;
The results show the details of all triggers, and the method can query the details of the trigger
SELECT * FROM information_schema.triggers WHERE TRIGGER_NAME='user_log';
tips: all trigger information is stored in information_ In the triggers table under the schema database
You can use the SELECT statement to query. If there is too much trigger information, you'd better use trigger_ The name field specifies the query
Back to the above, we have created the trigger, continue to insert data in users and view the data:
insert into users(name_lick,on_status) values('xxx',3);
OK, let's check the logs table again!
Through the above example, you can see that you only need to insert the user's information in users, and the log will be automatically recorded in the logs table. This may be the convenience brought to me by the trigger!
Limitations and precautions
Triggers have the following two limitations:
1. The trigger program cannot CALL the stored program that returns the data to the client, nor can it use the dynamic SQL statement with CALL statement, but the stored program is allowed to return the data to the trigger program through parameters, that is, it is possible for the stored procedure or function to return the data to the trigger through OUT or INOUT parameters, but it cannot CALL the process that directly returns the data.
2. Statements that start or end a transaction in a display or implicit manner, such as START TRANS-ACTION,COMMIT or ROLLBACK, can no longer be used in triggers.
Note: MySQL triggers are executed in the order of BEFORE trigger, row operation and AFTER trigger. If an error occurs in any step, the remaining operations will not be executed. If an error occurs in the operation on the transaction table, it will be rolled back. If an operation on the non transaction table, it will not be rolled back, and the data may be wrong.
summary
Triggers are triggered based on rows, so deletion, addition or modification may activate triggers. Therefore, do not write overly complex triggers or add excessive triggers, which will have a serious impact on data insertion, modification or deletion, and poor portability, Therefore, we must consider it when designing triggers.
Trigger is a special stored procedure. It triggers execution when inserting, deleting or modifying data in a specific table. It has finer and more complex data control ability than the standard functions of the database itself.
Database triggers have the following functions:
1. Safety. You can give users some right to operate the database based on the value of the database.
# You can limit user actions based on time, for example, you are not allowed to modify database data after work and holidays.
# The user's operation can be limited based on the data in the database. For example, the rise of stock price is not allowed to exceed 10% at a time.
2. Audit. You can track user operations on the database.
# Audit the statements of users operating the database.
# Write the user's updates to the database into the audit table.
3. Implement complex data integrity rules
# Implement non-standard data integrity checks and constraints. Triggers can produce more complex constraints than rules. Unlike rules, triggers can reference columns or database objects. For example, a trigger can roll back any attempt to eat more futures than its own margin.
# Provide variable defaults.
4. Implement complex non-standard database related integrity rules. Triggers can update related tables in the database in a series. For example, in the authors table, author_ The delete trigger on the code column causes matching rows in other tables to be deleted accordingly.
# Cascade modification or deletion of matching rows in other tables when modifying or deleting.
# When modifying or deleting, set the matching rows in other tables to NULL values.
# When modifying or deleting, cascade the matching rows in other tables to the default value.
# Triggers can reject or roll back changes that destroy the integrity of the relevant data and cancel transactions that attempt to update the data. This trigger works when an external key that does not match its main key is inserted. For example, you can use books.author_code An insert trigger is generated on the column if the new value is the same as authors.author_ When a value in the code column does not match, the insertion is fallback.
5. Copy the data in the table synchronously and in real time.
6. Automatically calculate the data value. If the data value meets certain requirements, carry out specific processing. For example, if the fund on the company's account is less than 50000 yuan, send warning data to the financial personnel immediately.
No matter what industry you are engaged in, as long as you do two things well, one is your specialty, the other is your character. Specialty determines your existence, character determines your contacts, and the rest is to insist and win more trust with kindness, specialty and sincerity.