1. 数据审计
– 记录数据变更历史
– 追踪操作日志
– 合规性审计

2. 数据同步
– 表间数据同步
– 缓存更新
– 统计数据更新

3. 业务规则
– 数据验证
– 自动计算
– 状态管理

4. 数据保护
– 防止误删
– 数据备份
– 级联操作

1. 创建审计表
CREATE TABLE data_audit (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
table_name VARCHAR(64) NOT NULL,
record_id BIGINT UNSIGNED NOT NULL,
action ENUM(‘INSERT’, ‘UPDATE’, ‘DELETE’) NOT NULL,
old_data JSON,
new_data JSON,
action_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
action_user VARCHAR(64),
PRIMARY KEY (id),
KEY idx_table_record (table_name, record_id),
KEY idx_action_time (action_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT=’数据审计表’;

2. 创建用户表审计触发器
DELIMITER //

CREATE TRIGGER audit_user_insert
AFTER INSERT ON users
FOR EACH ROW
BEGIN
INSERT INTO data_audit (table_name, record_id, action, new_data, action_user)
VALUES (‘users’, NEW.id, ‘INSERT’, JSON_OBJECT(
‘user_name’, NEW.user_name,
’email’, NEW.email,
‘status’, NEW.status
), CURRENT_USER());
END //

CREATE TRIGGER audit_user_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
INSERT INTO data_audit (table_name, record_id, action, old_data, new_data, action_user)
VALUES (‘users’, NEW.id, ‘UPDATE’, JSON_OBJECT(
‘user_name’, OLD.user_name,
’email’, OLD.email,
‘status’, OLD.status
), JSON_OBJECT(
‘user_name’, NEW.user_name,
’email’, NEW.email,
‘status’, NEW.status
), CURRENT_USER());
END //

CREATE TRIGGER audit_user_delete
AFTER DELETE ON users
FOR EACH ROW
BEGIN
INSERT INTO data_audit (table_name, record_id, action, old_data, action_user)
VALUES (‘users’, OLD.id, ‘DELETE’, JSON_OBJECT(
‘user_name’, OLD.user_name,
’email’, OLD.email,
‘status’, OLD.status
), CURRENT_USER());
END //

DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

3. 测试审计触发器
INSERT INTO users (user_name, email, status, created_at)
VALUES (‘audit_test’, ‘audit@test.com’, 1, NOW());

输出示例：
Query OK, 1 row affected (0.01 sec)

4. 查看审计记录
SELECT * FROM data_audit WHERE table_name = ‘users’ ORDER BY action_time DESC LIMIT 1;

输出示例：
+—-+————+———–+——–+—————————————————-+—————————————————-+———————+————-+
| id | table_name | record_id | action | old_data | new_data | action_time | action_user |
+—-+————+———–+——–+—————————————————-+—————————————————-+———————+————-+
| 1 | users | 1503 | INSERT | NULL | {“user_name”: “audit_test”, “email”: “audit@test.com”, “status”: 1} | 2026-04-04 10:45:00 | root@localhost |
+—-+————+———–+——–+—————————————————-+—————————————————-+———————+————-+
1 row in set (0.00 sec)

1. 创建订单统计表
CREATE TABLE order_statistics (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
user_id BIGINT UNSIGNED NOT NULL,
total_orders INT NOT NULL DEFAULT 0,
total_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
last_order_time DATETIME,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY uk_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT=’订单统计表’;

2. 创建同步触发器
DELIMITER //

CREATE TRIGGER sync_order_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
INSERT INTO order_statistics (user_id, total_orders, total_amount, last_order_time)
VALUES (NEW.user_id, 1, NEW.amount, NEW.created_at)
ON DUPLICATE KEY UPDATE
total_orders = total_orders + 1,
total_amount = total_amount + NEW.amount,
last_order_time = NEW.created_at;
END //

CREATE TRIGGER sync_order_update
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
IF OLD.status != NEW.status AND NEW.status = 3 THEN
UPDATE order_statistics
SET last_order_time = NEW.created_at
WHERE user_id = NEW.user_id;
END IF;
END //

CREATE TRIGGER sync_order_delete
AFTER DELETE ON orders
FOR EACH ROW
BEGIN
UPDATE order_statistics
SET total_orders = total_orders – 1,
total_amount = total_amount – OLD.amount
WHERE user_id = OLD.user_id;
END //

DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

3. 测试同步触发器
INSERT INTO orders (user_id, amount, status, created_at)
VALUES (1, 200.00, 0, NOW());

输出示例：
Query OK, 1 row affected (0.01 sec)

4. 查看同步数据
SELECT * FROM order_statistics WHERE user_id = 1;

输出示例：
+—-+———+————–+————–+——————+———————+
| id | user_id | total_orders | total_amount | last_order_time | updated_at |
+—-+———+————–+————–+——————+———————+
| 1 | 1 | 52 | 5300.00 | 2026-04-04 10:50:00 | 2026-04-04 10:50:00 |
+—-+———+————–+————–+——————+———————+
1 row in set (0.00 sec)

1. 创建库存表
CREATE TABLE inventory (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
product_id BIGINT UNSIGNED NOT NULL,
quantity INT NOT NULL DEFAULT 0,
warehouse_id INT NOT NULL,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY uk_product_warehouse (product_id, warehouse_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT=’库存表’;

2. 创建库存验证触发器
DELIMITER //

CREATE TRIGGER validate_order_before_insert
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
DECLARE available_quantity INT;

SELECT SUM(quantity) INTO available_quantity
FROM inventory
WHERE product_id = NEW.product_id;

IF available_quantity IS NULL OR available_quantity < NEW.quantity THEN
SIGNAL SQLSTATE ‘45000’
SET MESSAGE_TEXT = ‘库存不足，无法下单’;
END IF;
END //

CREATE TRIGGER update_inventory_after_order
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
UPDATE inventory
SET quantity = quantity – NEW.quantity
WHERE product_id = NEW.product_id
LIMIT 1;
END //

DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

3. 测试验证触发器
INSERT INTO orders (user_id, product_id, quantity, amount, status, created_at)
VALUES (1, 100, 5, 500.00, 0, NOW());

输出示例：
Query OK, 1 row affected (0.01 sec)

4. 查看库存变化
SELECT * FROM inventory WHERE product_id = 100;

输出示例：
+—-+————+———-+————–+———————+
| id | product_id | quantity | warehouse_id | updated_at |
+—-+————+———-+————–+———————+
| 1 | 100 | 95 | 1 | 2026-04-04 10:55:00 |
+—-+————+———-+————–+———————+
1 row in set (0.00 sec)

1. 创建删除保护表
CREATE TABLE deleted_records (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
table_name VARCHAR(64) NOT NULL,
record_id BIGINT UNSIGNED NOT NULL,
record_data JSON NOT NULL,
deleted_by VARCHAR(64),
deleted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
KEY idx_table_record (table_name, record_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT=’删除记录保护表’;

2. 创建删除保护触发器
DELIMITER //

CREATE TRIGGER protect_user_delete
BEFORE DELETE ON users
FOR EACH ROW
BEGIN
INSERT INTO deleted_records (table_name, record_id, record_data, deleted_by)
VALUES (‘users’, OLD.id, JSON_OBJECT(
‘user_name’, OLD.user_name,
’email’, OLD.email,
‘status’, OLD.status,
‘created_at’, OLD.created_at
), CURRENT_USER());
END //

DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

3. 测试删除保护触发器
DELETE FROM users WHERE id = 3;

输出示例：
Query OK, 1 row affected (0.01 sec)

4. 查看删除保护记录
SELECT * FROM deleted_records WHERE table_name = ‘users’ ORDER BY deleted_at DESC LIMIT 1;

输出示例：
+—-+————+———–+————————————————————+————-+———————+
| id | table_name | record_id | record_data | deleted_by | deleted_at |
+—-+————+———–+————————————————————+————-+———————+
| 1 | users | 3 | {“user_name”: “user003”, “email”: “user003@test.com”, “status”: 1} | root@localhost | 2026-04-04 11:00:00 |
+—-+————+———–+————————————————————+————-+———————+
1 row in set (0.00 sec)

1. 适合使用触发器的场景
– 必须在数据库层面保证的数据一致性
– 需要自动执行的审计和日志
– 跨表的数据同步
– 复杂的业务规则验证

2. 不适合使用触发器的场景
– 可以在应用层实现的逻辑
– 需要复杂计算的场景
– 性能要求极高的场景
– 需要外部系统交互的场景

3. 触发器设计原则
– 保持逻辑简单
– 避免嵌套调用
– 充分测试验证
– 完善文档说明

4. 性能优化建议
– 减少触发器数量
– 优化触发器逻辑
– 考虑批量操作影响
– 定期评估必要性