How to write mysql trigger

Triggers are database objects related to MySQL data tables. They are triggered when defined conditions are met and execute the set 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 <触发器名> < BEFORE | AFTER >
<INSERT | UPDATE | DELETE >
ON <表名> FOR EACH Row<触发器主体>

The syntax description is as follows.

1) Trigger name

The name of the trigger. The trigger must have a unique name in the current database. If you are creating in a specific database, the name should be preceded by the name of the database.

2) INSERT | UPDATE | DELETE

trigger event, used to specify the type of statement that activates the trigger.

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

3) BEFORE | AFTER

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

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

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

6) FOR EACH ROW

Generally refers to row-level triggering, and the trigger action must be activated for each row affected by the triggering event. For example, when using the INSERT statement to insert multiple rows of data into a table, the trigger will execute the corresponding trigger action for each row of data inserted.

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

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

Example:

BEGIN    
IF new.state  in ('2','3')  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, BEGIN to END, between which is the database Operation

2. Between IF and END IF is judgment. What you think is right is if. . . The meaning of

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

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

5. The entire trigger is translated into vernacular, if the state of the table When it becomes 2 or 3, execute the following insert statement

Note: Does the trigger occur before or after the status change? Choose Before or After, as the name suggests!

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