问题描述
我有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.
这篇关于在执行触发器后插入值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!