MySQL database trigger)

MySQL database trigger)

MySQL trigger: monitors a situation and triggers an operation

I. Four Elements

Trigger time: before/after

Location: table

Monitoring Operation: insert/update/delete

Trigger operation: insert/update/delete

 

Ii. Create a trigger

Delimiter $ # change the SQL terminator to $ to facilitate writing SQL statements triggered

Create trigger triggerName after/before insert/update/delete on tableName

For each row

Begin

SQL statement; # The statement to be triggered

End $

 

Test Table: item table (g), order table (o)

3. delete a monitor

Drop trigger triggerName

 

4. Order (o) and item 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 monitoring insert operations, new rows are represented by new

The value of each column in the row, which is retrieved using the new. Column name.

 

5. delete an order and add the item table accordingly.

Create trigger tg after delete on o

For each row

Begin

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

End $

 

6. Modify an order and change the item table accordingly.

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 $

 

7. Differences between after and before

After is the process of adding, deleting, modifying, and triggering the data operation.

Before is triggered first to determine whether the trigger conditions are met, and then add, delete, and modify data.

This article permanently updates the link address: