Basic MySQL triggers (6) and mysql triggers

Basic MySQL triggers (6) and mysql triggers

1. Why trigger:

In a MySQL database, a database object table is a logical structure for storing and operating data, and a database object trigger is used to perform an operation triggered by some table events. In the database system, when a table event is executed, the trigger is activated to execute the operations contained by the executor. Trigger operations include creating, viewing, and deleting a trigger.

A trigger is one of the database objects. This object is very similar to functions in programming languages and must be declared and executed. However, the trigger is not executed by a program or manually, but is triggered and activated by an event for execution. For example, if the student table contains the Student name and total number of students, the total number of students must be changed when a student record is added. You can create a trigger for this instance. Each time you add a student record, the total number of students is calculated. This ensures that after each student record is added, the total number of students is the same as the number of student records. When the following DELETE, INSERT, and UPDATE statements are triggered in MySQL, the operation is automatically performed. Other SQL statements do not activate the trigger. The trigger is often used because the object can enhance data integrity constraints and business rules in database tables.

2. Create a trigger:
Based on the statement entries executed when the trigger is activated, the trigger can be divided into one statement execution trigger and multiple statement execution triggers"

2.1 create a trigger with the execution statement:

Syntax format:

create trigger trigger_name   before | after trigger_event    on table_name for each row trigger_stmt

// The trigger_name parameter indicates the name of the trigger to be created. The trigger name cannot be repeated. We recommend that you name trigger trigger_xxx or tri_xxx;
The before and after Parameters specify the trigger execution time. before: Refers to executing the trigger statement before the trigger event, and after: Refers to executing the trigger statement after the trigger event;
Trigger_event indicates the trigger event, that is, the trigger execution conditions, including the delete, insert, and update statements; the name of the table_name trigger event operation table;
For each row indicates that the trigger will be triggered when any operation on the record meets the trigger event; trigger_stmt indicates the statement executed after the trigger is activated.

Example:

Create trigger tri_diaryteim before insert on t_dept for each row insert into t_diary values (null,'t _ dept ', now (); // function description: insert the current time record to the t_diary table before inserting a record to the Department table.

2.2 create a trigger containing multiple statements:

The syntax format is as follows:

create trigger trigger_name  before|after trigger_event    on table_name for each row      begin      trigger_stmt      end

// There are two more keywords begin and end than the "trigger with only one execution statement" syntax. The two keywords are the content of multiple execution statements to be executed, the execution statements are separated by semicolons.
In mysql, the ";" symbol is generally used as the end symbol of the statement. However, when creating a trigger, the ";" symbol is used as the end symbol of the execution statement.
To solve this problem, you can use the keyword DELIMITER statement, for example, "DELIMITER $", which can be used to set the end symbol to "$ ".

Example:

DELIMITER $$create trigger tri_diarytime2 after insert  on t_dept for each row  begin   insert into t_diary values(null,'t_dept',now());  insert into t_diary values(null,'t_dept',now());  end  $$    DELIMITER;

3. view the trigger:

3.1 view the trigger using the show triggers statement:

Syntax:
Show triggers \ G

3.2 view the trigger by viewing the system table triggers:

The operation statement is;

Use information_schema; select * from triggers \ Gselect * from triggers where trigger_name = 'tri _ diarytime2' \ G // view the specified trigger

3. Delete trigger:

Syntax:
Drop trigger trigger_name;

The above is all the content of this article. I hope it will be helpful for your learning and support for helping customers.