MySQL database trigger (trigger)

MySQL trigger (trigger): monitor a situation and trigger an action

1:4 elements

Trigger Time: Before/after

Location: Table

Monitoring operation: Insert/update/delete

Trigger Action: Insert/update/delete

II: Create a Trigger

Delimiter $ #将sql结束符改为 $ Convenient write-triggered SQL statement

Create trigger Triggername After/before Insert/update/delete on TableName

For each row

Begin

SQL statements; #想要触发的语句

end$

Measured frequently: commodity table (g). Order Form (O)

Three: Delete monitor

Drop Trigger Triggername

Four: Order (O). Commodity table (g) Data trigger update

Create trigger TG after insert on O

For each row

Begin

Update g set Num=num-new.much where Id=new.gid;

end$

PS: For the monitoring operation Insert, the new row is represented by new

The value of each column in the row, taken out with the new column name

Five: Delete an order, the product table corresponding to add

Create trigger TG after delete on O

For each row

Begin

Update g set Num=num+old.much where Id=old.gid;

end$

Six: Change an order. Changes to the commodity table

Create trigger TG after update on O

For each row

Begin

Update g set Num=num+old.much-new.much where Id=old.gid;

end$

Seven: After and before differences

After is the first complete data additions and deletions, and then trigger, the previous data operation completed

The before is triggered first, the condition of the trigger is satisfied, and the data is added and manipulated.

MySQL database trigger (trigger)