trigger (Trigger)Role: Monitor a situation and trigger an action. Trigger definition: In the development of database application software, we sometimes encounter some data changes in the table, hoping to cause the need to change other related data, and use the trigger to satisfy the demand. It can automatically complete certain queries when some specific data in the table changes. Using triggers can not only simplify the program, but also increase the flexibility of the program. A trigger is a special kind of transaction: You can monitor a data operation (Insert/update/delete) and trigger a related action (insert/update/delete).
Trigger Application Scenarios: 1. When you add or delete records to a table, you need to synchronize them in the related tables. For example, when an order is generated, the inventory of the goods purchased by the order should be reduced accordingly. 2. When the value of a column of data on a table is associated with data in another table. For example, when a customer makes a payment, a design trigger can be used to determine whether the customer's accumulated arrears exceed the maximum amount when the order is generated. 3. When a table needs to be tracked. such as: When a new order is generated, you need to promptly notify the relevant personnel for processing, you can design and add a trigger on the order form to implement.
four elements of a trigger: Monitor location (table), monitor event (insert/update/delete), Trigger Time (After/before), trigger event (Insert/update/delete)
To create a trigger syntax:Create TRIGGER Trigger name After/before (trigger time) Insert/update/delete (trigger event) on table name (monitor address)
For each row--this sentence is written dead in MySQL, only row triggers, and table triggers in OracleBeginsql1...sqlnend;
examples of triggers: Requirements: Commodity table: Goods order form: Ord when the next order, the corresponding product should be reduced (buy several items to reduce several inventory), analysis: The monitoring who: Ord monitoring action: Insert trigger time: After trigger event: Update CREATE TRIGGER T1afterinserton ordfor each rowbeginupdate goods set num=num-2 where gid=1;end; in-depth analysis will find that all the data in this trigger is dead, and every time you buy anything, it will only reduce gid= 1 of the goods.
How to reference a row's value in a triggerFor insert, the new row is represented by new, and the value of each column in the row is represented by the new column name. For delete, the deleted row is represented by old, and the value of each column in the row is represented by the old. Column name. For update, the modified row, the data before the modification is represented by old, the previous column name refers to the value in the row before the modification, the modified data is represented by new, and the new column name refers to the value in the modified row.
Example Upgrade: Create Trigger T1afterinserton ordfor each rowbeginupdate goods set Num=num-new.much where gid=new.gid;end;
Note: The old MySQL default encountered a semicolon end, such as the above will prompt the error, you need to first use
Delimite $The default semicolon ends with a successful "$" end (which can also be changed to a different terminator), and then end followed by $, and the statement after begin is still terminated with a semicolon.
Delete trigger Syntax: Drop trigger trigger name;
View all triggers: Show triggers;
the difference between before and afterAfter is the data to complete the increase, delete, change, and then trigger. means that the preceding deletions cannot be affected. Before is the first to complete the trigger, then the data increase, delete, change. The triggered statements are preceded by additions and deletions to the monitor, which means we have an opportunity to review, judge, and modify the actions that will occur. Typical case: To determine the order, if the order quantity >5 is considered a malicious order, forcing the order quantity to be changed to 5. Implementation results:
Basic SQL statements--triggers
