The role and syntax of MySQL triggers

A trigger is a special kind of stored procedure that triggers a run when inserting, deleting, or altering 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. The ability to have a database-based value gives the user some right to manipulate the database.

# ability to limit user actions based on time, such as not agreeing to change database data after work and holidays.

# Ability to restrict user actions based on data in a database, such as the price of a stock that is not agreed to increase by more than 10%.

2. Audit. Ability to 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, triggers can be rolled back regardless of what attempts to eat into futures that exceed their margin.

# provides a variable default value.

4. implement complex non-standard database-related integrity rules. Triggers are capable of serial updating of related tables in the database. For example, a delete trigger on the Auths table author_code column causes the corresponding delete row to match in the other table.

# cascade changes or delete rows in other tables that match in changes or deletions.

# Set the matching rows in other tables to null values when changing or deleting.

# set the row cascade in the other table to the default value when changing or deleting.

# triggers can reject or roll back changes that disrupt related integrity and cancel transactions that attempt to update data. Such triggers work 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. take the initiative to calculate the data values, assuming that the value of the data to meet certain requirements, then the specific processing. For example, suppose the company's account has less than $50,000 in money and immediately sends a warning to a financial officer.

MySQL trigger syntax is explained in detail.
㈠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 either 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. Like what
The Before trigger on insert can be activated not only by the INSERT statement, but also by the load data statement.
One of the examples that can cause 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
The update trigger, depending on whether there are repeated 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, no
Can have two before update triggers. But can have 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 runs when the triggering program is activated. Assuming you plan to run multiple statements, you can use the BEGIN ... End Complex
Statement structure. This way, you can use the same statement that you agreed to in the stored subroutine

㈡drop Trigger Syntax
DROP TRIGGER [schema_name.] Trigger_name
Discard the triggering program. The scheme name (schema_name) is optional. Assuming that the schema (scheme) is omitted, the current party
The triggering procedure is discarded in the case.
Gaze: When upgrading from MySQL version number prior to MySQL 5.0.10 to 5.0.10 or later (contains all MySQL version 5.1
This), you must discard all triggers before the upgrade and create them again later, otherwise, 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 values inserted into a table, or to evaluate the values involved in an update.
Triggers are related to tables, and triggers are activated when an INSERT, delete, or UPDATE statement is run against a table. To be able to
The trigger is set to be activated before or after the statement is run. 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
(The consent of the stored program to return data to the trigger via the parameters).
· The trigger cannot use statements that start or end a transaction either explicitly or implicitly, such as Start TRANSACTION,
Commit or rollback.
Using old and Newkeyword, you can access columns in rows affected by the trigger (old and new do not differentiate between uppercase and lowercase).
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 previous
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, assume that you have a
Select permission to refer to it. In the Before trigger, assume that you have the update permission, 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, not the actual insert
The serial number.

By using the BEGIN ... End structure, you can define a trigger that runs multiple statements. In the Begin block, you can also use the storage
Other syntax, such as conditions and loops, that are agreed to in a subroutine. However, as with stored subroutines, it is defined that multiple statements are run
When triggering a program, assume that you use a MySQL program to enter a trigger, and you need to define the statement delimiter again so that you can trigger
The character ";" is used in the program definition. These points are demonstrated in the following demonstration examples. In the demo sample, 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 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 >
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.
It is also helpful to assume that you intend to invoke the same subroutine from within several trigger programs.
In the process of triggering the program, MySQL handles errors such as the following:
· Assume that the before trigger fails, and the action on the corresponding row is not run.
· The After trigger is run only if the before trigger (if any) and the row operation have run successfully.
· Assuming that an error occurs during the run of the before or after trigger, the entire language of the triggering program is called
The failure of the sentence.
· For a transactional table, assuming that the trigger fails (and the resulting failure of the entire statement), the statement runs
All changes are rolled back. For non-transactional tables, this type of rollback cannot be run, so even if the statement fails, the
Whatever changes are still in effect.

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;