Detailed explanation of trigger usage examples in mysql

This article mainly introduces the relevant information of mysql Trigger usage examples. Friends in need can refer to

MySQL Trigger syntax Detailed explanation:

##​​

​ Trigger trigger is a special stored procedure, which inserts ( inset), delete (delete) or modify (update) the data in a specific table. It is triggered when the data is The standard itself has more sophisticated and complex data control capabilities. The trigger is not called by the program, but is triggered by a event. Automatically enforce business rules when data is modified, often used to strengthen data integrityconstraintsand business rules, etc. Triggers can queryother tables and contain replicated sql statements. Triggers can also be used to enforce reference 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; A trigger is a named database object
related to a table that will be 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. The 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, you can use the BEGIN ...

END

compound statement structure. In this way, you can use the same statements allowed in stored subroutines

(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 MySQL5.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 There are restrictions on the use of triggers.

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 (stored procedures are allowed Return data to the trigger program via parameters).

The trigger cannot use statements that explicitly or implicitly start or end a transaction, 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, 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 subprograms, such as conditions and loops, etc. However, as with stored subroutines, 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 to be able to use the character " ;". In the following example, 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 CALL statement from The trigger program calls the stored program. 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;

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