Mysql study notes 5 (triggers) and mysql Study Notes

Mysql study notes 5 (triggers) and mysql Study Notes
Trigger is one of mysql database objects. This object is very similar to functions in programming languages and must be declared and executed. However, trigger execution is not called by a program, it is not manually started, but triggered by an event, which is activated for execution.

Instance:
1. The student table contains the student Name field and the total number of students. Each time a student record is added, the total number of students must be changed at the same time.
2. In the customer information table, you have the customer name field, the customer name field, and the abbreviated customer address field. Each time you add a customer record, you must check whether the telephone number format is correct, whether the customer address abbreviation is correct.

In short, when the table needs to be changed, some processing will be performed automatically, such as checking the data validity and triggering other field updates. The mysql constraint does not contain the field size range constraint.
When mysql triggers the following statement, the set operation is automatically executed:

1. DELETE
2. INSERT
3. UPDATE

Other SQL statements do not activate the trigger.


Only one trigger can be created for a table at the same trigger time. Therefore, for experienced users, before creating a trigger, you need to check whether the trigger and trigger related events of this identifier exist in mysql software.


I. Create
1. Create a trigger for executing a statement

CREATE TRIGGER TRIGGERNAME

BEFORE | AFTER TRIGGER_EVENT

ON TABLE_NAME FOR EACH ROW

TRIGGER_STMT

BEFORE and AFTER
The parameter specifies the trigger execution time.
Trigger_EVENT
The parameter indicates the trigger event, that is, the trigger execution conditions, including delete, insert, and update;
For each row
The parameter indicates that a trigger event is triggered when any operation on the record is met;
Trigger_stmt
The parameter indicates the statement executed after the trigger is activated.
Example:

Create two tables t_dept and t_diary

Create table t_dept (

Detpno int,

Dname varchar (20 ),

Loc varchar (20)

);

Create table t_diary (

Diaryno int primary key,

Tablename varchar (20 ),

Diarytime datetime

);

Desc t_dept;
Desc t_diary;
Create trigger tri_diarytime

Before insert

On t_dept for each row

Insert into t_diaty values (null,'t _ dept ', now ());

Insert into t_dept values (1, 'cjgongdept ', 'shansi ');

Verification:

Select * from t_diary;

2. Create a trigger containing multiple execution statements
Create trigger triggername

Before | after trigger_event

On tablename for each row

Begin

Trigger_stmt

End

In mysql, the ";" symbol is generally used as the statement Terminator. However, when creating a trigger, the ";" symbol is used as the statement Terminator. To solve this problem, you can use the keyword delimiter statement, such as "delimiter $", to set the terminator 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 ());

On

$
Delimiter;
Insert into t_dept values (2, 'cjgongdept ', 'shansi ');
Verification:

Select * from t_diary;

Ii. View

Show triggers \ G

Iii. Delete

Drop trigger triggername
Show triggers \ G