How to create triggers using MySQL database

Triggers in MySQL trigger certain operations by events such as insert, update, and delete. If the trigger conditions of the trigger are met, the database will execute the program statement defined in the trigger, and the trigger's There may be only one execution statement, or there may be multiple execution statements. Then how to create a trigger, the specific operations are as follows:

1. First, find "Trigger" under the database world, right-click "Create" "Trigger", a pop-up will pop up to enter the new trigger name, fill in the name "city_trigger",

as shown below:

2. After inputting, click the "Create" button to generate city_trigger,

as shown below:

##3. In order not to affect other table structures, a new database table t_city_info is created here. The code is as follows:

CREATE TABLE `world`.`t_city_info`(  
  `id` INT(8) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `city_code` INT(4) COMMENT '城市编码',
  `city_name` VARCHAR(20) COMMENT '城市名称',
  `city_detail` VARCHAR(100) COMMENT '描述',
  PRIMARY KEY (`id`)
) CHARSET=utf8;

As shown in the figure below:

4. After the database table t_city_info is created, modify the trigger code as follows:

DELIMITER $$

USE `world`$$

DROP TRIGGER /*!50032 IF EXISTS */ `city_trigger`$$

CREATE
    /*!50017 DEFINER = 'root'@'localhost' */
    TRIGGER `city_trigger` BEFORE INSERT ON `t_city_info` 
    FOR EACH ROW BEGIN
    INSERT INTO  t_tri_time VALUES (NOW());
    END;
$$

DELIMITER ;

As shown below:

5. Modify the `city_trigger` trigger, click the run button, and refresh the database. You can do this in the trigger file Find `city_trigger` under the folder,

as shown below:

6. Then, first insert a piece of data into t_city_info , the code is as follows:

然后，先t_city_info中插入一条数据，代码如下：
INSERT INTO t_city_info (
  id,
  city_code,
  city_name,
  city_detail
) 
VALUES
  (1000, 100, '武汉', '武汉市') ;

After clicking run, an error occurs, the error code is as follows:

1 queries executed, 0 success, 1 errors, 0 warnings
查询：insert into t_city_info ( id, city_code, city_name, city_detail ) values (1000, 100, '武汉', '武汉市')
错误代码： 1146
Table 'world.t_tri_time' doesn't exist
执行耗时   : 0 sec
传送时间   : 0 sec
总耗时      : 0.012 sec

As shown in the figure below:

7. Because t_tri_time does not exist, an error occurs. Then create the t_tri_time database table,

as shown in the figure below:

8. Execute the insert statement again, and the result is successful; then, query the t_tri_time database table, the code is as follows:

SELECT * FROM t_tri_time;

As shown below:

Instructions:

Pay attention to the database trigger creation syntax

Understand the syntax of database table addition, deletion, modification and query

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