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

This article mainly introduces how to write mysql trigger, which has certain reference value and can be used for reference by friends who need it. I hope you will learn a lot after reading this article. Next, let the editor take you to learn about it.

A trigger is a database object associated with an MySQL data table that fires when a definition condition is met and executes a collection of statements defined in the trigger. This feature of triggers can help applications ensure data integrity on the database side.

The syntax format is as follows:

CREATE

ON FOR EACH Row

The syntax is explained as follows.

1) trigger name

The name of the trigger, which must have a unique name in the current database. If you want to create it in a particular database, the name should be preceded by the name of the database.

2) INSERT | UPDATE | DELETE

A trigger event that specifies the type of statement that activates the trigger.

Note: the execution time of the three triggers is as follows. INSERT: activates the trigger when a new row is inserted into the table. For example, INSERT's BEFORE trigger can be activated not only by MySQL's INSERT statement, but also by LOAD DATA statement. DELETE: triggers, such as DELETE and REPLACE statements, are activated when a row of data is deleted from a table. UPDATE: triggers are activated when a row of data in a table is changed, such as a UPDATE statement.

3) BEFORE | AFTER

BEFORE and AFTER, when the trigger is triggered, indicates that the trigger is fired 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.

4) 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. For example, for a data table, you cannot have two BEFORE UPDATE triggers at the same time, but you can have one BEFORE UPDATE trigger and one BEFORE INSERT trigger, or one BEFORE UPDATE trigger and one AFTER UPDATE trigger.

5) 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.

6) FOR EACH ROW

It generally refers to the row-level trigger, and the action of the trigger is activated for each row affected by the trigger event. For example, when you insert multiple rows of data into a table using an INSERT statement, the trigger performs the corresponding trigger action for each row of data insertion.

Note: each table supports BEFORE and AFTER for INSERT, UPDATE, and DELETE, so each table supports up to six triggers. Only one trigger is allowed per event per table. A single trigger cannot be associated with multiple events or tables.

In addition, in MySQL, if you need to view triggers that already exist in the database, you can use the SHOW TRIGGERS statement.

Example:

BEGIN IF new.state in then INSERT INTO `userservice`.`ehome _ us_green_ examine` (modifydate,modifyuser,brancCode,stateDescription,state,userID,fromSystemkey,applydate,makeHospitalName,illnessName,userName,rightsholderName,tranno) VALUES (sysdate (), 'manage', old.brancCode, old.stateDescription, new.state,'', old.fromSystemkey, old.applydate,old.makeHospitalName,old.illnessName,old.userName,old.rightsholderName, old.tranno); END IF; END

This trigger is relatively simple.

1. Between BEGIN and END is the operation of the database.

2. Between IF and END IF is the judgment, you are right, that is, if. The meaning of

3. "new.state in" means when the field state of the table is (2 or 3).

4. Execute the following insert statement. New is the new value and old is the original value (for example, old.userName is the value of the original field).

5. The whole trigger is translated into vernacular. If the state of the table becomes 2 or 3, execute the following insert statement

Note: does the trigger occur before or after the state change? Choose Before or After, as the name implies!

Thank you for reading this article carefully. I hope it will be helpful for everyone to share how the mysql trigger is written. At the same time, I also hope that you will support it, pay attention to the industry information channel, and find out if you have any problems. Detailed solutions are waiting for you to learn!

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.