Mysql trigger, mysql trigger instance

Mysql trigger, mysql trigger instance
Create a trigger

Delimiter $ change the end symbol of mysql to $

Create trigger <TRIGGER Name> <----- TRIGGER name {BEFORE | AFTER} <----- TRIGGER time: TRIGGER execution time setting: it can be set to {INSERT | UPDATE | DELETE} ON <Table Name> <----- the table name trigger belongs to a table before or after an event occurs: when the insert, update, or delete operation is performed on this table, the trigger is activated. We cannot schedule two triggers for the same event of the same table. For each rowfor each row clause notifies the trigger to execute an action on every ROW instead of the entire table. <Trigger SQL statement> the trigger contains the SQL statement to be triggered: The statement here can be any legal statement, including compound statements, but the statements here are subject to the same restrictions as those of functions.

Create a table with a trigger

CREATE TABLE t22 (s1 INTEGER)//CREATE TRIGGER t22_biBEFORE INSERT ON t22FOR EACH ROWBEGINSET NEW.s1 = 55;END;

A table named t22 is created, and a trigger t22_bi is created on table t22. When we want to insert rows to the table, the trigger will be activated, change the value of column s1 to 55.

View existing triggers

show triggers;

Delete trigger

Drop trigger <trigger Name>

Old and new

In the SQL statement of the trigger, you can associate any column in the table. However, you cannot only use the column name to identify it, which will confuse the system, because there may be a new column name (this may be exactly what you want to modify, your action may be to modify the column name), and the old name of the Column exists. Therefore, you must use this syntax to identify:

"NEW. Column name" or "OLD. Column name". In this way, the NEW and OLD columns are processed technically.

Insert does not have this row of data, but you insert this row of data, so it is new.
The delete row originally exists, but this row does not exist after it is deleted, so it is the old one.
The data updated once is the data after the old change is the new

Minor issues

There are only three database losses, but the customer buys 10 to avoid negative inventory

Create trigger t1BEFORE before triggering inserton ordfor each rowbegin # declare a variable declarernum int in the trigger; # assign the inventory num to rnumselect num into rnum from goods where gid = new. gid; # much the quantity you want to buy if new. much> rnum: if the quantity purchased exceeds the database loss, the current inventory is assigned to the quantity to be purchased. set new. much = rnum; end if; then in the modification goods table inventory minus the current purchase volume update goods set num = num-new.much where gid = new. gid; end $

Copyright statement: one step at a time, so that you can easily review your work. If you have any mistakes, please correct them. Thank you very much for your progress!