cbear小站
large picture of the cover

2-9-TRIGGER触发器


1. 触发器基础

触发器(trigger)是MySQL提供给程序员和数据分析员来保证数据完整性的一种方法 , 它是与表事件相关的特殊的存储过程 , 它的执行不是由程序调用 , 也不是手工启动 , 而是由事件来触发 , 比如当对一个表进行操作(insert ,delete , update) 时就会激活它执行。简单理解为: 你执行一条sql语句 , 这条sql语句的执行会自动去触发执行其他的sql语句。

按照SQL标准 , 触发器可以分为行级触发器(row-level trigger)和语句级触发器( statement-level trigger)。行级触发器对于修改的每一行数据都会激活一次 ,如果一个语句插入了100行数据 ,将会调用触发器100次 ;语句级触发器针对每个语句激活一次 , 一个插入 100行数据的语句只会调用一次触发器。MySQL 只支持行级触发器 , 不支持语句级触发器。

不同事件可以激活不同类型的触发器。

INSERT事件触发器用于插入数据的操作 , 包括 INSERT、LOAD DATA、REPLACE 语句等 ;

UPDATE 事件触发器用于更新操作 , 例如UPDATE语句 ;

DELETE事件触发器用于删除操作 , 例如DELETE和REPLACE语句等

DROP TABLE和TRUNCATE TABLE语句不会激活删除触发器。

另外 , MySQL触发器可以在触发事件之前或者之后执行 , 分别称为BEFORE触发器和AFTER触发器。这两种触发时机可以和不同的触发事件进行组合 , 例如BEFORE INSERT触发器或者AFTER UPDATE触发器。

MySQL触发器的优点包括:

  • 记录并审核用户对表中数据的修改操作 , 实现审计功能 ;

  • 实现比检查约束更复杂的完整性约束 ,例如禁止非业务时间的数据操作;

  • 实现某种业务逻辑 , 例如增加或删除员工时自动更新部门中的人数 ;

  • 同步实时地复制表中的数据。

虽然触发器功能强大 , 但是它也存在一些缺点 :

  • 触发器会增加数据库结构的复杂度 , 而且触发器对应用程序不可见 , 难以调试 ;

  • 触发器需要占用更多的数据库服务器资源 , 尽量使用数据库提供的非空、唯一、检查约束等；

  • 触发器不能接收参数 , 只能基于当前的触发对象进行操作。

2. 触发器使用

2.1 创建触发器

CREATE [DEFINER = { user | CURRENT_USER }]
    TRIGGER trigger_name
    trigger_time trigger_event
    ON tbl_name FOR EACH ROW
    trigger_body  

说明 :

  • trigger_name : 触发器的名称

  • trigger_time : {BEFORE | AFTER } , 表示在事件之前或之后触发

  • trigger_event: : {INSERT |UPDATE |DELETE } , 触发的具体事件

  • tbl_name : 该触发器作用在表名

  • FOR EACH ROW : 表明这是一个行级触发器

  • trigger_body : 是触发器执行的具体语句

请注意 , trigger_event与以表操作方式激活触发程序的SQL语句并不很类似 , 这点很重要。

例如 , 关于INSERT的BEFORE触发程序不仅能被INSERT语句激活 , 也能被LOAD DATA语句激活。

可能会造成混淆的例子之一是INSERT INTO .. ON DUPLICATE UPDATE ...语法 : BEFORE INSERT触发程序对于每一行将激活 , 后跟AFTER INSERT触发程序 , 或BEFORE UPDATE和AFTER UPDATE触发程序 , 具体情况取决于行上是否有重复键。

对于具有相同触发程序动作时间和事件的给定表 , 不能有两个触发程序。

例如 , 对于某一表 , 不能有两个BEFORE UPDATE触发程序。但可以有1个BEFORE UPDATE触发程序和1个BEFORE INSERT触发程序 , 或1个BEFORE UPDATE触发程序和1个AFTER UPDATE触发程序。

trigger_body是当触发程序激活时执行的语句。

如果你打算执行多个语句 , 可使用BEGIN ... END复合语句结构。这样 ,就能使用存储子程序中允许的相同语句。

创建一个单执行语句的触发器

CREATE TABLE account(acct_num INT ,amount DECIMAL(10,2));
CREATE TRIGGER ins_sum BEFORE INSERT ON account
FOR EACH ROW SET @SUM=@SUM+new.amount;

首先创建一个account表 , 表中有两个字段 , 分别为: acct_num字段(定义为int类型) amount字段(定义成浮点类) ;其次创建一个名为ins_sum的触发器 , 触发的条件是向数据表account插入数据之前 , 对新插入的amount字段值进行求和计算

