Shulou(Shulou.com)06/01 Report--

This article is about how mysql creates triggers. The editor thought it was very practical, so I shared it with you as a reference. Let's follow the editor and have a look.

MySQL to create a trigger is to first create a 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 set of statements defined in the trigger

Trigger

Trigger is one of the database objects of MySQL, which is very similar to the functions in programming language, which need to be declared, executed and so on. However, the execution of the trigger is not called by the program, nor started by hand, but triggered and activated by events to achieve execution. It's a bit like an event in DOM.

The creation of triggers

The syntax for creating a trigger is as follows:

CREATE

ON FOR EACH Row

Grammatical analysis

Trigger name

Refers to the trigger name and is unique in the database (if you need to add the database name if established in a particular database)

INSERT | UPDATE | DELETE

Represents a trigger event that specifies the type of statement to activate the trigger

INSERT: activate the trigger when a new row is inserted into the table

DELETE: activates a trigger when a row of data is deleted from a table

UPDATE: activates a trigger when a row of data in a table is changed

BEFORE | AFTER

The time when a trigger is triggered indicates that the trigger is triggered before or after the statement that activates it. Use the BEFORE option if you want to verify that the new data meets the criteria; if you want to make 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, which must be a permanent table and cannot be associated with a temporary table or view. The trigger is activated only when an event is triggered on the table. The same table cannot have two triggers with the same trigger time and event.

Trigger body

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

FOR EACH ROW

Refers to a row-level trigger that activates the trigger for each row affected by the triggered event.

Note: only one trigger can be defined for the same trigger event of the same table at the same trigger time. Triggers can only be created on permanent tables, not on 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, which is triggered by inserting data into the data table tb_emp1 and then inserting 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.

Thank you for reading! On how to create triggers for mysql to share here, I hope the above content can be of some help to you, so that you can learn more knowledge. If you think the article is good, you can share it and let more people see it.

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.