(15) mysql triggers and 15mysql triggers
A trigger, as its name implies, executes a program when something (event) occurs. Triggers have four main elements:
Monitored location (table_name),
Monitoring event (insert/update/delete),
Trigger time (before/after),
Trigger execution Program (insert/update/delete)
# Create trigger triggerName trigger time monitoring event on table name for each rowbeginsql Statement (trigger event) end ------------------------------ # Delete trigger drop trigger [database_name.] trigger_name; ------------------------------------ # view the trigger show triggers;
Old (row data before update),
New (updated row data)To reference the changes in the trigger.
Create two tables goods (item table) and order_t (Order table)
I want to place an order for three items 1. I need to take two steps to insert a piece of data to the order table and update the num column of Item 1 in the item table.
# The trigger insert order_t (gid, much) values ('1', 3); update goods set num = num-3 where id = 1;
Now, create a trigger.
delimiter $$create trigger test_tr1after insert on order_tfor each rowbeginupdate goods set num=num-3 where id=1;end $$delimiter ;
After the creation, I only need to execute one task to complete the above tasks.
# Use the trigger insert order_t (gid, much) values ('1', 3 );
The number of Item 1 Changes to 7, which means that when we insert an order, the trigger will automatically update the order.
Now there is a problem, because the num and id in the trigger are all written to death, so no matter which product we buy, the number of Item 1 will be updated in the end. For example, we insert another record into the Order table: insert into o (gid, much) values (2, 3). After execution, we will find that the number of Item 1 has changed to 4, the number of product 2 has not changed. This is obviously not the result we want. We need to modify the previously created trigger.
For insert, the newly inserted row uses
newThe value of each column in the row is represented by the new. Column name.
delimiter $$create trigger test_tr1after insert on order_tfor each rowbeginupdate goods set num=num-new.much where id=new.gid;end $$delimiter ;
Next, let's test and insert an order record:
insert into o(gid,much) values(2,3)
After the execution, the number of product 2 changes to 7, and now it is correct.
There are two cases:
When a user cancels an order, we directly delete an order. Do we need to add the corresponding product quantity back?
For delete: a row is deleted. to reference the row to be deleted, use
oldThe old. Column name can reference the value of the row to be deleted.
delimiter $$create trigger test_tri2after delete on order_tfor each rowbeginupdate goods set num = num + old.much where id = old.gid;end $$delimiter ;
delete from order_t where id = 2;
The number of product 2 is changed to 10 again.
How can we modify the trigger when the user modifies the number of orders?
For update: The data of the modified row is represented by old. the column name references the value in the row before the modification. The modified data is represented by "new. column name reference the value in the row after the column name is modified.
delimiter $$create trigger test_trg3after update on order_tfor each rowbeginupdate goods set num = num+old.much-new.much where id = old/new.gid;end $$delimiter ;
Modify the inserted order record:
update order_t set much = 5 where id = 1;
When we purchase 5 items 1, then we can query the item table and find that there are only 5 items left in the number of item 1, which indicates that our trigger has played a role.Reference