(a) trigger in MySQL

Overview

A trigger, as the name implies, executes a program when something (event) occurs. Triggers have four main elements:,,, 监视地点（table_name） 监视事件（insert/update/delete） 触发时间（before/after）触发执行程序（insert/update/delete）

Grammar

 #创建触发器  Create  trigger  Triggername Trigger Time monitoring event on  Table name for  each  row  begin  sql  statement (trigger event) end  ———————————————————————————————————— #删除触发器 drop  trigger  [database_name.] trigger_name;  ———————————————————————————————————— #查看触发器 show  triggers;   

• Triggers can only be created on a permanent table (permanent table).
• Only one trigger can be defined for the same table, the same trigger time, and the same monitoring event.
• Use old（更新前的行数据） new（更新后的行数据） to refer to the contents of the record that changed in the trigger.
• Trigger only supports row-level triggering and does not support statement-level triggering, so it can be inefficient when working with large datasets.
• The triggering executor cannot invoke the stored procedure/function that returns the data to the client, but allows the stored program to return the data to the triggering executor through the parameters (that is, the Out/inout parameter).
• A trigger cannot perform a transactional operation.
• Triggers do not guarantee atomicity, such as in MyISAM, when an UPDATE trigger triggers an update to another table after updating a table, the update of the first table is not rolled back if the trigger fails. The triggers and actions in InnoDB are done in a single transaction and are atomic operations.
• After and before difference: After is the first to complete the data additions and deletions, and then trigger, trigger the statement later than the monitoring and deletion of the operation, can not affect the previous additions and deletions to change the action, that is, first insert the order record, and then update the quantity of the goods; before is the first to complete the trigger, then increase the deletion, The triggered statements are preceded by additions and deletions to the monitor, and we have the opportunity to judge and modify the actions that will occur.

function

• Security: You can make a user have some right to manipulate the database based on the value of the database. For example, you can limit user actions based on time, such as not allowing you to modify database data after work and holidays.
• Audit: You can track user operations on the database. For example, the statement that audits the user operations database.
• Implement complex data integrity rules: Implement non-standard data integrity checks and constraints. Triggers can produce more complex restrictions than rules. Unlike rules, triggers can reference columns or database objects. For example, a trigger can rewind any futures that attempt to eat more than their own margin.
• Implement complex non-standard database-related integrity rules: Triggers can be used to update the related tables in the database. For example, cascade Modify or delete rows in other tables that match them when modified or deleted.
• Synchronously replicates data in a table in real time.
• The data values are calculated automatically, and if the values of the data meet certain requirements, specific processing is performed. For example, if the company's account has less than $50,000 in money, it immediately sends warning data to the financial officer.

Example

Create two tables goods (commodity table) and order_t (Order table)

Example One
I want to order the next 3 Item 1, need two steps, insert a data into the order table, update the NUM column of item 1 in the commodity table.

#没有使用触发器insert order_t(gid,much) values(‘1‘,3);update goods set num=num-3 where id=1;

Now I'm going to 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 can complete the above tasks with just one single execution.

#使用触发器insert order_t(gid,much) values(‘1‘,3);

Will find that the number of items 1 has changed to 7, indicating that when we insert an order, the trigger automatically helps us with the update operation.

Example Two
Now there is a problem, because we trigger that NUM and ID are written dead, so no matter which product we buy, the final update is the number of goods 1. For example: We insert a record into the order form: INSERT into O (Gid,much) values (2,3), after execution will find that the number of goods 1 has changed 4, and the number of goods 2 has not changed, which is obviously not the result we want. We need to change the trigger we created earlier.
For insert, the newly inserted row is used new to represent the value of each column in the row represented by the new. Column name.
Change Trigger

delimiter $$create trigger test_tr1after insert on order_tfor each rowbeginupdate goods set num=num-new.much where id=new.gid;end $$delimiter ;

To test again, insert an order record:

insert into o(gid,much) values(2,3)

After the execution of the discovery item 2 the quantity becomes 7, now is right.

Now there are two situations:

example Three
When the user revokes an order, we delete an order directly, do we need to add the corresponding quantity of goods back?
For delete: Originally a row, and then deleted, want to refer to the deleted line, used old to represent, old. Column names can refer to the values of deleted rows.

delimiter $$create trigger test_tri2after delete on order_tfor each rowbeginupdate goods set num = num + old.much where id = old.gid;end $$delimiter ;

Re-execute

delete from order_t where id = 2;

Will find that the number of goods 2 has become 10.

example Four
When the user modifies the quantity of an order, how do we write the trigger change?
For update: The modified line, the data before the modification, the old to indicate the value of the column name refers to the previous row, the modified data, with new to represent, new. The column name refers to the value in the row after it has been 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 ;

We then modify the inserted order record:

update order_t set much = 5 where id = 1;

We turned to buy 5 goods 1, this time again check the commodity table will find that the number of goods 1 is only 5, indicating that our triggers play a role.

Reference

http://blog.csdn.net/tonyxf121/article/details/8255782
Http://www.cnblogs.com/zzwlovegfj/archive/2012/07/04/2576989.html

(a) trigger in MySQL