• notice
  • Congratulations on the launch of the Sought Tech site

Basic operation of mysql trigger (6)

1.Why use triggers: 

    In a MySQL database, database object tables are a logical structure for storing and manipulating data, while database object triggers are used to implement certain operations triggered by some table events.In a database system, when a table event is executed, a trigger is activated to execute the operation contained in the actuator.Trigger operations include create, view, and delete. 

    Triggers are one of the database objects, which are very similar to functions in programming languages ​​and require declaration, execution and so on.However, the execution of the trigger is not called by the program, nor is it started manually, but triggered and activated by the event to realize the execution.For example: there are fields for student name and total number of students in the student table.Whenever a student record is added, the total number of students must be changed at the same time.For this example, you can create a trigger.Every time a student record is added, the operation of calculating the total number of students is performed once, so that each time a 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 set operation will be automatically executed, and other SQL statements will not activate the trigger.The reason why triggers are often used is because the object can strengthen the integrity constraints and business rules of the data in the database table.

2.Create a trigger: 
    According to the statement entry 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 that executes a statement:

The syntax form is:

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

//trigger_name The parameter indicates the name of the trigger to be created.The name of the trigger cannot be repeated.It is recommended to name the trigger as trigger_xxx or tri_xxx;
The before and after parameters specify the execution time of the trigger, before: refers to the execution of the trigger statement before the trigger event, after: refers to the execution of the trigger statement after the trigger event;
trigger_event represents the trigger event, that is, the trigger execution condition, including delete, insert, and update statements; table_name triggers the name of the event operation table;
for each row means that any operation on a record will trigger the trigger when the trigger event is met; trigger_stmt means the statement that is executed after the trigger is activated.


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 into the table t_diary before inserting a record into the department table.

2.2 Create a trigger containing multiple execution statements:

The syntax is as follows:

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

//There are two more keywords begin and end than the "trigger with only one execution statement" syntax.Between these two keywords is the content of multiple execution statements to be executed.Use between execution statements Separate by semicolons.
In mysql, in general, the ";" symbol is used as the ending symbol of a statement, but when creating a trigger, Need to use the ";" symbol as the end symbol of the executed statement.
In order to solve this problem, you can use the keyword DELIMITER statement, for example: "DELIMITER$$", which can be used to set the end symbol to "$$".


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

3.View trigger: 

3.1 View the trigger through the SHOW TRIGGERS statement:

The syntax is:
show triggers \G

3.2 View triggers by viewing the system table triggers:

The operation statement is;

use information_schema;
select * from triggers \G
select * from triggers where trigger_name='tri_diarytime2' \G//View the specified trigger

3.Delete trigger:

The syntax is:
drop trigger trigger_name;


Technical otaku

Sought technology together

Related Topic


Leave a Reply