How to use trigger in MySQL database

1. Basic concepts

A trigger is a special type of stored procedure. A trigger is triggered by an event. Execution of

trigger trigger is similar to js events

1. Function

• Forcibly check or convert data before writing to the data table (to ensure data security )

• When a trigger error occurs, the result of the change will be revoked (transaction safety)

• Some database management systems can define language for data DDL uses triggers, called DDL triggers

• You can replace the change instructions instead of according to specific situations (mysql does not support)

2. Advantages and Disadvantages of Triggers

2.1. Advantages

• Triggers can achieve cascading changes through related tables in the database (if the data of a table changes , you can use triggers to implement operations on other tables, the user does not know)

• Ensure data security and perform security verification

##2.2. Disadvantages

• Excessive reliance on triggers will inevitably affect the structure of the database and increase the complexity of maintenance

• Making the data unavailable at the program level Control

2. Create trigger

1. Basic syntax

create trigger 触发器名字 触发时机 触发事件 on 表 for each row
begin
end

2. Trigger object

on table for each row The trigger binds all rows in the table. When no specified change occurs in a row, the trigger will be triggered

3. Trigger timing

The row corresponding to each table There are different states. When an SQL command occurs, the data in the row will change. Each row will always have two states: before data operation and after data operation.

• before : The state before the data changes

• after: The state after the data has changed

4. Trigger event

The target of triggers in mysql is changes in data, and the corresponding operations are only write operations (add, delete, modify)

• inert insert operations

• update Update operation

• delete operation

5. Notes

In a table, each triggering time is bound to a trigger There can only be one trigger type corresponding to the event

There can only be one corresponding after insert trigger in a table

There can only be a maximum of 6 triggers

before insert
after insert
before update
after update
before delete
after delete

Requirements:

Place an order to reduce inventory

There are two tables, one is the product table and the other is the order table (retaining the product ID) for each order generated, the corresponding inventory in the product table should change

Create two tables:

create table my_item(
    id int primary key auto_increment,
    name varchar(20) not null,
    count int not null default 0
) comment '商品表';

create table my_order(
    id int primary key auto_increment,
    item_id int not null,
    count int not null default 1
) comment '订单表';

insert my_item (name, count) values ('手机', 100),('电脑', 100), ('包包', 100);

mysql> select * from my_item;
+----+--------+-------+
| id | name   | count |
+----+--------+-------+
|  1 | 手机   |   100 |
|  2 | 电脑   |   100 |
|  3 | 包包   |   100 |
+----+--------+-------+
3 rows in set (0.00 sec)

mysql> select * from my_order;
Empty set (0.02 sec)

Create trigger:

If data insertion occurs in the order table, the corresponding product should be reduced in inventory

delimiter $$
create trigger after_insert_order_trigger after insert on my_order for each row
begin
    -- 更新商品库存
    update my_item set count = count - 1 where id = 1;
end
$$
delimiter ;

3. View the trigger

-- 查看所有触发器
show triggers\G
*************************** 1. row ***************************
             Trigger: after_insert_order_trigger
               Event: INSERT
               Table: my_order
           Statement: begin

    update my_item set count = count - 1 where id = 1;
end
              Timing: AFTER
             Created: 2022-04-16 10:00:19.09
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
  Database Collation: utf8mb4_general_ci
1 row in set (0.00 sec)
-- 查看创建语句
show crate trigger 触发器名字;
-- eg:
show create trigger after_insert_order_trigger;

4. Trigger the trigger

Let the trigger execute , let the corresponding operation occur at the corresponding time in the table specified by the trigger

insert into my_order (item_id, count) values(1, 1);

mysql> select * from my_order;
+----+---------+-------+
| id | item_id | count |
+----+---------+-------+
|  1 |       1 |     1 |
+----+---------+-------+
1 row in set (0.00 sec)

mysql> select * from my_item;
+----+--------+-------+
| id | name   | count |
+----+--------+-------+
|  1 | 手机   |    99 |
|  2 | 电脑   |   100 |
|  3 | 包包   |   100 |
+----+--------+-------+
3 rows in set (0.00 sec)

5. Deleting the trigger

drop trigger 触发器名字;
-- eg
drop trigger after_insert_order_trigger;

6. Application of the trigger

Record keywords new old

6. Improve

Automatic inventory deduction for products

The trigger targets each record in the data table, and each row of data has a corresponding one before and after the operation. The status

The trigger obtains the corresponding data status before execution:

• Save the data status before there is any operation to The status after the

  old keyword in the

• operation is placed in

  new

trigger In the container, you can use old and new to obtain the corresponding record data in the binding table

Basic syntax:

Keyword.Field name

Not all triggers of old and new have

• insert is empty before inserting, and there is no old

• delete clear Data, no new

Item automatically deducts inventory:

delimiter $$
create trigger after_insert_order_trigger after insert on my_order for each row
begin
    -- 通过new关键字获取新数据的id 和数量
    update my_item set count = count - new.count where id = new.item_id;
end
$$
delimiter ;

Trigger trigger:

mysql> select * from my_order;
+----+---------+-------+
| id | item_id | count |
+----+---------+-------+
|  1 |       1 |     1 |
+----+---------+-------+
mysql> select * from my_item;
+----+--------+-------+
| id | name   | count |
+----+--------+-------+
|  1 | 手机   |    99 |
|  2 | 电脑   |   100 |
|  3 | 包包   |   100 |
+----+--------+-------+
insert into my_order (item_id, count) values(2, 3);
mysql> select * from my_order;
+----+---------+-------+
| id | item_id | count |
+----+---------+-------+
|  1 |       1 |     1 |
|  2 |       2 |     3 |
+----+---------+-------+
mysql> select * from my_item;
+----+--------+-------+
| id | name   | count |
+----+--------+-------+
|  1 | 手机   |    99 |
|  2 | 电脑   |    97 |
|  3 | 包包   |   100 |
+----+--------+-------+

2.Optimization

What should I do if the inventory quantity is not as large as the product order?

-- 删除原有触发器
drop trigger after_insert_order_trigger;
-- 新增判断库存触发器
delimiter $$
create trigger after_insert_order_trigger after insert on my_order for each row
begin
    -- 查询库存
    select count from my_item where id = new.item_id into @count;

    -- 判断
    if new.count > @count then
        -- 中断操作，暴力抛出异常
        insert into xxx values ('xxx');

    end if;
    
    -- 通过new关键字获取新数据的id 和数量
    update my_item set count = count - new.count where id = new.item_id;
end
$$
delimiter ;

Result verification:

mysql> insert into my_order (item_id, count) values(3, 101);
ERROR 1146 (42S02): Table 'mydatabase2.xxx' doesn't exist
mysql> select * from my_order;
+----+---------+-------+
| id | item_id | count |
+----+---------+-------+
|  1 |       1 |     1 |
|  2 |       2 |     3 |
+----+---------+-------+
2 rows in set (0.00 sec)

mysql> select * from my_item;
+----+--------+-------+
| id | name   | count |
+----+--------+-------+
|  1 | 手机   |    99 |
|  2 | 电脑   |    97 |
|  3 | 包包   |   100 |
+----+--------+-------+
3 rows in set (0.00 sec)

The above is the detailed content of How to use trigger in MySQL database. For more information, please follow other related articles on the PHP Chinese website!