SET @sum=0;
INSERT INTO account VALUES(1,1.00),(2,2.00);
SELECT @sum;

结果如下:

MariaDB [hellodb]> SELECT @sum;
+------+
| @sum |
+------+
| 3.00 |
+------+
1 row in set (0.000 sec)
​

NEW与OLD详解

MySQL中定义了NEW和OLD , 用来表示触发器的所在表中 , 触发了触发器的那一行数据 , 来引用触发器中发生变化的记录内容, 具体地:

  • 在INSERT型触发器中 , NEW用来表示将要(BEFORE) 或已经(AFTER)插入的新数据;

  • 在UPDATE型触发器中 , OLD用来表示将要或已经被修改的原数据, NEW用来表示将要或已经修改为的新数据;

  • 在DELETE型触发器中 ,OLD用来表示将要或已经被删除的原数据 ;

使用方法: NEW.columnName ( columnName为相应数据表某一列名)

另外 , OLD是只读的 , 而NEW则可以在触发器中使用SET赋值 , 这样不会再次触发触发器 , 造成循环调用(如每插入一个学生前 , 都在其学号前加“2013”）。

范例

# 创建表 创建触发器
mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
Query OK, 0 rows affected (0.03 sec)
mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);
​
mysql> delimiter //
mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account
       FOR EACH ROW
       BEGIN
           IF NEW.amount < 0 THEN
               SET NEW.amount = 0;
           ELSEIF NEW.amount > 100 THEN
               SET NEW.amount = 100;
           END IF;
       END;//
mysql> delimiter ;
​
# 验证触发器作用
mysql> select * from account;
+----------+---------+
| acct_num | amount  |
+----------+---------+
|      137 |   14.98 |
|      141 | 1937.50 |
|       97 | -100.00 |
+----------+---------+
3 rows in set (0.00 sec)
​
mysql> update account set amount = 114.98 where acct_num = 137;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
​
mysql> select * from account;
+----------+---------+
| acct_num | amount  |
+----------+---------+
|      137 |  100.00 |
|      141 | 1937.50 |
|       97 | -100.00 |
+----------+---------+
3 rows in set (0.00 sec)

范例

#创建触发器 , 在向学生表INSERT数据时 , 学生数增加 , DELETE学生时 , 学生数减少
CREATE TABLE student_info (
    stu_id INT(11) NOT NULL AUTO_INCREMENT ,
    stu_name VARCHAR(255) DEFAULT NULL,
    PRIMARY KEY (stu_id)
);
CREATE TABLE student_count (
    student_count INT(11) DEFAULT 0
);
INSERT INTO student_count VALUES(0);  
​
CREATE TRIGGER trigger_student_count_insert
AFTER INSERT
ON student_info FOR EACH ROW
UPDATE student_count SET student_count=student_count+1;  
​
CREATE TRIGGER trigger_student_count_delete
AFTER DELETE
ON student_info FOR EACH ROW
UPDATE student_count SET student_count=student_count-1;
​
[root@centos8 ~]#cat /var/lib/mysql/hellodb/trigger_student_count_delete.TRN
TYPE=TRIGGERNAME
trigger_table=student_info
[root@centos8 ~]#cat /var/lib/mysql/hellodb/trigger_student_count_insert.TRN
TYPE=TRIGGERNAME
trigger_table=student_info
[root@centos8 ~]#cat /var/lib/mysql/hellodb/student_info.TRG
TYPE=TRIGGERS
triggers='CREATE DEFINER=`root`@`localhost` TRIGGER
trigger_student_count_insert\nAFTER INSERT\nON student_info FOR EACH ROW\nUPDATE
student_count SET student_count=student_count+1' 'CREATE
DEFINER=`root`@`localhost` TRIGGER trigger_student_count_delete\nAFTER
DELETE\nON student_info FOR EACH ROW\nUPDATE student_count SET
student_count=student_count-1'
sql_modes=1411383296 1411383296
definers='root@localhost' 'root@localhost'
client_cs_names='utf8' 'utf8'
connection_cl_names='utf8_general_ci' 'utf8_general_ci'
db_cl_names='utf8_general_ci' 'utf8_general_ci'
created=158207907828 158207907970

范例

# 禁止删除数据 即使你有权限
mysql> select * from student;
+--------------+------+--------+-------+-------+
| increment_id | s_id | s_name | s_sex | s_age |
+--------------+------+--------+-------+-------+
|            1 | 1001 | sdfsd  | 男    |    18 |
|            2 | 1003 | zsdfsd | 女    |    19 |
+--------------+------+--------+-------+-------+
2 rows in set (0.00 sec)
​
mysql> delimiter //
mysql> CREATE TRIGGER `tri_delstu` BEFORE DELETE ON `student` FOR EACH ROW begin
    -> declare msg varchar(255);
    -> set msg="不允许删除学生信息";
    -> SIGNAL SQLSTATE 'HY000' SET  MESSAGE_TEXT = msg;
    -> end; //
