【MySQL 12】Trigger

Trigger is a special type of stored procedure, which is different from the stored procedures we introduced before. Triggers are mainly triggered by events and are automatically called and executed. Stored procedures can be called by the name of the stored procedure.
The main function of triggers is to achieve data integrity and consistency between two or more tables that are more complex than referential integrity, thereby ensuring that changes in data in the tables comply with the determination of the database designer. business rules.
A special stored procedure that is automatically executed when a trigger inserts, updates, or deletes a table. Triggers are generally used on constraints with more complex check constraints.
The difference between triggers and ordinary stored procedures is that triggers operate on a certain table. During operations such as update, insert, and delete, the system will automatically call and execute the corresponding trigger on the table.
Triggers in SQL Server 2005 can be divided into two categories: DML triggers and DDL triggers. DDL triggers affect a variety of data definition language statements and are triggered. These statements include create, alter, and drop statements.

DML triggers are divided into:

1. after trigger (triggered after)
a. insert trigger
b. update trigger
c. delete trigger Trigger
2. Instead of trigger (triggered before)
3. Difference
After trigger: It is required that the trigger will be triggered only after a certain operation insert, update, delete is performed, and can only be defined in on the table.
Instead of trigger: It means that it does not execute its defined operations (insert, update, delete) but only executes the trigger itself. Instead of triggers, you can define them on the table or on the view.

inserted table and deleted table

When using triggers, SQL Server will create two special temporary tables for each trigger, namely the inserted table and the deleted table. These two tables are stored in memory and have the same structure as the table where the trigger was created. They are maintained and managed by the system and users are not allowed to modify them. Each trigger can only access its own temporary table. After the trigger is executed, the two tables will be automatically released.
(1) The inserted table is used to store copies of rows affected by insert or update statements. When an insert or update operation is performed, new data rows are added to both the base table and the inserted table that activate the trigger.
(2) The deleted table is used to store copies of rows affected by delete or update statements. When a delete or update operation is performed, the specified original data row is deleted from the base table and then transferred to the deleted table. Generally speaking, the same data rows will not exist in the base table and the deleted table.
Description:
The update operation is divided into two steps: first, transfer the modified original data rows in the basic table to the deleted table, and then copy the modified new data rows from the inserted table to the basic table. In other words, for the update operation, the deleted table stores the old value before modification, and the inserted table stores the new value after modification.

Four elements of trigger creation syntax:

1.监视地点(table) 
2.监视事件(insert/update/delete) 
3.触发时间(after/before) 
4.触发事件(insert/update/delete)

Syntax:

delimiter &&create trigger trigger
Nameafter/before insert/update/delete on 表名
for each row   #这句话在mysql是固定的
beginsql语句;
end&&

Product table

mysql> create table g(
    -> id int auto_increment primary key,    -> name varchar(10),    -> num int    -> );

Order table

mysql> create table o(
    -> idd int auto_increment primary key,    -> gid int,    -> much int    -> );

Insert product:

mysql> insert into g (name,num) value ('juzi',20);mysql> select * from g;
+----+------+------+| id | name | num  |
+----+------+------+|  3 | juzi |   20 |
+----+------+------+

If we did not use triggers: Suppose we now sell 3 items 1, we need to do two things

1. Insert a record into the order table

insert into o(gid,much) values(1,3);

2. Update the remaining quantity of product 1 in the product table

update g set num=num-3 where id=1;

Create trigger:

delimiter &
mysql> create trigger trg1
    -> after insert on o    -> for each row    -> begin    -> update g set num = num -3 where id = 1;    -> end&

Execution:

insert into o(gid,much) values(1,3)$

Result:

You will find that the quantity of product 1 has changed to 7, which means that when we insert an order, the trigger automatically performs the update operation for us.

But now there is a problem, because the num and id in our trigger are hard-coded, so no matter which product we buy, the quantity of product 1 will be updated in the end. For example: we insert another record into the order table: insert into o(gid,much) values(2,3). After execution, we will find that the quantity of product 1 has changed to 4, but the quantity of product 2 has not changed. This is obviously not the case. the results we want. We need to change the trigger we created earlier.

How do we reference the value of the row in the trigger, that is to say, we need to get the value of gid or much in our newly inserted order record.

For insert, the newly inserted row is represented by new, and the value of each column in the row is represented by new.column name.

So now we can change our trigger like this

create trigger tg2 
after insert on o 
for each row 
begin 
update g set num=num-new.much where id=new.gid;(注意此处和第一个触发器的不同) 
end$

The second trigger is created, let’s delete the first trigger first

drop trigger tg1$

Let’s test it again and insert an order record: insert into o(gid,much) values(2,3)$

After execution, it is found that the quantity of product 2 has changed to 7. That's right now.

There are still two situations:

1.当用户撤销一个订单的时候，我们这边直接删除一个订单，我们是不是需要把对应的商品数量再加回去呢？

2.当用户修改一个订单的数量时，我们触发器修改怎么写?

我们先分析一下第一种情况：

监视地点：o表

监视事件：delete

触发时间：after

触发事件：update

对于delete而言：原本有一行,后来被删除，想引用被删除的这一行，用old来表示，old.列名可以引用被删除的行的值。

那我们的触发器就该这样写：

create trigger tg3
after delete on o
for each row
begin
update g set num = num + old.much where id = old.gid;(注意这边的变化)
end$

创建完毕。

再执行

delete from o where oid = 2$

会发现商品2的数量又变为10了。

第二种情况：

监视地点：o表

监视事件：update

触发时间：after

触发事件：update

对于update而言：被修改的行，修改前的数据，用old来表示，old.列名引用被修改之前行中的值；

修改的后的数据，用new来表示，new.列名引用被修改之后行中的值。

那我们的触发器就该这样写：

create trigger tg4
after update on o
for each row
begin
update g set num = num+old.much-new.much where id = old/new.gid;
end$

先把旧的数量恢复再减去新的数量就是修改后的数量了。

我们来测试下：先把商品表和订单表的数据都清掉，易于测试。

假设我们往商品表插入三个商品，数量都是10，

买3个商品1：

insert into o(gid,much) values(1,3)$

这时候商品1的数量变为7；

我们再修改插入的订单记录:

update o set much = 5 where oid = 1$

我们变为买5个商品1，这时候再查询商品表就会发现商品1的数量只剩5了，说明我们的触发器发挥作用了。

以上就是 【MySQL 12】触发器的内容，更多相关内容请关注PHP中文网（m.sbmmt.com）！