Basic operations for MySQL triggers (vi) _mysql

1. Why use triggers:

In a MySQL database, a Database object table is a logical structure for storing and manipulating data, and database object triggers are used to implement an action that is triggered by some table events. In a database system, when a table event is executed, the trigger is activated, thus the actions the executor contains. The actions of a trigger include creating, viewing, and deleting.

Triggers are one of the database objects that are very similar to functions in a programming language and need to be declared, executed, and so on. However, the execution of the trigger is not invoked by the program or initiated by the hand, but by the event to trigger, activate, and execute. For example, in a student's table there are student names, total student numbers, and each time a student record is added, the total number of students must be changed at the same time. For this instance, you can create a trigger that calculates the total number of students each time a student record is added, so that the total number of students and the number of student records will be consistent each time a student record is added. In MySQL, when the following delete, INSERT, UPDATE statement is triggered, the set action is performed automatically, and the other SQL statements do not activate the trigger. Triggers are used frequently because the object strengthens the integrity constraints and business rules of the data in the database tables.

2. Create triggers:
you can divide triggers into "one trigger for executing statements" and "multiple execution statements" by following the statement entries that are executed when the trigger is activated

2.1 Create a trigger that has a statement to execute:

The grammatical form is:

Create Trigger Trigger_name 
  before | After trigger_event on table_name for each
    row trigger_stmt

The Trigger_name parameter represents the name of the trigger you are creating, and the name of the trigger cannot be duplicated. The suggested triggers are named trigger_xxx or tri_xxx;
The Before and after parameters specify when the trigger executes, before: The trigger statement is executed before the trigger event, after: The trigger statement is executed after the trigger event;
Trigger_event represents the triggering event, that is, the trigger execution condition, the package delete, INSERT, UPDATE statement, and the name of the TABLE_NAME trigger event action sheet;
A For each row indicates that the action on any record that satisfies the triggering event triggers the trigger; TRIGGER_STMT represents the statement that was executed after the trigger was activated.

Example:

Create trigger Tri_diaryteim 
  before insert on t_dept for each row
    inserts into the t_diary values (null, ' t_dept ', now ()) ;
Feature Description: Inserts the current time record into the table t_diary before inserting a record into the department table.

2.2 Create a trigger that contains more than one execution statement:

The grammatical forms are as follows:

Create Trigger Trigger_name
  Before|after trigger_event on table_name for each
    row
      begin
      trigger_stmt< C10/>end

Two more keywords begin and end than the "trigger with only one execution statement" syntax, where the contents of multiple execution statements to be executed are separated by semicolons.
In MySQL, the ";" symbol is generally used as the closing symbol for a statement, but when a trigger is created, the ";" symbol is used as the closing symbol for executing the statement.
To resolve this problem, you can use the keyword DELIMITER statement, such as "delimiter$$", to set the ending symbol to "$$".

Example:

DELIMITER $$
CREATE trigger tri_diarytime2 after insert in 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 triggers:

3.1 View triggers by show triggers statement:

The syntax is:
Show triggers \g

3.2 Implement view triggers by viewing system table triggers:

The action statement is;

Use Information_schema;
SELECT * FROM triggers \g
select * from triggers where trigger_name= ' tri_diarytime2 ' \g//view specified triggers

3. Delete triggers:

The syntax is:
drop trigger trigger_name;

The above is the entire content of this article, I hope to help you learn, but also hope that we support the cloud habitat community.