sql编程介绍触发器

触发器（Trigger）是一种特殊的存储过程，可以在特定的事件发生时自动执行。它可以在 INSERT、UPDATE 或 DELETE 操作之前或之后自动执行一些特定的操作，如记录日志、更新关联表数据、修改数据等等。本文将详细介绍 SQL 中的触发器使用方法，包括创建触发器、触发器类型、使用场景及案例说明。

## 创建触发器

创建触发器需要使用 CREATE TRIGGER 语句，语法如下：

```

CREATE TRIGGER trigger_name

{BEFORE | AFTER} {INSERT | UPDATE | DELETE}

ON table_name

[FOR EACH ROW]

trigger_body

```

其中：

- `trigger_name`：触发器的名称。

- `BEFORE | AFTER`：触发器的类型，用于指定触发器是在操作之前还是之后执行。

- `INSERT | UPDATE | DELETE`：触发器所针对的操作类型。

- `table_name`：触发器所针对的表名。

- `FOR EACH ROW`：用于指定触发器是基于行的，即每一行数据都会触发一次。

- `trigger_body`：触发器的主体部分，包含要执行的 SQL 语句。

例如，我们可以创建一个在用户表（users）中添加新数据时自动添加一条日志记录的触发器，代码如下：

```

CREATE TRIGGER log_user_insert

AFTER INSERT

ON users

FOR EACH ROW

INSERT INTO user_logs (user_id, action) VALUES (NEW.id, 'insert');

```

在该触发器中，我们使用 AFTER 关键字指定触发器是在 INSERT 操作之后执行，使用 ON 关键字指定触发器所针对的表为 users，使用 FOR EACH ROW 指定触发器是基于行的。在 trigger_body 部分，我们使用 INSERT INTO 语句向 user_logs 表中添加一条新的日志记录，其中 NEW.id 代表新增数据的 id 值。

## 触发器类型

SQL 触发器主要可以分为以下两种类型：

### BEFORE 触发器

BEFORE 触发器在操作（INSERT、UPDATE 或 DELETE）执行之前执行，可以用于对操作的数据进行检查和修改。如果 BEFORE 触发器中抛出异常，则操作会被拒绝，并直接返回错误。

例如，我们可以创建一个 BEFORE 触发器，用于检查自动编号字段（id）是否为空，代码如下：

```

CREATE TRIGGER check_id_not_null

BEFORE INSERT

ON users

FOR EACH ROW

BEGIN

IF NEW.id IS NULL THEN

SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'id cannot be null';

END IF;

END;

```

在该触发器中，我们使用 BEFORE 关键字指定触发器是在 INSERT 操作之前执行，使用 ON 关键字指定触发器所针对的表为 users。在 trigger_body 部分，我们使用 IF 语句判断新增数据的 id 字段是否为空，如果为空则使用 SIGNAL 语句抛出异常，中止插入操作。

### AFTER 触发器

AFTER 触发器在操作（INSERT、UPDATE 或 DELETE）执行之后执行，可以用于对操作的数据进行后续处理，如更新关联数据、记录日志等。

例如，我们可以创建一个 AFTER 触发器，用于在新增或修改用户信息时自动更新关联的部门信息，代码如下：

```

CREATE TRIGGER update_department_info

AFTER INSERT OR UPDATE

ON users

FOR EACH ROW

UPDATE departments SET user_count = (

SELECT COUNT(*) FROM users WHERE department_id = NEW.department_id

) WHERE id = NEW.department_id;

```

在该触发器中，我们使用 AFTER 关键字指定触发器是在 INSERT 或 UPDATE 操作之后执行，使用 ON 关键字指定触发器所针对的表为 users。在 trigger_body 部分，我们使用 UPDATE 语句更新关联的 departments 表中的 user_count 字段，这个字段记录了当前部门下的用户数。我们使用 SELECT COUNT(*) FROM users WHERE department_id = NEW.department_id 计算出当前部门下的用户数，使用 WHERE id = NEW.department_id 限定要更新的部门记录。

## 使用场景及案例

触发器可以用于实现一些较为复杂的业务逻辑，以下是一些使用场景及案例：

### 记录日志

在数据库应用中，通常需要记录一些操作日志，例如新增、修改、删除等操作时需要记录一些相关信息，如操作人员、操作时间、操作类型等。此时我们可以使用触发器，在操作完成后自动插入一条新的日志记录。

```

CREATE TABLE user_logs (

id INT PRIMARY KEY AUTO_INCREMENT,

user_id INT,

action VARCHAR(10),

created_time DATETIME DEFAULT CURRENT_TIMESTAMP

);

CREATE TRIGGER log_user_insert

AFTER INSERT

ON users

FOR EACH ROW

INSERT INTO user_logs (user_id, action) VALUES (NEW.id, 'insert');

CREATE TRIGGER log_user_update

AFTER UPDATE

ON users

FOR EACH ROW

INSERT INTO user_logs (user_id, action) VALUES (NEW.id, 'update');

CREATE TRIGGER log_user_delete

AFTER DELETE

ON users

FOR EACH ROW

INSERT INTO user_logs (user_id, action) VALUES (OLD.id, 'delete');

```

在该例子中，我们在 users 表中每次插入、更新或删除数据时都会自动向 user_logs 表中添加一条相应的日志记录。

### 数据级别安全检查

在某些情况下，我们需要对数据进行一些级别检查，例如限制某些用户只能访问特定的数据，在这种情况下，我们可以使用触发器来实现这一要求。

```

CREATE TRIGGER check_user_department

BEFORE INSERT OR UPDATE

ON users

FOR EACH ROW

BEGIN

DECLARE user_department INT;

SELECT department_id INTO user_department FROM users WHERE id = NEW.id;

IF user_department != NEW.department_id THEN

SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'You can only assign user to the same department';

END IF;

END;

```

在该例子中，我们在 users 表中新增或修改数据时，使用 BEFORE 触发器自动检查用户所属部门是否为有效部门。如果不是，则抛出异常，中止插入或更新操作。

### 自动更新关联数据

在数据库应用中，通常需要更新一些关联数据，例如在新增、修改、删除某条数据时需要更新关联数据的计数。此时我们可以使用触发器，在相关操作完成后自动更新关联数据。

```

CREATE TRIGGER update_department_user_count

AFTER INSERT OR UPDATE OR DELETE

ON users

FOR EACH ROW

UPDATE departments SET user_count = (

SELECT COUNT(*) FROM users WHERE department_id = NEW.department_id

) WHERE id = NEW.department_id OR id = OLD.department_id;

```

在该例子中，我们在 users 表中新增、修改或删除数据时，使用 AFTER 触发器自动更新关联的 departments 表中的 user_count 字段，这个字段记录了当前部门下的用户数。我们使用 SELECT COUNT(*) FROM users WHERE department_id = NEW.department_id 计算出当前部门下的用户数，使用 WHERE id = NEW.department_id OR id = OLD.department_id 限定要更新的部门记录。

## 总结

本文介绍了 SQL 中触发器的使用方法，包括创建触发器、触发器类型、使用场景及案例说明。需要注意的是，在使用触发器时应该避免使用过于复杂的逻辑，以免影响数据库性能。同时，在触发器执行时应该注意数据的一致性，避免死锁等问题的发生。 如果你喜欢我们三七知识分享网站的文章， 欢迎您分享或收藏知识分享网站文章 欢迎您到我们的网站逛逛喔！https://www.37seo.cn/