Query OK, 0 rows affected (0.02 sec)
​
mysql> delimiter ;
mysql> delete from student where s_id = 1003;
ERROR 1644 (HY000): 不允许删除学生信息
​
# 禁止更新某个字段
mysql> delimiter //
mysql> CREATE TRIGGER trg__updateSid BEFORE UPDATE ON `student`
    -> FOR EACH ROW
    -> BEGIN
    ->  DECLARE msg VARCHAR(100); 
    ->  IF NEW.s_id <> OLD.s_id THEN
    ->  SET msg='不允许修改学号'; 
    ->  SIGNAL SQLSTATE 'HY000' SET message_text = msg; 
    ->  END IF; 
    -> END; //
Query OK, 0 rows affected (0.06 sec)
​
mysql> delimiter ;
mysql> update student set s_id = 1002 where increment_id = 2;
ERROR 1644 (HY000): 不允许修改学号
​
# 限制修改范围
mysql> delimiter //
mysql> CREATE TRIGGER `tri_update_age` BEFORE UPDATE ON `student` FOR EACH ROW BEGIN
    ->         DECLARE msg VARCHAR(20);
    ->   IF (NEW.s_age<0) THEN 
    ->         set msg="年龄不能小于0";
    ->   signal sqlstate 'HY000' set message_text=msg;
    ->         END IF;
    -> END; //
Query OK, 0 rows affected (0.02 sec)
​
mysql> delimiter ;
mysql> update student set s_age=10 where s_id = 1001;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
​
mysql> update student set s_age=-10 where s_id = 1001;
ERROR 1644 (HY000): 年龄不能小于0

2.2 查看触发器

  • 语法

    SHOW TRIGGERS [{FROM | IN} db_name]
    [LIKE 'pattern' | WHERE expr]

    注 : 在当前数据库对应的目录下 , 可以查看到新生成的相关文件 : trigger_name.TRN,table_name.TRG

    范例

    MariaDB [hellodb]> show triggers \G;
*************************** 1. row ***************************
             Trigger: upd_check
               Event: UPDATE
               Table: account
           Statement: BEGIN
           IF NEW.amount < 0 THEN
               SET NEW.amount = 0;
           ELSEIF NEW.amount > 100 THEN
               SET NEW.amount = 100;
           END IF;
       END
              Timing: BEFORE
             Created: 2023-03-16 22:23:42.88
            sql_mode: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.001 sec)

  • 查看数据库实例中所有触发器

    SELECT
 a.TRIGGER_SCHEMA,
 a.TRIGGER_NAME,
 a.ACTION_TIMING,
 a.EVENT_OBJECT_TABLE,
 a.EVENT_MANIPULATION 
FROM
 information_schema.`TRIGGERS` a 
WHERE
 a.TRIGGER_SCHEMA NOT IN ( 'information_schema', 'performance_schema', 'mysql', 'sys' );

    范例

    MariaDB [hellodb]> SELECT
    ->  a.TRIGGER_SCHEMA,
    ->  a.TRIGGER_NAME,
    ->  a.ACTION_TIMING,
    ->  a.EVENT_OBJECT_TABLE,
    ->  a.EVENT_MANIPULATION 
    -> FROM
    ->  information_schema.`TRIGGERS` a 
    -> WHERE
    ->  a.TRIGGER_SCHEMA NOT IN ( 'information_schema', 'performance_schema', 'mysql', 'sys' );
+----------------+--------------+---------------+--------------------+--------------------+
| TRIGGER_SCHEMA | TRIGGER_NAME | ACTION_TIMING | EVENT_OBJECT_TABLE | EVENT_MANIPULATION |
+----------------+--------------+---------------+--------------------+--------------------+
| hellodb        | upd_check    | BEFORE        | account            | UPDATE             |
+----------------+--------------+---------------+--------------------+--------------------+
1 row in set (0.002 sec)

  • 查询系统表information_schema.triggers的方式指定查询条件 , 查看指定的触发器信息。

    USE information_schema;
SELECT * FROM triggers WHERE trigger_name='trigger_name';

    范例

    MariaDB []> USE information_schema;
MariaDB [information_schema]> SELECT * FROM triggers WHERE trigger_name='upd_check';

2.3 删除触发器

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name

IF EXISTS可以避免触发器不存在时产生错误。

熊熊

site logo

Hi,Friend

© 2025 cbear小站