Introduction to the use and usage of MySQL triggers

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

1. Safety. The user can have a certain right to manipulate the database based on the value of the database;
User actions can be limited based on time, such as not allowing changes to database data after work and holidays;
You can restrict the user's actions based on data in the database, such as not allowing the price of a stock to increase by more than 10%.
2. Audit. Can track the user to the database operation;
The statement of Audit user operation database;
Writes the user's update to the database to the audit table.
3. Implementation of complex data integrity rules;
Implementation of non-standard data integrity checks and constraints. Triggers can produce more complex restrictions than rules. Unlike rules, triggers can refer to columns or database objects. For example, triggers can be rolled back any futures that attempt to eat more than their margin;
Provides a variable default value.
4. Implementation of complex non-standard database-related integrity rules. Triggers can have a serial update of related tables in the database. For example, a delete trigger on a auths table Author_code column can cause a corresponding deletion of the rows that match in the other table.
Cascade Modify or delete rows that match in other tables when modifying or deleting them;
Setting the matching row in the other table to a null value when modifying or deleting;
Set the matching row in the other table to the default value when modifying or deleting;
Triggers can reject or rewind changes that break the associated integrity and cancel the transaction that is attempting to update the data. This trigger works when you insert a foreign key that does not match its primary health. For example, you can generate an INSERT trigger on the Books.author_code column, and the insertion is rolled back if the new value does not match a value in the Auths.author_code column.
5. Synchronize the data in the table in real time.
6. Automatic calculation of data values, if the value of the data to meet certain requirements, then the specific processing. For example, if the company's account number is less than 50,000 yuan, immediately send the warning data to the financial staff.
Trigger syntax Detailed:

1. 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 that is related to a table and is activated when a specific event occurs on the table.
Triggers are related to tables named Tbl_name. Tbl_name must refer to a permanent table. Triggers cannot be associated with temporary tables or views.

Trigger_time is the action time of the trigger. 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 insert,update and deletes, representing the modification and deletion of the database respectively. It is important to note that trigger_event is not very similar to the SQL statement that activates triggers in a table operation. For example, a before trigger for inserts can be activated not only by an 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 triggers are activated for each row, followed by an insert trigger, or before update and after update triggers, depending on whether there are duplicate keys on the row.
There can be no two triggers for a given table with the same trigger action time and events. For example, you cannot have two before update triggers for a table. However, there can be 1 before update triggers and one before insert trigger, or 1 before update triggers and an after UPDATE trigger.
TRIGGER_STMT is the statement that executes when a trigger is activated. If you are going to execute multiple statements, you can use the BEGIN ... End Compound statement structure. This allows you to use the same statements that are allowed in the storage subroutine.

2. DROP Trigger Syntax

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

3. Using triggers

A trigger is a named database object that is related to a table and is activated when a specific event occurs on the table. In the use of some triggers, you can use to check values that are inserted into a table, or to calculate the values involved in an update.
Triggers are related to tables, and triggers are activated when an INSERT, delete, or UPDATE statement is executed on a table. Triggers can be set to activate before or after the statement is executed. For example, triggers can be activated before each row is deleted from a table, or after each row is updated.

For example:

CREATE TABLE Account (acct_num INT, amount DECIMAL (10,2));
CREATE TRIGGER ins_sum before INSERT on account for each ROW SET @sum = @sum + new.amount;
SET @sum = 0;
INSERT into account values (5,12.5);
SELECT @num;

With the old and new keywords, you can access columns in rows affected by triggers (old and new are case-insensitive).

