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
begin
insert into archive_orders(order_num, order_date, cust_id)
values(old.order_num, old.order_date, old.cust_id);
end;
/*
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
Technology