(15) mysql triggers and 15mysql triggers

(15) mysql triggers and 15mysql triggers
Overview

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)

Syntax

# 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;

• A trigger can only be created on a permanent table.
• Only one trigger can be defined for the same table, the same trigger time, and the same monitoring event.
• UseOld (row data before update),New (updated row data)To reference the changes in the trigger.
• The trigger only supports row-level triggering and does not support statement-level triggering. Therefore, it may be inefficient to process large datasets ..
• The trigger execution program cannot call the stored procedure/function that returns data to the client, but allows the stored procedure to return data to the trigger execution program through the parameter (that is, the out/inout parameter.
• The trigger cannot perform transaction operations.
• Trigger cannot guarantee atomicity. For example, in MYISAM, when an update trigger updates a table, it triggers updates to another table. If the trigger fails, the update of the first table is not rolled back. The triggers and operations in InnoDB are completed in a transaction and are atomic operations.
• The difference between "after" and "before" is that "after" is used to add, delete, modify, and then trigger data. The triggered statement is later than the added, deleted, and modified operations monitored, and cannot affect the previous added, deleted, and modified operations; that is to say, the order record is inserted first, and then the number of items is updated. before is triggered first, and then the deletion and modification are added. The triggered statement is prior to the addition, deletion, and modification of monitoring, so we have the opportunity to judge, modify the forthcoming operation

Function

• Security: users can have the right to operate databases based on database values. For example, you can restrict user operations based on time. For example, you cannot modify Database Data after work or holidays.
• Audit: You can track your database operations. For example, statements used to audit user operations on databases.
• Implement complex data integrity rules: implement non-standard data integrity checks and constraints. Triggers can generate more complex limits than rules. Unlike rules, triggers can reference columns or database objects. For example, a trigger can be used to roll back any attempt to eat futures that exceed its deposit.
• Implement complex non-standard database-related integrity rules: triggers can be used to update related tables in the database. For example, when you modify or delete a table, you can cascade to modify or delete the matched rows in other tables.
• Synchronize the data in the table in real time.
• Automatically calculates the data value. If the value meets certain requirements, specific processing is performed. For example, if the company account has funds less than 50 thousand yuan, the company immediately sends a warning data to the finance staff.

Example

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

Example 1
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.

Example 2
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 usesnewThe value of each column in the row is 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 ;

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:

Example 3
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, useoldThe 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 ;

Run again

delete from order_t where id = 2;

The number of product 2 is changed to 10 again.

Example 4
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

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