How MySQL triggers are used is explained in detail

MySQL trigger syntax specific explanation:

trigger trigger is a special kind of stored procedure . He triggers a run when inserting (inset), deleting (delete), or altering (update) data in a particular table , which is more granular and more complex than the data itself .

A trigger is not called by a program, but is triggered by an event.

Proactively enforce their business rules when there is data churn, often used to enforce data integrity constraints and business rules. Triggers are able to query other tables and include copied SQL statements. Triggers can also be used to enforce referential integrity. Triggers can enforce more complex constraints than constraints defined with a CHECK constraint.

(i). CREATE Trigger Syntax
CREATE TRIGGER trigger_nametrigger_time trigger_event on tbl_name for each ROW trigger_stmt;
A trigger is a named database object that is related to a table. The object 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. You cannot associate a trigger with a temporary table or view.
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:
(1). Insert: Activates the trigger when inserting a new row into the table. For example, through INSERT, LOAD data, and replace
Statement.
(2). Update: Activates a trigger when a row is changed, for example, through an UPDATE statement.

(3). Delete: Activates the trigger when a row is deleted from the table. Like what. Through the delete and replace statements.
Note that trigger_event is not very similar to SQL statements that activate triggers as a table operation. This is very important.

For example, 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: The before INSERT trigger is activated for each row, followed by the after insert trigger, or before update and after update triggers, depending on whether there are repeated keys on the row.

For a given table with the same trigger action time and event. There can be no two trigger programs.

For example, there cannot be two before update triggers for a table. However, there can be 1 before update triggers and one before insert trigger, or 1 beforeupdate triggers 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 Compound statement structure. This way, you can use the same statement that you agreed to in the stored subroutine

(b). DROP Trigger Syntax
DROP Trigger[schema_name.] Trigger_name discards the triggering program.

The scheme name (schema_name) is optional. Assuming the schema is omitted, the trigger is discarded from the current scenario.

Gaze: When upgrading from MySQL version number prior to MySQL 5.0.10 to 5.0.10 or later (including all MySQL5.1 version numbers), you must discard all triggers before upgrading. And then create them again, otherwise. Drop trigger does not work after the upgrade. The DROP trigger statement requires Super permissions.

(iii). Using the Trigger program
In this section. Describes how to use triggers in MySQL 5.1, and describes the limitations of using trigger programs.
A trigger is a named database object related to a table that is activated when a specific event occurs on the table. In the use of some triggers, you can check the values that are inserted into the table. Or the values involved in the update are evaluated.

Triggers are related to tables, and triggers are activated when an INSERT, delete, or UPDATE statement is run against a table. The ability to set the trigger to be activated before or after the statement is run.

For example, the trigger can be activated before each row is deleted from the table, or after each row has been updated.

To create a trigger or discard a trigger, you can use the CREATE trigger or drop The trigger statement. The trigger cannot invoke the stored program that returns the data to the client, nor can it use the dynamic SQL that uses the Call statement (the consent store program passes the data back to the trigger).
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, only old.col_name can be used and no new rows are available. 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 refer to the 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, it is assumed that you have SELECT permission to reference it. In the Before trigger, assume that you have the update permission, you can change its value using SET New.col_name = value.

This means that you can use the trigger to change the value that will 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. is not the serial number that will be actively generated when a new record is actually inserted.

By using the BEGIN ... End structure. You can define a trigger that runs multiple statements. In the Begin block, you can also use other grammars that are agreed to in the stored subroutine, such as conditions and loops. However, as with stored subroutines, you define a trigger that runs multiple statements. Suppose you use a MySQL program to enter a trigger program. You need to define the statement delimiter again so that you can use the character ";" in the trigger definition. These points are demonstrated in the following demonstration examples.

In the demo sample, 1 update triggers are defined. Used to check for new values to be used when updating each row, 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 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. Suppose you intend to invoke the same subroutine from within a few triggers. This method is also very helpful.

In the process of triggering the program, MySQL handles errors such as the following:
(1) Assuming that the before trigger fails, the action on the corresponding row is not run.

(2) The After trigger is run only if the before trigger (if any) and the row operation have run successfully.

(3) Assuming that an error occurs during the run of the before or after trigger, the failure of the entire statement that invokes the triggering program is caused.
(4) for transactional tables. Assuming that the trigger fails (and the resulting failure of the entire statement), all changes that the statement runs are rolled back.

For non-transactional tables, this type of rollback cannot be run. Thus. Even if the statement fails. Any changes made before the failure 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;

How MySQL triggers are used is explained in detail