Shulou(Shulou.com)06/01 Report--

Editor to share with you how to achieve mysql trigger, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to understand it!

MySQL trigger syntax details:

Trigger trigger is a special stored procedure that triggers execution when inserting (inset), deleting (delete), or modifying (update) data in a particular table. It has finer and more 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 enforces its business rules when there is data modification, which is 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 grammar

CREATE TRIGGER trigger_nametrigger_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 a table named tbl_name. Tbl_name must reference the persistence 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 triggered before or after the statement that activates it.

Trigger_event indicates the type of statement that activates the trigger. Trigger_event can be one of the following values:

(1) .insert: activates the trigger when a new row is inserted into the table, for example, through INSERT, LOAD DATA, and REPLACE

Statement.

Update: activates the trigger when a row is changed, for example, through the UPDATE statement.

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

It is important to note that trigger_event is not very similar to activating the SQL statement of the trigger in a table operation. For example, the BEFORE trigger for INSERT can be activated not only by INSERT statements, but also by LOAD DATA statements. One of the examples that can cause confusion is INSERT INTO.. ON DUPLICATE UPDATE... Syntax: BEFORE INSERT triggers will be activated for each line, followed by AFTER INSERT triggers, or BEFORE UPDATE and AFTER UPDATE triggers, depending on whether there are duplicate keys on the line.

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

(2). DROP TRIGGER grammar

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

Note: when upgrading from MySQL versions prior to MySQL 5.0.10 to 5.0.10 or later (including all MySQL5.1 versions), you must discard all triggers before the upgrade and recreate them later, otherwise, DROP TRIGGER will not work after the upgrade. The DROP TRIGGER statement requires SUPER permission.

(III)。 Use the trigger program

In this section, it describes how to use triggers in MySQL 5.1 and describes the limitations in 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 the use of some triggers, it can be used to check the values inserted into the table or to calculate the values involved in the update.

The trigger is table-related and is activated when an INSERT, DELETE, or UPDATE statement is executed against the table. The trigger can be set to be activated before or after the statement is executed. For example, you can activate the trigger before deleting each row from the table, or after updating each row. To create or discard triggers, use CREATE TRIGGER or DROP TRIGGER statements. Triggers cannot call stored procedures that return data to the client, nor can they use dynamic SQL with CALL statements (allowing stored programs to return data to triggers through parameters).

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

COMMIT or ROLLBACK.

Using the OLD and NEW keywords, you can access columns in rows affected by the trigger (OLD and NEW are not case-sensitive).

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

Columns named with OLD are read-only. You can quote it, but you can't change it. For a column named after NEW, you can reference it if you have SELECT permission. In the BEFORE trigger, if you have UPDATE permission, you can use "SET NEW.col_name = value" to change its value. This means that you can use the trigger to change the value to be inserted into the new row, or to update the value of the row. In the BEFORE trigger, the new value of the AUTO_INCREMENT column is 0, which is 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 triggers that execute multiple statements. In BEGIN blocks, other syntax allowed in storage subroutines, such as conditions and loops, can also be used. However, as with the storage subroutine, when defining 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 the character ";" can be used in the trigger definition. These points are demonstrated in the following example. In this example, a UPDATE trigger is defined to check for the new value that will be used when updating each row, and to change the value so that it is within the range of zero 100. It must be a BEFORE trigger because you need to check the value before using it to update the row:

Mysql > delimiter / / mysql > CREATE TRIGGER upd_check BEFORE UPDATE ON account-> FOR EACH ROW-> BEGIN-> IF NEW.amount

SET NEW.amount = 0;-> ELSEIF NEW.amount > 100THEN-> SET NEW.amount = 100;-> END IF;-> END;//mysql > delimiter

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

(1) if the BEFORE trigger fails, the action on the corresponding line is not performed.

(2) execute the BEFORE trigger only if the AFTER 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 to call the trigger program to fail.

(4) for transactional tables, if the trigger fails (and the resulting failure of the entire statement), all changes executed by the statement will be rolled back. For non-transactional tables, such a rollback cannot be performed, so any changes made before the failure are valid even if the statement fails.

Example 1:

Mysql > CREATE TABLE account (acct_num INT, amount DECIMAL (10Power2)); mysql > CREATE TRIGGER ins_sum BEFORE INSERT ON account FOR EACH ROW SET @ sum = @ sum + NEW.amount; above is all about how mysql triggers are implemented. Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.