在MySQL中保存使用触发器删除记录的用户的USER_ID [英] Saving the USER_ID of a user that deleted a record with a trigger in MySQL

问题描述

我正在尝试设置一系列历史记录触发器，以通过触发器自动收集给定表的历史记录.我想使用触发器，因为这可以确保我捕获所有更改，即使有人忘记将其保存在应用程序中也是如此.我的问题是我有这个触发器.

I'm trying to set up a series of history triggers to automatically collect history for a given table via triggers. I want to use triggers as this guarantees I capture all changes regardless if someone forgets about saving it in the app. My problem is that I have this trigger.

CREATE TRIGGER `db`.`delete_history_trigger` BEFORE DELETE ON `db`.`payments`
    FOR EACH ROW BEGIN
          INSERT INTO `payments_history` select *, 'delete', NOW(), USER() from `payments` where `PAYMENT_ID` = OLD.`PAYMENT_ID`;
    END
//

这会将 payments 中的所有内容填充到 payments_history 中，并填写已执行的操作，操作发生的日期/时间，导致该操作被执行的MySQL用户.执行.我的应用程序中的每个用户都有一个要保存的 USER_ID (来自 users 表).对于更新，这没有问题，因为 USER_ID 是 update 查询中使用的一个字段.但是，在删除操作中，没有数据传入，因此无法访问NEW，因为那里什么也没有.我想不出一种好方法来使 USER_ID 在删除时保存到历史记录表中，而无需执行第二次查询来更新记录.我宁愿不使用此解决方案，因为其他人可能会忘记删除该方法以更新记录，而该数据可能会丢失.

This stuff everything in payments into payments_history and fills out the action that was performed, the date/time the action occurred, the MySQL user that caused the action to be performed. Each user in my app has a USER_ID (which is from the users table) that I want to save. For updates this is no problem as USER_ID is one fields used in the update query. However, in delete there is no data passed in and therefore NEW is not accessible as there is nothing there. I can't think of a good way to get the USER_ID to save into the history table on deletes short of doing a second query to update the record. I'd rather not use this solution as it is possible that someone else may forget to call the method to update the record after a delete and that data would be lost.

推荐答案

如果user_id会话变量中没有user_id值，则将会话变量设置为user_id并让触发器中止删除操作.

Set a session variable to the user_id and have the trigger abort the delete if the user_id session variable does not have a value user_id in it.

查询以在MySQL会话中设置user_id:

Query to set user_id in MySQL session:

SET @user_id = <value of user_id>

新删除:

CREATE TRIGGER `db`.`delete_history_trigger` BEFORE DELETE ON `db`.`payments`
   FOR EACH ROW BEGIN
      INSERT INTO `payments_history` select *, 'delete', NOW(), USER(), @user_id from `payments` where `PAYMENT_ID` = OLD.`PAYMENT_ID`;
   END
//

这篇关于在MySQL中保存使用触发器删除记录的用户的USER_ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！