Mysql trigger usage example

Mysql trigger usage example

MySQL trigger syntax explanation:

Trigger is a special stored procedure that triggers execution when inserting (inset), deleting (delete), or modifying (update) data in a specific table, it provides more refined and complex data control capabilities than the data standard. A trigger is triggered by an event instead of a program call. When data is modified, its business rules are automatically enforced, which is often used to enhance data integrity constraints and business rules. The trigger can query other tables and contain the copied SQL statement. Triggers can also be used to forcibly reference integrity. A trigger can force more complex constraints than those defined by the check constraint. Mysql trigger usage example

(1). create trigger syntax

Create trigger trigger_nametrigger_time trigger_event ON tbl_name for each row trigger_stmt;

A trigger is a table-related named database object. This object is activated when a specific event occurs on the table.

The trigger program is related to the table named tbl_name. Tbl_name must reference a permanent table. You cannot associate the trigger program with the TEMPORARY table or view.

Trigger_time is the time when the program is triggered. It can be BEFORE or AFTER to indicate that the trigger program is triggered BEFORE or AFTER its statement is activated.

Trigger_event indicates the type of statements used to activate the trigger program. Trigger_event can be one of the following values:

(1). INSERT: activates the trigger program when a new row is inserted into the table, for example, through INSERT, load data, and REPLACE

Statement.

(2). UPDATE: Activate the trigger when changing a line. For example, use the UPDATE statement.

(3). DELETE: the trigger program is activated when a row is deleted from the table, for example, through the DELETE and REPLACE statements.

Note that trigger_event is not very similar to the SQL statement used to activate the trigger program in the form of table operations, which is very important. For example, the BEFORE trigger program for INSERT can be activated by both the INSERT statement and the load data statement. One of the examples that may cause confusion is insert .. on duplicate update... syntax: The before insert trigger will be activated for each row, followed by the after insert trigger program, or the before update and after update trigger programs, depending on whether there is a duplicate key on the row.

For a given table with the same triggering program action time and event, there cannot be two triggering programs. For example, a table cannot have two before update triggers. However, there can be one before update trigger program, one before insert trigger program, one BEFOREUPDATE trigger program, and one after update trigger program. Trigger_stmt is the statement executed when the trigger program is activated. If you want to execute multiple statements, you can use the in... END compound statement structure. In this way, the same statements allowed in the stored subroutine can be used.

(2). drop trigger syntax

Drop trigger [schema_name.] trigger_name discard the TRIGGER program. The solution name (schema_name) is optional. If schema is omitted, the trigger program is discarded from the current scheme.

Note: When you upgrade MySQL versions earlier than MySQL 5.0.10 to 5.0.10 or later (including all MySQL versions), you must discard all triggers before the upgrade, and re-create them later. Otherwise, the drop trigger will not work after the upgrade. The drop trigger statement requires the SUPER permission.

(3) Use the trigger Program

This section describes how to use the trigger program in MySQL 5.1, and introduces restrictions on the use of the trigger program.

A trigger is a table-related named database object. This object is activated when a specific event occurs on the table. In some trigger program usage, it can be used to check the values inserted into the table or calculate the values involved in the update.

The trigger program is related to the table. When an INSERT, DELETE, or UPDATE statement is executed on the table, the trigger program is activated. You can set the trigger program to be activated before or after the statement is executed. For example, you can activate the trigger program after deleting each row in the table or updating each row. To CREATE or discard a TRIGGER program, use the create trigger or drop trigger statement. the trigger program cannot CALL the storage program that returns data to the client, nor use dynamic SQL statements that use the CALL Statement (allow the storage program to return data to the trigger program through parameters ).

The trigger program cannot use statements that START or end transactions explicitly or implicitly, such as start transaction,

COMMIT or ROLLBACK.

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

In the INSERT trigger program, only NEW. col_name can be used, and no old rows exist. In the DELETE trigger program, only OLD. col_name can be used, and no new lines exist. In the UPDATE trigger program, you can use OLD. col_name to reference the columns of a row before UPDATE, or use NEW. col_name to reference columns in the updated row.

Columns named after OLD are read-only. You can reference it, but you cannot change it. If you have SELECT permission for a column named NEW, you can reference it. In the BEFORE trigger program, if you have the UPDATE permission, you can use "set new. col_name = value" to change its value. This means that you can use a trigger program to change the value to be inserted into a new row or update the value of a row. In the BEFORE trigger program, the NEW value of the AUTO_INCREMENT column is 0, rather than the serial number automatically generated when a NEW record is inserted.

You can use the BEGIN... END structure to define the trigger program for executing multiple statements. In the BEGIN block, other syntaxes, such as conditions and loops, can also be used to store subprograms. However, when defining the trigger program for executing multiple statements as the storage subroutine, if the mysql program is used to enter the trigger program, you need to redefine the statement separator, so that the character ";" can be used in the trigger program definition. The following example shows the key points. In this example, an UPDATE trigger is defined to check the new value that will be used when each row is updated, and change the value so that it is located between 0 and ~ Within the range of 100. It must be a BEFORE trigger, because it needs to be checked BEFORE the value is used for updating rows:

Mysql> delimiter //

Mysql> create trigger upd_check before update on account

-> FOR EACH ROW

-> BEGIN

-> If new. amount <0 THEN

-> Set new. amount = 0;

-& Gt; elseif new. amount & gt; 100 THEN

-& Gt; set new. amount = 100;

-> End if;

-> END ;//

Mysql> delimiter;

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

(1) If BEFORE fails to trigger the program, the operation on the corresponding row is not executed.

(2) The AFTER trigger program is executed only when both the BEFORE trigger Program (if any) and the row operation are successful.

(3) If an error occurs during execution of the BEFORE or AFTER trigger program, the entire statement of the trigger program will fail to be called.

(4) For a transaction table, if the triggering program fails (and the resulting entire statement fails), all the changes executed by the statement will be rolled back. This type of rollback cannot be performed for non-transactional tables. Therefore, any changes made before the failure even if the statement fails.Mysql trigger usage example

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;