mysql trigger usage example sharing

This article mainly introduces the relevant information about the detailed explanation of mysql trigger usage examples. Friends who need it can refer to it. I hope it can help everyone.

Detailed explanation of MySQL trigger syntax:

The trigger is a special stored procedure that inserts (inset), deletes (delete) or modifies (update) a specific table. Execution is triggered when the data in it is executed, and it has more refined and complex data control capabilities than the standard functions of the data itself. Triggers are not called by a program, but are triggered by an event. Automatically enforce business rules when data is modified, often used to strengthen data integrity constraints and business rules. Triggers can query other tables and contain replicated SQL statements. Triggers can also be used to enforce referential integrity. Triggers can enforce more complex constraints than those defined with check constraints.

(1).CREATE TRIGGER syntax

CREATE TRIGGER trigger_nametrigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt;
The trigger is a named database object related to the table. This object is activated when a specific event occurs on the table.
The trigger is related to the table named tbl_name. tbl_name must refer to a permanent table. A trigger cannot be associated with a TEMPORARY table or view.
                    Trigger_time is the action time of the trigger program. It can be BEFORE or AFTER to indicate whether the trigger is fired before or after the statement that activates it.
         trigger_event specifies the type of statement that activates the trigger program. trigger_event can be one of the following values:

(1).INSERT: The trigger is activated when a new row is inserted into the table, for example, through INSERT, LOAD DATA and REPLACE
statements.
(2).UPDATE: The trigger is activated when a row is changed, for example, through the UPDATE statement.
        (3).DELETE: The trigger is activated when a row is deleted from the table, for example, through the DELETE and REPLACE statements.

It is important to note that trigger_event is not very similar to the SQL statement that activates the trigger program through table operations. For example, the BEFORE trigger on INSERT can be activated not only by the INSERT statement, but also by the LOAD DATA statement. One example that may cause confusion is INSERT INTO .. ​​ON DUPLICATE UPDATE ... Syntax: BEFORE INSERT trigger will activate for each row, followed by AFTER INSERT trigger, or BEFORE UPDATE and AFTER UPDATE trigger, depending Are there duplicate keys on the row.

There cannot be two triggers for a given table with the same trigger action time and event. For example, for a certain table, there cannot be two BEFORE UPDATE triggers. But there can be 1 BEFORE UPDATE trigger and 1 BEFORE INSERT trigger, or 1 BEFOREUPDATE trigger and 1 AFTER UPDATE trigger. trigger_stmt is the statement executed when the trigger is activated. If you plan to execute multiple statements, use the BEGIN ... END compound statement structure. In this way, you can use the same statements allowed in stored subprograms

(2).DROP TRIGGER syntax

DROP TRIGGER[schema_name.]trigger_name discards the trigger program. The schema name (schema_name) is optional. If schema is omitted, the trigger will be discarded from the current schema.

Note: When upgrading from a MySQL version before MySQL 5.0.10 to 5.0.10 or later (including all MySQL 5.1 versions), all triggers must be discarded before upgrading. and recreate them later, otherwise, DROP TRIGGER does not work after upgrade. The DROP TRIGGER statement requires SUPER permission.

(3). Using trigger programs

In this section, we introduce the method of using trigger programs in MySQL 5.1 and introduce the use of trigger programs. limits.

A trigger is a named database object associated with a table that is activated when a specific event occurs on the table. In some trigger usages, it can be used to check values ​​inserted into a table, or to perform calculations on values ​​involved in an update.

The trigger program is related to the table. When an INSERT, DELETE or UPDATE statement is executed on the table, the trigger program will be activated. Triggers can be set to activate before or after a statement is executed. For example, a trigger can be activated before each row is deleted from the table, or after each row is updated. To create a trigger or discard a trigger, use the CREATE TRIGGER or DROP TRIGGER statement. A trigger cannot call a stored procedure that returns data to the client, nor can it use dynamic SQL using the CALL statement (which allows stored procedures to return data to the trigger through parameters). program).

            Triggers cannot use statements that explicitly or implicitly start or end transactions, such as START TRANSACTION,
COMMIT, or ROLLBACK.

Use the OLD and NEW keywords to access the columns in the rows affected by the trigger (OLD and NEW are not case-sensitive).

In the INSERT trigger program, only NEW.col_name can be used, no old rows. In a DELETE trigger, only OLD.col_name can be used, no new lines. In the UPDATE trigger, you can use OLD.col_name to refer to a column in a row before the update, or NEW.col_name to refer to a column in the updated row.

Columns named with OLD are read-only. You can reference it, but not change it. For columns named with NEW, you can reference it if you have SELECT permission. In the BEFORE trigger program, if you have UPDATE permission, you can use "SET NEW.col_name = value" to change its value. This means that you can use triggers to change values ​​that will be inserted into new rows, or to update values ​​in rows. In the BEFORE trigger program, the NEW value of the AUTO_INCREMENT column is 0, which is not the sequence number that will be automatically generated when a new record is actually inserted.

By using the BEGIN...END structure, you can define a trigger program that executes multiple statements. In the BEGIN block, you can also use other syntax allowed in stored subroutines, such as conditions and loops. However, just like stored subprograms, when defining a trigger program that executes multiple statements, if you use the MySQL program to enter the trigger program, you need to redefine the statement separator so that you can use the character ";" in the trigger program definition. In the example below, these points are demonstrated. In this example, an UPDATE trigger is defined that checks for the new value that will be used when updating each row and changes the value so that it is in the range of 0 to 100. It must be a BEFORE trigger because the value needs to be checked before it is used to update the row:

mysql> delimiter //
mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account
   -> FOR EACH ROW
   -> BEGIN
   -> IF NEW.amount < 0 THEN
   -> SET NEW.amount = 0;
   -> ELSEIF NEW.amount > 100 THEN
   -> SET NEW.amount = 100;
   -> END IF;
   -> END;//
mysql> delimiter ;

It is simpler to define the stored procedure separately and then use a simple The CALL statement calls a stored procedure from the triggering procedure. This method is also helpful if you plan to call the same subroutine from within several trigger programs. During the execution of the trigger program, MySQL handles errors as follows:

(1) If the BEFORE trigger program fails, the operation on the corresponding line will not be executed.
(2) The AFTER trigger is executed only if the BEFORE trigger (if any) and the row operation have been successfully executed.
(3) If an error occurs during the execution of the BEFORE or AFTER trigger program, it will cause the entire statement that calls the trigger program to fail.
(4) For transactional tables, if the trigger program fails (and the resulting failure of the entire statement), all changes performed by the statement will be rolled back. For non-transactional tables, this type of rollback cannot be performed, so even if the statement fails, any changes made before the failure are still valid.

Example 1:

mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account FOR EACH ROW SET @sum = @sum + NEW.amount;

Related recommendations:

##Mysql triggers are used for information backup in PHP projects. Restoration and clearing

Questions about MySQL triggers

Introduction to mysql triggers and how to create and delete triggers

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