Shulou(Shulou.com)05/31 Report--

This article shows you how to create a trigger in mysql, which is concise and easy to understand. It will definitely brighten your eyes. I hope you can get something from the detailed introduction of this article.

We can use the CREATE TRIGGER statement to create a new trigger to look at the specific syntax:

CREATE TRIGGER trigger_name trigger_time trigger_event ON table_name FOR EACH ROW BEGIN... END

Then let's take a closer look at the specific meaning of the above sql:

Place the trigger name after the CREATE TRIGGER statement. Trigger names should follow the naming convention [trigger time] _ [table name] _ [trigger event], such as before_employees_update.

Trigger activation time can be before or after. You must specify the activation time that defines the trigger. Use the BEFORE keyword if you want to process the action before the change, and the AFTER keyword if you need to process the action after the change.

The trigger event can be INSERT,UPDATE or DELETE. This event causes the trigger to be called. Triggers can only be called by one event. To define triggers called by multiple events, you must define multiple triggers, one for each event.

The trigger must be associated with a specific table. No table trigger will not exist, so the table name must be specified after the ON keyword.

Place the SQL statement between the BEGIN and END blocks. This is the location where the trigger logic is defined.

With that in mind, let's try to create a trigger to record changes to row data in the employees table. Let's take a look at the structure of the table:

Mysql > DESC employees +-+ | Field | Type | Null | Key | Default | Extra | +- -+ | employeeNumber | int (11) | NO | PRI | NULL | | lastName | varchar (50) | NO | | NULL | | firstName | varchar (50) | NO | | NULL | | extension | varchar (10) | NO | | NULL | | email | varchar (10) | NO | NULL | officeCode | varchar (10) | NO | MUL | NULL | ReportsTo | int (11) | YES | MUL | NULL | jobTitle | varchar (50) | NO | | NULL | | +-+-+ 8 rows in set

Let's create a new table called employees audit to hold changes to the data in the employees table:

CREATE TABLE employees_audit (id INT AUTO_INCREMENT PRIMARY KEY, employeeNumber INT NOT NULL, lastname VARCHAR (50) NOT NULL, changedat DATETIME DEFAULT NULL, action VARCHAR (50) DEFAULT NULL)

Then create a BEFORE UPDATE trigger that is called before recording changes to the rows in the employees table:

DELIMITER $$CREATE TRIGGER before_employee_update BEFORE UPDATE ON employees FOR EACH ROW BEGIN INSERT INTO employees_audit SET action = 'update', employeeNumber = OLD.employeeNumber, lastname = OLD.lastname, changedat = NOW (); END$$DELIMITER

In the body of the trigger above, we use the OLD keyword to access the employeeNumber and lastname columns of the rows affected by the trigger. We should note that in triggers defined for insert, only the NEW keyword can be used. The OLD keyword cannot be used. However, there are no new lines in the triggers defined for DELETE, so you can only use the OLD keyword. In update triggers, OLD refers to the row before it is updated, while NEW is the row after update.

We can then use the SHOW TRIGGERS statement to view the triggers in the database:

Mysql > SHOW TRIGGERS +- -+- -+ -+ | Trigger | Event | Table | Statement | Timing | Created | sql_mode | | Definer | character_set_client | collation_connection | Database Collation | + - -+- -+ | before_employee_update | UPDATE | employees | BEGIN INSERT INTO employees_audit SET action = 'update' EmployeeNumber = OLD.employeeNumber, lastname = OLD.lastname, changedat = NOW () END | BEFORE | 2017-08-02 22 purl 06 purl 36.40 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER NO_ENGINE_SUBSTITUTION | root@localhost | utf8 | utf8_general_ci | utf8_general_ci | +- - -- + +-+-+ 1 row in set

When we're done, we'll update the employees table to see if the trigger is called:

UPDATE employees SET lastName = 'Maxsu'WHERE employeeNumber = 1056

You can use the following query to query the employees_audit table to check whether the trigger is called by the UPDATE statement:

Mysql > SELECT * FROM employees_audit +-+ | id | employeeNumber | lastname | changedat | action | + -+-+ | 1 | 1056 | Hill | 2017-08-02 22:15:51 | update | +-+ 1 row in set the above is how to create a trigger in mysql Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.