In an INSERT trigger, only new.col_name can be used, and no old rows are available. In the delete trigger, you can only use Old.col_name and no new rows.
In the update trigger, you can use Old.col_name to refer to a row before the update, or you can use New.col_name to refer to the columns in the updated row. The column named with the old is read-only. You can reference it, but you can't change it. For a column named with new, you can refer to it if you have a SELECT permission. In before triggers, if you have update permissions, you can use SET New.col_name = value to change its value. This means that you can use triggers to change the value that will be inserted into the new row, or to update the row.
In the Before trigger, the new value of the Auto_increment column is 0, not the serial number that is automatically generated when the new record is actually inserted.
By using the BEGIN ... End structure, which enables you to define triggers that execute more than one statement. In the Begin block, you can also use other syntax, such as conditions and loops, that are allowed in the storage subroutine. However, as with stored subroutines, when you define a trigger that executes multiple statements, if you use a MySQL program to enter a trigger, you need to redefine the statement separator so that you can use the character ";" in the trigger definition. In the following example, these points are illustrated. In this example, 1 update triggers are defined to check the new value that will be used for each row of the update, and to change the value so that it is in the range of 0~100. It must be a before trigger, because it needs to be checked before the value is used for the update row:
CREATE TRIGGER Upd_check before UPDATE on account for each ROW BEGIN IF new.amount < 0 THEN SET new.amount = 0; ELSEIF new.amount > THEN SET new.amount = 100; End IF; End;

The simpler approach is to define the stored procedure separately and then invoke the stored procedure from the trigger using a simple call statement. This method is also helpful if you intend to call the same subroutine from within several triggers.
During the execution of a trigger, MySQL handles the error in the following ways:

If the before trigger fails, the action on the corresponding line is not performed;
After triggers are executed only if the before trigger (if any) and row operations have been successfully executed;
If an error occurs during the execution of a before or after trigger, it will cause the entire statement that invoked the trigger to fail;
For a transactional table, if the trigger fails (and the entire statement that is caused by the failure), all the changes made by the statement are rolled back, and no such rollback can be performed for non-transactional tables, so any changes that have taken place before the failure are valid even if the statement fails.

