Questions about MySQL triggers

trigger

A trigger in Mysql is a database object related to table operations. When a specified event occurs on the table where the trigger is located, it will be called The object, that is, the operation event of the table triggers the execution of the trigger on the table.

Sometimes when changing some data in a table, we also hope that the data in other related tables will also change. This need can be met by using triggers. The use of triggers can simplify the program and increase the flexibility of the program.

The syntax for creating a trigger is as follows:

create trigger  name(触发器名称)
after/befor(触发时间)
insert/update/delete（触发事件，当填写其中一种，其它的操作不会激活触发器）
on (监视的表，即在哪张表上建立触发器 )
for each row (行级触发器)
begin
(sql语句，希望改变的数据，可写多条语句，每条语句必须用 ; 结尾)
end;

The above sql statement will be executed after writing ;, and an error will be reported before the trigger is completed!

The DELIMITER command will be used at this time (delimiter is the delimiter, the meaning of the separator). It is a command and does not require an end-of-statement identifier. The syntax is:

DELIMITER ;; (the end identifier of mysql can be set to other symbols, such as;; or $, and can also be set to multiple lengths)

After this statement, the trigger will not react if it ends with a semicolon. Only when ;; is encountered, it is considered to be the end of the statement. Note: After using it, remember to modify it back!

Assume that there are two tables in the system:
Product table goods (product ID gid, product name goods_name, product quantity goods_num)
Order table ord (order oid, product ID gid, Order quantity goods_much )
To create a trigger to automatically update the quantity of goods in the product table with the order quantity in the order table, the code is as follows:

delmiter $
#判断库存是否足够
create trigger t1
before
insert
on ord
for each row
begin
#声明变量
declare
goods_num int;
select num into goods_num from goods where gid = new.gid;
#判断
if new.much > goods_num then
    set new.much = goods_num;
end if;
update goods set num = num - new.much where gid=new.gid
end$
delmiter ;

Use declare in MySQL to define a local variable. Variables can only be used in BEGIN...END compound statements and should be defined at the beginning of the compound statement.

The NEW keyword is used in the above example, which is similar to INSERTED and DELETED in MS SQL Server. It is defined in MySQL NEW and OLD are used to represent the row of data in the table where the trigger is located that triggered the trigger.
Specifically:
In INSERT type triggers, NEW is used to represent new data that will be (BEFORE) or has been inserted (AFTER);
In UPDATE type triggers, OLD is used to represent that will be or The original data that has been modified, NEW is used to represent the new data that will be or has been modified;
In the DELETE type trigger, OLD is used to represent the original data that will be or has been deleted;

The above is the detailed content of Questions about MySQL triggers. For more information, please follow other related articles on the PHP Chinese website!