景德镇网站建设景德镇,所有网上购物的网站,烟台网站设计,WordPress直接调用头像地址基本介绍
触发器是与表有关的数据库对象#xff0c;在 INSERT、UPDATE、DELETE 操作之前或之后触发并执行触发器中定义的 SQL 语句。
触发器的这种特性可以协助应用在数据库端确保数据的完整性、日志记录、数据校验等操作。
使用别名 NEW 和 OLD 来引用触发器中发生变化的记…基本介绍
触发器是与表有关的数据库对象在 INSERT、UPDATE、DELETE 操作之前或之后触发并执行触发器中定义的 SQL 语句。
触发器的这种特性可以协助应用在数据库端确保数据的完整性、日志记录、数据校验等操作。
使用别名 NEW 和 OLD 来引用触发器中发生变化的记录内容这与其他的数据库是相似的。
现在触发器还只支持行级触发不支持语句级触发。
触发器类型OLD的含义NEW的含义INSERT 型触发器无 (因为插入前状态无数据)NEW 表示将要或者已经新增的数据UPDATE 型触发器OLD 表示修改之前的数据NEW 表示将要或已经修改后的数据DELETE 型触发器OLD 表示将要或者已经删除的数据无 (因为删除后状态无数据)
基本操作
创建触发器
DELIMITER $CREATE TRIGGER 触发器名称
BEFORE|AFTER INSERT|UPDATE|DELETE
ON 表名
FOR EACH ROW -- 行级触发器
BEGIN触发器要执行的功能;
END$DELIMITER ;
查看触发器的状态、语法等信息
SHOW TRIGGERS; 删除触发器如果没有指定 schema_name默认为当前数据库
DROP TRIGGER [schema_name.]trigger_name;
注意事项
确保触发器中的逻辑不影响性能尤其是在高频操作的表上。避免在触发器中出现无限循环的情况例如更新触发器又触发了同一操作。
代码示例
通过触发器记录账户表的数据变更日志。包含增加、修改、删除。
数据准备
CREATE TABLE accounts
(id INT AUTO_INCREMENT PRIMARY KEY, -- 账户IDusername VARCHAR(50) NOT NULL UNIQUE, -- 用户名必须唯一password VARCHAR(255) NOT NULL, -- 密码建议加密存储email VARCHAR(100) NOT NULL UNIQUE, -- 邮箱必须唯一created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 创建时间默认当前时间updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP -- 更新时间自动更新
);
CREATE TABLE account_change_log
(log_id INT AUTO_INCREMENT PRIMARY KEY, -- 日志IDaccount_id INT, -- 被更改的账户IDoperation_type VARCHAR(10), -- 操作类型INSERT, UPDATE, DELETEold_value VARCHAR(255), -- 更新前的值new_value VARCHAR(255), -- 更新后的值changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 变更时间默认当前时间FOREIGN KEY (account_id) REFERENCES accounts (id) -- 外键约束引用账户表
);
# 创建 INSERT 型触发器
-- 更改存储过程的结束符为$$
DELIMITER $$
-- 创建一个触发器在accounts表插入数据后触发
CREATE TRIGGER after_account_insertAFTER INSERTON accountsFOR EACH ROW
BEGIN-- 将插入操作记录到account_change_log表中INSERT INTO account_change_log (account_id, operation_type, new_value)VALUES (NEW.id, INSERT, NEW.username); -- 记录新增账户的用户名
END $$
-- 复原存储过程的结束符为默认的;
DELIMITER ;# 创建 UPDATE 型触发器
-- 再次更改存储过程的结束符为$$
DELIMITER $$
-- 创建一个触发器在accounts表更新数据后触发
CREATE TRIGGER after_account_updateAFTER UPDATEON accountsFOR EACH ROW
BEGIN-- 将更新操作记录到account_change_log表中INSERT INTO account_change_log (account_id, operation_type, old_value, new_value)VALUES (OLD.id, UPDATE, OLD.username, NEW.username); -- 记录更新前后的用户名
END $$
-- 复原存储过程的结束符为默认的;
DELIMITER ;-- 创建 DELETE 型触发器
-- 再次更改存储过程的结束符为$$
DELIMITER $$
-- 创建一个触发器在accounts表删除数据后触发
CREATE TRIGGER after_account_deleteAFTER DELETEON accountsFOR EACH ROW
BEGIN-- 将删除操作记录到account_change_log表中INSERT INTO account_change_log (account_id, operation_type, old_value)VALUES (OLD.id, DELETE, OLD.username); -- 记录删除账户的用户名
END $$
-- 复原存储过程的结束符为默认的;
DELIMITER ;# 插入测试执行上述插入操作后可以查看 account_change_log 表确认新用户的插入记录是否正确。
INSERT INTO accounts (username, password, email) VALUES
(user1, password1, user1example.com),
(user2, password2, user2example.com),
(user3, password3, user3example.com); # 更新测试更新某个用户的信息查看 account_change_log 表确认更新的记录是否正确。
UPDATE accounts SET username updated_user1 WHERE id 1; # 删除测试删除某个用户查看 account_change_log 表确认删除的记录是否正确。
DELETE FROM accounts WHERE id 2; 参考资料 MySQL 官方文档 - 触发器
