在执行触发器后插入值 [英] Insert Value After Execute Trigger

问题描述

我有2个具有这种结构的表:

I have 2 tables with this structure:

ID | Revision   | Purpose
-------------------------
1  |     A      |   3

表1是我保存信息的地方，表2在更新表1之后填充了触发器.这是触发器:

Table 1 is where I save the information, table 2 is filled with a trigger after update table 1. Here is the trigger:

TRIGGER `update` AFTER UPDATE ON `table 1` 
FOR EACH ROW INSERT INTO table 2 (
Id,
Revision,
Purpose,
)
VALUES
(
OLD.Id,
OLD.Revision,
OLD.Purpose,
);
$$
DELIMITER ;

一切都很好，但是现在我想将表2的结构更改为:

Everything its fine, but now I want to change the structure of table 2 to this:

ID | Revision   | Purpose |  Change
-----------------------------------
1  |     A      |   1     |  Purpose change to 1

我希望用户可以编写表1的更改说明，然后将其保存到表2.

I want that the user can write a description of what changes from table 1 and then save it to table 2.

我尝试创建该字段，然后像往常一样进行更新:

I try creating the field and then do the update as always:

Update Table 1 ...... where Id = Id

这可行，但是表2可以有很多具有相同ID的行，因此这不是一个选择.有解决办法吗?

And this works but Table 2 can have a lot of rows with the same Id, so this is not an option. Any solution?

我也尝试在表1中创建该字段，但这意味着我必须更改所有代码，但我只想要表2中的该字段.

Also I try creation the field in table 1 too, but this meant that I have to change all my code but I only want this field in table 2.

推荐答案

您可以使用MySQL变量:

You can use a MySQL variable:

1:在更新表1 之前，设置用户变量 @purpose_change :

1: Before to update the table 1, set a user variable @purpose_change:

 set @purpose_change = 'Purpose change to 1';

2:另外，请按以下步骤更改触发器:

2: Also, change you trigger as follows:

CREATE TRIGGER `update` AFTER UPDATE ON `table 1` 
FOR EACH ROW INSERT INTO table 2 (
Id,
Revision,
Purpose,
Change
)
VALUES
(
OLD.Id,
OLD.Revision,
OLD.Purpose,
@purpose_change  /* user variable */
);
$$
DELIMITER ;

@purpose_change 将存在于同一连接中.

The @purpose_change will exists in the same connection.

这篇关于在执行触发器后插入值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！