Detailed example of mysql trigger usage

MySQL trigger syntax details:

    A trigger is a special stored procedure that is executed when data in a specific table is inserted, deleted, or updated. It provides more sophisticated and complex data control capabilities than the standard functions of the data itself . A trigger is not called by a program, but is 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.

(I) CREATE TRIGGER syntax

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

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

It is important to note that trigger_event is not very similar to the SQL statement that activates the trigger as a table operation. For example, a BEFORE trigger on INSERT can be activated not only by an INSERT statement but also by a LOAD DATA statement. One example where this can be confusing is the INSERT INTO .. ​​ON DUPLICATE UPDATE ... syntax: a BEFORE INSERT trigger will activate for each row, followed by an AFTER INSERT trigger, or a BEFORE UPDATE and an AFTER UPDATE trigger, depending on whether there are duplicate keys on the rows.

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

(II) DROP TRIGGER syntax

DROP TRIGGER [schema_name.] trigger_name drops the trigger. The schema name (schema_name) is optional. If schema is omitted, triggers will be dropped from the current schema.

Note: When upgrading from a MySQL version prior to MySQL 5.0.10 to MySQL 5.0.10 or later (including all MySQL 5.1 versions), you must drop all triggers before upgrading and re-create them afterwards; otherwise, DROP TRIGGER will not work after the upgrade. The DROP TRIGGER statement requires the SUPER privilege.

(III) Using trigger program

This section describes how to use triggers in MySQL 5.1 and describes the limitations of using 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 uses of triggers, it can be used to check values ​​inserted into a table, or to perform calculations on values ​​involved in an update.

A trigger is associated with a table and is activated when an INSERT, DELETE, or UPDATE statement is executed on the table. 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 a table, or after each row is updated. To create or drop a trigger, use the CREATE TRIGGER or DROP TRIGGER statements. Triggers cannot call stored procedures that return data to the client, nor can they use dynamic SQL using the CALL statement (which allows stored procedures to return data to the trigger through parameters).

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

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

In an INSERT trigger, only NEW.col_name can be used, not the old row. In a DELETE trigger, only OLD.col_name can be used, no new rows. In an UPDATE trigger, you can use OLD.col_name to refer to the columns of a row before the update, and you can use NEW.col_name to refer to the columns of the row after the update.

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

By using the BEGIN ... END structure, you can define a trigger that executes multiple statements. In the BEGIN block, you can also use other syntax allowed in stored subroutines, such as conditionals and loops. However, just as with stored subroutines, when you define a trigger that executes multiple statements, if you use the mysql program to enter the trigger, you need to redefine the statement delimiter so that you can use the character ";" in the trigger definition. In the following example, these points are demonstrated. In this example, an UPDATE trigger is defined that checks the new value to be used when each row is updated and changes the value to be in the range 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;

A simpler approach is to define a stored procedure separately and then call the stored procedure from the trigger using a simple CALL statement. This method is also helpful if you plan to call the same subroutine from within several triggers. During the execution of a trigger, MySQL handles errors as follows:

(1) If the BEFORE trigger fails, the operation on the corresponding row is not performed.
(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 a BEFORE or AFTER trigger, the entire statement that called the trigger will fail.
(4) For transactional tables, if the trigger fails (and thus the entire statement fails), all changes performed by the statement are rolled back. For nontransactional tables, this type of rollback cannot be performed, so even if a statement fails, any changes made before the failure remain in effect.

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;

 

Thank you for reading, I hope it can help you, thank you for your support of this site!