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$

Test form: 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 increase

Create trigger TG after delete on O

For each row

Begin

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

end$

Six: Modify an order, the product table corresponding changes

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 to complete the data additions and deletions, and then trigger, the previous data operation completed

Before is the first trigger, to determine whether the trigger condition is satisfied, and then the data to be increased and censored operation

MySQL database trigger (trigger)