Detailed explanation of MySQL trigger trigger example

MySQL seems to have supported triggers since version 5.0.2. This blog will introduce triggers. First, let's talk about the concepts:

What is a trigger

A trigger is a database object associated with a table that is triggered when the defined conditions are met and executes the set of statements defined in the trigger. This feature of triggers can help applications ensure data integrity on the database side.

For example, if you now have two tables [user table] and [log table], when a user is created, you need to insert the creation log into the log table. If you do not use triggers, you need to write programming language logic to achieve it. However, if you define a trigger, the role of the trigger is to help you insert a log message into the log table after you insert a piece of data into the user table. Of course, triggers are not limited to insert operations, but can also perform modifications and 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_stmt
trigger_name: the name of the trigger trigger_time: the trigger time, which can be BEFORE or AFTER
trigger_event: trigger event, INSERT, DELETE or UPDATE
tb_name: indicates the table on which the trigger is created. trigger_stmt: the body of the trigger, which can be a SQL statement or multiple statements enclosed by BEGIN and END. So we can say that MySQL creates the following six types of 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.

BEFORE and AFTER parameters specify when the trigger is executed, before or after the event.

FOR EACH ROW means that any operation on a record that meets the trigger event will trigger the trigger.

Creating 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

The execution statement list parameter between BEGIN and END indicates multiple statements to be executed, and different statements are separated by semicolons.

Tips:

In general, MySQL uses ; as the end of the execution statement by default, which conflicts with the branch required in the trigger.

To solve this problem, you can use DELIMITER, such as: DELIMITER ||, which can change the end symbol to ||

After the trigger is created, you can use DELIMITER ; to change the end symbol to ;

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 || at the beginning, and a trigger is defined in the middle. Once a deletion operation meets the conditions,

The statements in BEGIN and END will be executed, and then end with ||

Finally, use DELIMITER ; to 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. The replace statement is generally similar to insert. However, 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.

A trigger can be a single SQL statement or multiple SQL code blocks. How to create one?

DELIMITER $ #Change the statement delimiter to $
BEGIN
sql1;
sql2;
...
sqln
END $
DELIMITER ; #Change the statement separator back to the original semicolon ";"

You can also define variables in the BEGIN...END statement, but they can only be used inside the BEGIN...END statement:

DECLARE var_name var_type [DEFAULT value] #Define a variable and specify a default value SET var_name = value #Assign a value to a variable

Use of NEW and OLD:

According to the above table, you can use the following format to use the corresponding data:

NEW.columnname: a column of data in a newly added row OLD.columnname: a column of data in a deleted row

Having said that, let's create a trigger now!

Now the table is as follows:

Users table

CREATE TABLE `users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) 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;

Logs table:

CREATE TABLE `logs` (
  `Id` int(11) 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 : when a piece of data is inserted in users, a log message will be generated in logs.

Create a trigger:

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 was found that the Chinese character encoding was garbled, so the character set was set here SET s2 = " is created";
SET s1 = CONCAT(NEW.name,s2); #CONCAT function can connect strings INSERT INTO logs(log) values(s1);
END $
DELIMITER ;

Here I use navicat:

View Triggers

SHOW TRIGGERS statement to view trigger information

Tip:

I used navicat to create it directly. If you use mysql front, the name will be different. Let's delete the trigger and test it in MySQL front.

drop trigger user_log;#Delete trigger

Open Mysql Front:

When mysql front compiles sql, it does not need to define the end delimiter. The modified sql can be directly as follows:

#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;
SET s2 = " is created";
SET s1 = CONCAT(NEW.name,s2); #CONCAT function can connect strings INSERT INTO logs(log) values(s1);
END #$
#DELIMITER ; 

Here are a few more words:

Tips : The SHOW TRIGGERS statement cannot query the specified trigger

View trigger information in the triggers table

SELECT * FROM information_schema.triggers; 

The result shows the detailed information of all triggers. At the same time, this method can query the detailed information of the specified 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 there is too much trigger information, it is best to specify the query through the TRIGGER_NAME field

Back to the top, we have created the trigger, continue to insert data into users and view the data:

insert into users(name,add_time) values('周伯通',now());

Okay, let's check the logs table again!

From 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 that triggers bring to me!

Limitations and considerations

Triggers have the following two limitations:

1. Triggers cannot call stored procedures that return data to the client, nor can they use dynamic SQL statements that use the CALL statement. However, stored procedures are allowed to return data to triggers through parameters. In other words, stored procedures or functions can return data to triggers through OUT or INOUT type parameters, but procedures that directly return data cannot be called.

2. Statements that start or end transactions explicitly or implicitly, such as START TRANS-ACTION, COMMIT, or ROLLBACK, cannot be used in triggers.

Note : MySQL triggers are executed in the order of BEFORE triggers, row operations, and AFTER triggers. If an error occurs in any step, the remaining operations will not be executed. If an error occurs during operations on transaction tables, the operations will be rolled back. If operations are performed on non-transaction tables, they cannot be rolled back and data may be incorrect.

Summarize

Triggers are triggered based on rows, so deletion, addition or modification operations may activate triggers. Therefore, do not write overly complex triggers, and do not add too many triggers. This will have a serious impact on the insertion, modification or deletion of data, and will also result in poor portability. Therefore, you must consider this when designing triggers.

A trigger is a special stored procedure that is executed when data in a specific table is inserted, deleted, or modified. It has more sophisticated and complex data control capabilities than the standard functions of the database itself.

Database triggers have the following functions:

1. Security. Based on the values ​​of the database, users can be given certain rights to operate the database.

# You can restrict user operations based on time, for example, you cannot modify database data after get off work or on holidays.

# You can restrict user operations based on the data in the database, for example, not allowing the stock price to increase by more than 10% at a time.

2. Audit. Can track user operations on the database.

# Audit statements used by users to operate the database.

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

3. Implement complex data integrity rules

# Implement non-standard data integrity checks and constraints. Triggers can create more complex restrictions than rules. Unlike rules, triggers can reference columns or database objects. For example, a trigger could back out any attempt to take on futures that exceed one's margin.

# Provides mutable default values.

4. Implement complex, non-standard database-related integrity rules. Triggers can perform serial updates on related tables in the database. For example, a delete trigger on the author_code column of the auths table can cause corresponding deletes of matching rows in other tables.

# When modifying or deleting, cascade modify or delete matching rows in other tables.

# When modifying or deleting, set the matching rows in other tables to NULL values.

# When modifying or deleting, set the matching rows in other tables to the default value in cascade.

# Triggers can reject or roll back changes that destroy relevant integrity and cancel transactions attempting to update data. This trigger is triggered when a foreign key is inserted that does not match its primary key. For example, you could create an insert trigger on the books.author_code column so that if the new value doesn't match a value in the auths.author_code column, the insert is rolled back.

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

6. Automatically calculate data values, and if the data value reaches certain requirements, perform specific processing. For example, if the funds in the company's account are less than 50,000 yuan, a warning data will be sent immediately to the financial personnel.

No matter what industry you are in, it is enough to do two things well: one is your profession and the other is your character. Profession determines your existence, and character determines your network. The rest is persistence, and win more trust with kindness, professionalism and sincerity.

This article ends here. I hope it can be helpful to you. I also hope you can pay more attention to more content on 123WORDPRESS.COM!