(17) Triggers in MySQL

Triggers in MySQL

Trigger

Requirements: There are two tables, an order form, a commodity table. Each order is generated, the inventory of goods is reduced accordingly.

Trigger (Trigger)

Binding a piece of code in advance for a table, and when something changes (or additions or deletions), the system automatically triggers the execution of the code.

Triggers include: event type, trigger time, Trigger object

Event Type: Three types of INSERT, delete, and update.

Trigger time: Trigger before and after before and after

Trigger object: Each record (row) in the table

There can only be one type of trigger for a single trigger time in a table: A table has up to 6 triggers.

Create a Trigger

------------------------------------------------------------

--Temporarily modify the statement terminator

Delimiter Custom Symbols

Create TRIGGER Trigger Name Trigger time Event type on table name for each row

Begin--Represents the opening brace: start

--Inside is the content of the trigger

End--Represents the closing curly brace: End

--Modify the temporary modified statement terminator to a semicolon

delimiter;

------------------------------------------------------------

Create a table and insert data

--Create a trigger to generate an order, commodity inventory reduction

Delimiter $

Create trigger After_order after insert on My_order for Each_row

Begin

--Trigger content start

Update My_goods Set INV = inv-new.g_number where id = new.id;

End

Delimit;

View triggers

View all triggers or fuzzy matches

Show trigger [like ' pattern ']

To view the trigger creation statement:

Show create trigger trigger name;

All triggers will be saved to a table in the system information_schemea.triggers

Using triggers

Triggers: No manual calls are required and are triggered automatically when a situation (trigger type) occurs.

Modify Trigger && Delete trigger

Triggers cannot be modified and can only be deleted after new

Drop trigger trigger name;

Trigger record

Trigger record: Regardless of whether the trigger is triggered or not, as long as an operation is ready to be executed, the system will keep the current state of the current operation record and the new state after it is executed for use by the trigger. Where the current state of the operation is saved in the old, the possible patterns after the operation are saved to new.

Old represents a record, new represents a record.

No old at the time of insertion, no new at the time of deletion. Any record has a field name in addition to the data.

Old and new are used: old. Field name/new field name (new means the result after the assumption occurs)

Execute code structure

execution code structure: sequential structure, branching structure, looping structure

< Span style= "font-size:16px" > branching structure: implements the preparation of multiple blocks of code, optionally executing a segment of code according to conditions

only if in MySQL

basic syntax:

-----------------------------------------------------------

if condition judgment then

--satisfies the criteria to execute code

else

< Span style= "font-size:16px" >--code that does not meet conditional execution

end if;

------------------------------------------------------------

 

Triggers combined with if branches cannot generate orders if the number of items is insufficient.

Loop structure

Loop structure: A piece of code executes repeatedly on a specified condition

While Loop Basic Syntax

------------------------------------------------------------

While condition judge do

--code that satisfies the criteria to execute

--Change cycle conditions

End while;

------------------------------------------------------------

loop control : Loop inside the loop to judge, there is no continue and break in MySQL.

Iterate: iterations, similar to the code behind continue, does not execute, loops back

Leave: Leaving a similar break the entire loop ends.

Mode of use: Iterate/leave cycle name;

--Define the loop name

Loop name: While condition do

--Loop body

--Cycle control

Iterate/leave cycle name;

End while;

(17) Triggers in MySQL