mysql 触发器语法与应用示例

下面是一份关于“mysql 触发器语法与应用示例”的攻略：

什么是mysql触发器

MySQL触发器是一种特殊的存储过程，当特定的事件（如对一张表进行的 INSERT、UPDATE 和 DELETE 等操作）发生时，MySQL触发器会自动执行一个已经定义好的SQL语句集，因此它可以在数据库发生某些操作时进行响应并执行指定的操作。

触发器语法

其基本语法如下：

CREATE [OR REPLACE] TRIGGER trigger_name 
trigger_time trigger_event 
ON table_name FOR EACH ROW 
trigger_stmt

参数解释：

• CREATE [OR REPLACE] TRIGGER — 创建触发器。OR REPLACE表示如果此触发器已经存在，则替换它。
• trigger_name— 触发器名称，需在一个MySQL数据库中唯一。
• trigger_time— 触发器执行时间，有 BEFORE 或 AFTER 两种情况。
• trigger_event— 触发器事件，可以是INSERT、UPDATE 或 DELETE
• table_name— 触发器作用的表名。
• FOR EACH ROW— 表示针对每一行触发，是一个必选项。
• trigger_stmt— 触发器执行的MySQL语句集合。

触发器示例1：自动更新统计信息

假定现在有一个用户资料的表，名为 users，除去基本的用户信息(id、姓名、性别、年龄)外，还包含一个 score 字段，用于记录用户的当前积分。

我们可以通过触发器自动更新积分的最大值和总和到另外一张表 statistics 中。

首先，我们创建一个统计表 statistics：

CREATE TABLE statistics ( 
  total_count INT UNSIGNED NOT NULL DEFAULT 0, 
  max_score INT UNSIGNED NOT NULL DEFAULT 0 
);

然后，我们创建一个名为 after_update_users 的触发器，该触发器被定义为在每次UPDATE之后在users表中执行。

CREATE TRIGGER after_update_users 
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
  /* 更新最大积分和总积分 */
  UPDATE statistics SET 
    total_count = total_count + 1,
    max_score = GREATEST(max_score, NEW.score)
  /* 处理没有匹配行 */
  WHERE 1=1;

  /* 如果statistics没有产生过行 */
  /* 如果max_score和total_count值皆为0 */
  /* 则我们需要在插入此触发器被执行的第一行时插入他们 */
  IF ROW_COUNT() = 0 THEN
    INSERT INTO statistics (total_count, max_score) 
    VALUES (1, NEW.score);
  END IF;
END;

注意到 GREATEST(max_score, NEW.score) 是一个MySQL内置的函数，用于返回参数中最大的那个数。在这里，max_score表示统计表statistics中最大的积分值，而 NEW.score 则是在插入或更新操作中UPDATE操作更新前这个积分字段新的未更新的值（可以将他看成变量或者自定义参数）。

触发器示例2：在子表有新增时自动更新父表数据

假设现在有两张表， 父表orders和子表items，其中orders由三个字段构成：id、amount、count，而items则是通过id与之关联的明细表，可以拥有多行数据，该表由三个字段构成： id、price和quantity。

当向子表 items 中添加一行新数据时，我们需要在父表 orders 中自动计算出总计行数（count）和总金额数（amount）并更新。

下面是示例SQL语句：

CREATE TABLE orders (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  amount DECIMAL(7, 2) NOT NULL DEFAULT 0.00,
  count INT NOT NULL DEFAULT 0
);

CREATE TABLE items (
  id INT NOT NULL,
  price DECIMAL(7, 2) NOT NULL DEFAULT 0.00,
  quantity INT NOT NULL DEFAULT 0,
  PRIMARY KEY(id)
) ENGINE=INNODB;

INSERT INTO orders (amount, count) VALUES (0.00, 0);

为了将子表 items 与父表orders关联起来，我们需要在 items 表中添加一个触发器：

DELIMITER //
CREATE TRIGGER after_insert_items 
AFTER INSERT ON items 
FOR EACH ROW
BEGIN
  /* 通过 SUM(id)， sum(price*quantity)来计算总金额和总数量 */
  UPDATE orders o
  SET
    amount = (SELECT SUM(price*quantity) FROM items WHERE id = NEW.id),
    count = (SELECT SUM(quantity) FROM items WHERE id = NEW.id)
  WHERE o.id = NEW.id;
END;
//

总结

在本文中，我们讨论了 MySQL 触发器的语法和应用示例。通过这两个示例，我们可以了解 MySQL 触发器的使用场景和优点，使得我们在实际应用中更加灵活高效。