Here are some of the limitations of using triggers:
Triggers cannot invoke stored procedures that return data to the client or use dynamic SQL with the call statement (allowing stored procedures 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.

Creating triggers

The syntax is as follows:

CREATE TRIGGER trigger_name trigger_time trigger_event
On tbl_name for each ROW trigger_stmt
A trigger is a named database object that is related to a table and is activated when a specific event occurs on the table.

The trigger is associated with a table named Tbl_name. Tbl_name must refer to a permanent table. You cannot associate a trigger with a temporary table table or view.

Trigger_time is the action time that triggers the 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 a trigger when inserting a new row into a table, for example, through INSERT, LOAD data, and replace statements.

· Update: Activates a trigger when a row is changed, for example, through an UPDATE statement.

· Delete: Activates a trigger when a row is deleted from a table, for example, through the Delete and replace statements.

Note that it is important that the trigger_event is not very similar to the SQL statement that activates the triggering program as a table operation.

For example, the before trigger for inserts can be activated not only by an 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 triggers are activated for each row, followed by an insert trigger, or before update and after update triggers, depending on whether there are duplicate keys on the row.

There can be no 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.

However, there can be 1 before update triggers and a before insert trigger, or 1 before update triggers and an after UPDATE trigger.

TRIGGER_STMT is the statement that executes when the trigger is activated.

If you are going to execute multiple statements, you can use the BEGIN ... End Compound statement structure. This allows you to use the same statements that are allowed in the storage subroutine.

Create a trigger for a single execution statement

CREATE TABLE Account (acct_num INT, amount DECIMAL (10,2));
CREATE TRIGGER ins_sum before INSERT on account
For each ROW SET @SUM = @SUM +new.amount;
First, create an Account table with two fields in it: the Acct_num field (defined as type int)

Amount field (defined as a floating-point type), and then create a trigger named Ins_sum that triggers the condition that the data is inserted into the data table account before the

To sum the newly inserted amount field values

DECLARE @num INT
SET @num =0
INSERT into account VALUES (1,1.00), (2,2.00)
SELECT @num
First, you create an account table that calculates the sum of the amount values of all the newly inserted statement tables before inserting data into the table.

The name of the trigger is Ins_num, which is triggered before the data is inserted into the table.

Create a trigger with multiple execution statements, the syntax is as follows:

DELIMITER |

CREATE TRIGGER testref before INSERT on test1
For each ROW BEGIN
INSERT into test2 SET a2 = new.a1;
DELETE from test3 WHERE a3 = new.a1;
UPDATE test4 SET b4 = b4 + 1 WHERE a4 = new.a1;
End
|

viewing triggers

Viewing triggers is the definition, status, and syntax information of triggers that already exist in the database.

You can use show triggers and view trigger information in the Triggers table

Show triggers

trigger  event   table    statement                  timing  created  sql_mode  definer          character_set_client  collation_connection  Database Collation
------- ------ ------- ------------------------ ------ ------- ---- ---- -------------- -------------------- -------------------- ------------------
Ins_ sum  insert  account  Set @sum = @sum +new.amount  before  (NULL)               root@localhost  utf8                   utf8_general_ci        utf8_general_ci  

Event indicates the events that activate the trigger, where the triggering event is the insert Operation insert,table the object table that activates the trigger, here is the account table

Timing represents the time of the trigger, before the insert (before), Statement represents the action performed by the trigger, and other information such as SQL mode, the definition account for the trigger, and the character set

Viewing trigger information in the Triggers table

INFORMATION_SCHEMA database triggers table, you can view trigger information through a query

SELECT * from ' information_schema ' triggers ' WHERE ' trigger_name ' = ' ins_sum '

Trigger_catalog trigger_schema trigger_name event_manipulation event_object_catalog EVENT_OBJECT_SCHEMA _table action_order action_condition action_statement action_orientation action_timing         TABLE action_reference_new_table action_reference_old_row action_reference_new_row CREATED sql_mode DEFINER Character_set_client collation_connection database_collation
---------------  --------------  ------------  ------------------  --------------------  -------------------  ------------ ------  ------------  ----------------  ------------------------  ------------------  -------------  ---------------------  -----  --------------------------  ------------------------  ------------------------  -------  --------  -------------- --------------------  --------------------  ------------------
def              school           ins_sum       insert               def                    school                account                         0  (NULL)              Set @sum = @sum +new.amount  row                  before          (NULL)                        (NULL)                        old                        new                        ( NULL)              root@localhost  utf8                   Utf8_general_ ci       utf8_general_ci  

Trigger_schema represents the database where the trigger is located

Trigger_name represents the name of the trigger

Event_object_table indicates on which table the trigger

Action_statement a specific action that is performed when a trigger is triggered

Action_orientation is row, which means that each record is triggered

Action_timing indicates that the trigger time is before

deleting triggers

Use the Drop TRIGGER statement to remove a trigger that has already been defined in MySQL, and to delete the basic syntax of the trigger

DROP TRIGGER [schema_name.] Trigger_name
where (schema_name) is optional

If schema (scenario) is omitted, the trigger is discarded from the current scenario.

Delete Ins_sum triggers

DROP TRIGGER ' school '. ' Ins_sum '

Trigger Ins_sum Delete Succeeded

Summarize

For the same table, the same event can only create one trigger, such as creating a before insert trigger on the table account

So if you create a before insert trigger on the table account again, MySQL will complain, and at this point, you can only

Create triggers for after insert or before update type

If there is a wrong place, welcome to shoot Bricks O (∩_∩) o

The copyright of this article is owned by the author and may not be reproduced without the consent of the author.

CREATE TABLE account (acct_num int, amount DECIMAL (10,2), num int);

CREATE TRIGGER Ins_sum before INSERT
On account for each ROW
UPDATE
' Employee '
SET
' Age ' = new.acct_num + 1
WHERE ' ID ' = new.acct_num;

INSERT into account (Acct_num,amount) VALUES (1,2.00);

SELECT * from ' employee '

The following points need to be noted:

1.MySQL triggers operate on rows, so it can be inefficient when working with large datasets.
2. Triggers do not guarantee atomicity, for example in MyISAM, when an UPDATE trigger updates a table and triggers an update to another table, if the trigger fails, the first table update is not rolled back. Triggers and operations in InnoDB are done in one transaction and are atomic operations.