one , Concept of trigger

​ A trigger is when a table changes (delete,insert,update) One that is automatically executed when MySQL sentence ( Or located begin and end A set of statements between statements ).

two , Create trigger

​ When creating a trigger , Need to give 4 Message :

Unique trigger name ;

Table associated with trigger ;

The activity that the trigger should respond to (delete,insert or update);

When does the trigger execute ( Before processing before Or after after).

give an example :

create trigger newproduct after insert on products

for each row select 'Product added';


use create trigger Statement to create a newproduct Trigger for , When right products After any row of the table is successfully inserted ,
Will be displayed on the console Product added information .


before or after?

usually , take before For data validation and purification , The purpose is to ensure that the data inserted into the table is really the required data .

three , Delete trigger

drop trigger newproduct;

four , Using triggers

1. insert trigger

​ stay insert In trigger code , You can access one called new Virtual table for , The contents of the table are inserted rows .

give an example :

create trigger neworder after insert on orders

for each row select new.order_num;

-- Insert a line to each orders Time in table , Will be removed from the virtual table new Returns the name of the row in order_num The value is displayed on the console .

2. delete trigger

​ stay delete In trigger code , You can access one called old Virtual table for , The contents in the table are deleted rows . But notice old All contents in the table are read-only , Cannot update .

give an example :

create trigger deleteorder before delete on orders

for each row


insert into archive_orders(order_num, order_date, cust_id)

values(old.order_num, old.order_date, old.cust_id);



utilize old Table to save the data in the row to be deleted to a file named archive_orders In the archive table of . Of course, the premise is that you need to create it first archive_orders surface .


【 notes 】 use before delete Advantages of trigger ( be relative to after For triggers ):

​ If for some reason , Order cannot be archived , Namely before trigger failed , that delete Deleting rows will not be performed .

Multi statement trigger : As you can see , trigger deleteorder use begin and end Statement mark trigger body . This is not really necessary in this case , But there's no harm . use begin
end The advantage of block is that the trigger can accommodate multiple SQL sentence ( stay begin end One by one in the block ).

3. update trigger

​ stay update In trigger code , You can visit old Update the data in the previous row in the virtual table , You can also access new Data in rows after update in virtual table .

give an example :

create trigger updatevendor before update on vendors

for each row set new.vend_state = upper(new.vend_state);

-- To guarantee vend_state Always capitalize , Ignore update Is the statement given in uppercase or lowercase