MySQL Trigger Learning

Brief introduction

MySQL has the ability to support triggers starting with version 5.0.2. A trigger is a database object that is related to a table, fires when a condition is met, and executes a collection of statements defined in the trigger.

Create a Trigger

Grammar:

CREATE TRIGGER trigger_name trigger_time trigger_event

On tb_name for each ROW trigger_stmt

Based on the syntax to demonstrate how to create a trigger, first create 2 tables: Student and Student_demo

--CREATE TABLE StudentCREATE TABLE' student ' (' ID ')int(Ten) unsigned not NULLauto_increment, ' name 'varchar(255) not NULL, ' age 'tinyint(4) not NULL, ' Gender ' enum ('female','male') not NULL, ' address 'varchar(255) not NULL,  PRIMARY KEY(' id ')) ENGINE=InnoDB auto_increment=3 DEFAULTCHARSET=UTF8;--CREATE TABLE Student_demoCREATE TABLE' Student_demo ' (' ID ')int( One) not NULL, ' name 'varchar(255) not NULL, ' address 'varchar(255) not NULL,  PRIMARY KEY(' id ')) ENGINE=InnoDBDEFAULTCHARSET=UTF8;

Now, assuming that a new record is added to table student, Student_demo also needs a new record to demonstrate the creation of the trigger based on this requirement.

-- CREATE TRIGGER ins_student CREATE TRIGGER INSERT  on  for Each ROW BEGIN    INSERT  into Student_demo (ID, name, address)     VALUES      (        new.id,        new.name,         new.address     ); END;

The after insert trigger is now created for the student table, and when you insert data into the student table, Student_demo also inserts the corresponding record

As you can see, when you add a student message named "Han Meimei" to the student table, Student_demo also adds its corresponding information

For INSERT INTO ... On DUPLICATE KEY UPDATE statement, the order in which triggers are triggered differs. Create before insert, after insert, before update, and after update for the student table to insert a record of the observation results.

1 --Create a table tri_test2 CREATE TABLETri_test (IDINTAuto_increment, noteVARCHAR( -),PRIMARY KEY(ID))3 4 --create before Insert trigger5 CREATE TRIGGERINS_STUDENT_BEF beforeINSERT  onStudent forEach ROW6 BEGIN7     INSERT  intotri_test (note)8 VALUES9('before insert');Ten END; One  A --create after insert trigger - CREATE TRIGGERIns_student_aft afterINSERT  onStudent forEach ROW - BEGIN the     INSERT  intotri_test (note) - VALUES -('After insert'); - END; +  - --create before UPDATE trigger + CREATE TRIGGERUPD_STUDENT_BEF beforeUPDATE  onStudent forEach ROW A BEGIN at     INSERT  intotri_test (note) - VALUES -('before update'); - END; -  - --To create an after update trigger in CREATE TRIGGERUpd_student_aft afterUPDATE  onStudent forEach ROW - BEGIN to     INSERT  intotri_test (note) + VALUES -('After update'); the END;

View Code

Now there is a record in the student table

Now for the id=3 record, insert the data

INSERT  intoStudentVALUES    (        3,        'Li Lei',         -,        'male',        'Shijiazhuang, Hebei'    )  onDUPLICATEKEY UPDATENAME= 'Update Record'

The data in tables student and tri_test are:

For insert operations that have duplicate records and require an update, the order of the triggers is before insert, before update, after update

If you are inserting new, non-repeating data

INSERT  intoStudentVALUES    (        6,        'David',         -,        'male',        'Baoding, Hebei'    )  onDUPLICATEKEY UPDATENAME= 'Update Record'

The table data is

The order of the triggers is before insert, after insert

Delete Trigger

One trigger can be deleted at a time, and if not specified, the current database is assumed to be default. The syntax is as follows:

DROP TRIGGER [schema_name. ]trigger_name

For example, to delete a trigger ins_student, you can perform

View triggers show TRIGGERS

You can view the status, syntax, and so on of a trigger by using the show triggers command, which queries information about all triggers

Querying system tables

You can query the information for a specified trigger by querying the Information_schema.triggers table in the system table

Querying information for the specified trigger "Ins_student"

MySQL Trigger Learning