MySQL Trigger analysis

A trigger is a special kind of stored procedure that triggers execution when inserting, deleting, or modifying data in a particular table, which has finer and more complex data control than the standard functionality of the database itself.

Database triggers have the following effects:

1. security. You can make a user have some right to manipulate the database based on the value of the database.

# You can limit user actions based on time, such as not allowing database data to be modified after work and holidays.

# You can limit the user's actions based on data in the database, such as not allowing the price of the stock to increase by more than 10% at a time.

2. Audit. You can track user actions on a database.

# Audit the statements of the user operations database.

# writes the user's updates to the database to the audit table.

3. Implementing complex data integrity rules

# implements non-standard data integrity checks and constraints. Triggers can produce more complex restrictions than rules. Unlike rules, triggers can reference columns or database objects. For example, a trigger can rewind any futures that attempt to eat more than their own margin.

# provides a variable default value.

4. implement complex non-standard database-related integrity rules. Triggers can be used for serial updates of related tables in the database. For example, a delete trigger on the Auths table author_code column causes the corresponding row to be deleted in the other table.

# cascade Modify or delete rows in other tables that match them when modified or deleted.

# Sets the row in the other table to a null value when modified or deleted.

# Sets the row cascade in the other table to be the default value when modified or deleted.

# triggers can reject or rollback changes that disrupt related integrity and cancel transactions that attempt to update data. This trigger works when you insert a foreign key that does not match its primary health. For example, an INSERT trigger can be generated on the Books.author_code column, and if the new value does not match a value in the Auths.author_code column, the insert is rolled back.

5. synchronously replicates the data in the table in real time.

6. automatically calculates the data value, if the value of the data meets certain requirements, the specific processing. For example, if the company's account has less than $50,000 in money, it immediately sends warning data to the financial officer.

The MySQL trigger syntax is detailed.

㈠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 related to a table that is activated when a specific event occurs on the table.
The trigger is related to a table named Tbl_name. Tbl_name must reference a persistent table. The triggering program cannot be associated with
Temporary the table or view associated.
Trigger_time is the action time of the triggering program. It can be before or after to indicate that the trigger was triggered before or after the statement that activated it.
Trigger_event indicates the type of statement that activates the trigger. Trigger_event can be one of the following values:
· Insert: Activates the trigger when inserting a new row into the table, for example, through INSERT, LOAD data, and replace
Statement.
· Update: Activates a trigger when a row is changed, for example, through an UPDATE statement.
· Delete: Activates the trigger 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 as a table operation. 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 of possible confusion is insert into. On DUPLICATE UPDATE ... Syntax: Before INSERT
The trigger is activated for each row, followed by the after insert trigger, or before update and after
Update triggers, depending on whether there are duplicate keys on the line.
There cannot be two triggers for a given table with the same trigger action time and event. For example, for a table, do not
Can have two before update triggers. But there can be 1 before update triggers and one before
Insert trigger, or 1 before update trigger and one after update trigger.
TRIGGER_STMT is the statement that executes when the triggering program is activated. If you plan to execute multiple statements, you can use the BEGIN ... End Complex
Statement structure. This allows you to use the same statements that are allowed in the stored subroutine

㈡drop Trigger Syntax

DROP TRIGGER [schema_name.] Trigger_name
Discard the triggering program. The scheme name (schema_name) is optional. If schema is omitted, the current party
The triggering procedure is discarded in the case.
Note: When upgrading to 5.0.10 or later (including all MySQL version 5.1 versions) from MySQL prior to MySQL 5.0.10
), you must discard all triggers before upgrading and recreate them later, or, after the upgrade, drop
Trigger does not work.
The DROP trigger statement requires Super permissions.

㈢ using the Trigger program

In this section, the methods for using triggers in MySQL 5.1 are described, and the limitations on using triggers are described.
A trigger is a named database object related to a table that is activated when a specific event occurs on the table. In some triggering processes
Can be used to check the values inserted into the table or to evaluate the values involved in the update.
The trigger is related to the table, and the trigger is activated when an INSERT, delete, or UPDATE statement is executed against the table. can add
The trigger is set to be activated before or after the execution of the statement. For example, you can delete each row from the table, or update the
Activates the trigger after each line.
To create a trigger or discard a trigger, you can use the CREATE TRIGGER or DROP TRIGGER statement

· The trigger cannot invoke the stored program that returns the data to the client, nor can it use dynamic SQL with the call statement
(Allows the stored program to return data to the trigger via parameters).
· The trigger cannot use statements that begin or end a transaction either explicitly or implicitly, such as Start TRANSACTION,
Commit or rollback.
Using the old and new keywords, you can 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, with no old rows. In the delete trigger, you can only use the
Old.col_name, no new lines. In the update trigger, you can use Old.col_name to refer to a pre-update
Rows, you can also use New.col_name to refer to columns in the updated row.
A column named with old is read-only. You can reference it, but you can't change it. For a column named with new, if you have a
Select permission to refer to it. In the Before trigger, if you have update permissions, you can use the SET NEW.
Col_name = value "To change its value. This means that you can use the trigger to change the value that will be inserted into the new row.
Or the value used to update the row.
In the Before trigger, the new value of the Auto_increment column is 0, and it is not automatically raised when the record is actually inserted
The serial number.

By using the BEGIN ... End structure to define triggers that execute multiple statements. In the Begin block, you can also use the storage
Other syntax allowed in subroutines, such as conditions and loops. However, as with stored subroutines, it is defined that executing multiple statements
When triggering a program, if you use a MySQL program to enter a trigger, you need to redefine the statement delimiter to be able to trigger
The character ";" is used in the program definition. In the following example, these points are demonstrated. In this example, you define a
Update trigger, which checks for new values to be used when updating each row, and changes the value so that it is in the range of 0~100
Within It must be a before trigger, because it needs to be checked before the value is used to update the 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;
ELSEIF New.amount >
SET new.amount = 100;
-END IF;
end;//
Mysql> delimiter;
A simpler approach is to define the stored program separately and then invoke the stored program from the trigger using a simple call statement.
This method is also helpful if you are going to invoke the same subroutine from within several trigger programs.
During the execution of the trigger, MySQL handles the error in the following way:
· If the before trigger fails, the action on the corresponding line is not performed.
· The after trigger is executed only if the before trigger (if any) and the row operation have been executed successfully.
· If an error occurs during the execution of a before or after trigger, the entire language of the triggering program is called
The failure of the sentence.
· For a transactional table, if the trigger fails (and the resulting failure of the entire statement), the statement executes the
All changes are rolled back. For non-transactional tables, this type of rollback cannot be performed, so even if the statement fails, the
Any changes are still valid.

Example one:
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;