How to create a trigger in mysql

To create a trigger in MySQL, you must first create the table to be stored in the trigger, then set the time when the trigger is activated, and finally trigger when the defined conditions are met, and execute the actions defined in the trigger. Statement collection

[Recommended course: MySQL Tutorial]

Trigger

Trigger is one of MySQL's database objects. It is very similar to functions in programming languages ​​and requires declaration, execution, etc. However, the execution of a trigger is not called by a program or started manually, but is triggered and activated by events to achieve execution. Somewhat similar to events in the DOM.

Creation of triggers

The syntax for creating triggers is as follows:

CREATE <触发器名> < BEFORE | AFTER >
<INSERT | UPDATE | DELETE >
ON <表名> FOR EACH Row<触发器主体>

Grammar analysis

Trigger name

refers to the trigger name and is unique in the database (if it is created in a specific database, the database name needs to be added )

INSERT | UPDATE | DELETE

represents a trigger event and is used to specify the type of statement that activates the trigger

INSERT: Insert a new row The trigger is activated when the table

DELETE: The trigger is activated when a row of data is deleted from the table

UPDATE: The trigger is activated when a row of data in the table is changed

BEFORE | AFTER

The moment when the trigger is fired, indicating whether the trigger is fired before or after the statement that activates it. If you want to verify that the new data meets the conditions, use the BEFORE option; if you want to complete several or more changes after the statement that activates the trigger is executed, you usually use the AFTER option.

Table name

The table name associated with the trigger. This table must be a permanent table. Triggers cannot be associated with temporary tables or views. The trigger is activated when a trigger event occurs on the table. The same table cannot have two triggers with the same firing time and event.

Trigger body

The trigger action body contains the MySQL statement that will be executed when the trigger is activated. If you want to execute multiple statements, you can use the BEGIN…END compound statement structure.

FOR EACH ROW

refers to row-level triggering, and the trigger action must be activated for each row affected by the triggering event.

Note: Only one trigger can be defined for the same trigger event with the same trigger time in the same table. Triggers can only be created on permanent tables, not temporary tables.

Example: Create a trigger named double_salary

double_salary
   -> AFTER INSERT ON tb_emp1
   -> FOR EACH ROW
   -> INSERT INTO tb_emp2
   -> VALUES (NEW.id,NEW.name,deptId,2*NEW.salary);
Query OK, 0 rows affected (0.25 sec)

The meaning of the above code is to create a trigger for double_salary, and the triggering condition is to After inserting data into the data table tb_emp1, insert the same data into the data table tb_emp2, and the salary is twice the value of the newly inserted salary field in tb_emp1.

Summary: The above is the entire content of this article, I hope it will be helpful to everyone.

The above is the detailed content of How to create a trigger in mysql. For more information, please follow other related articles on the PHP Chinese website!