设为首页收藏本站
切换到窄版

鱼C论坛

 找回密码
 立即注册
鱼C论坛»论坛 › 编程语言专区 › 综合交流区 › 数据库 › 如何通过触发器实现更新数据表时将更新字段名称、字段原 ...
返回列表 发新帖
查看: 603|回复: 3

[已解决]如何通过触发器实现更新数据表时将更新字段名称、字段原值和现值保存在一张新表中

[复制链接]
zzong2019
发表于 2024-1-1 15:53:59 | 显示全部楼层 |阅读模式
5鱼币
尝试实现过程如下：
  1. create table employee_info_update(update_column varchar(20),
  2.                                   old_value varchar(20),
  3.                                   new_value varchar(20));
  4.                                                                                                                                        
  5. create trigger update_employee_info
  6. before update on employee_1 for each row
  7. insert into employee_info_update
  8. values(?,old.?,new.?,now());

  10. update employee_1 set department='业务拓展三部',age=33 where id='2000013'
  11. # 将id为2000013的员工所属机构调整为业务拓展三部，年龄修改为33岁

  13. select * from employee_info_update;
复制代码


目前存在的困难有以下几个方面：

1.因为不知道要更新哪些字段，因此在创建employee_info_update数据表时，对于old_value和new_value的数据类型无法定义；

2.一条update语句可能会更新一条记录中的多个字段，如何将多个字段的更新结果保存到employee_info_update数据表中；

3.如何编写SQL语句去判断更新的是哪几个字段？
最佳答案
月排行榜 / 总排行榜
FishC
2024-1-1 15:54:00
你遇到的困难都可以通过编写一些额外的代码来解决。让我们一步一步地来解决这些问题。

1. 首先，你可以将old_value和new_value的数据类型定义为JSON或者文本类型，以便能够保存任意类型的值。下面是修改后的表创建语句：

  1. CREATE TABLE employee_info_update (
  2.     update_column VARCHAR(20),
  3.     old_value TEXT,
  4.     new_value TEXT
  5. );
复制代码


2. 当一条UPDATE语句更新多个字段时，你需要在触发器中使用FOR EACH STATEMENT来处理整个更新语句，而不是每行触发。然后，你可以使用NEW和OLD关键字来获取旧值和新值，并使用IF条件判断每个字段是否有变化。以下是修改后的触发器创建语句：

  1. CREATE TRIGGER update_employee_info
  2.   AFTER UPDATE ON employee_1
  3.   FOR EACH STATEMENT
  4. BEGIN
  5.   IF NEW.department != OLD.department THEN
  6.     INSERT INTO employee_info_update (update_column, old_value, new_value)
  7.     VALUES ('department', OLD.department, NEW.department);
  8.   END IF;
  9.   
  10.   IF NEW.age != OLD.age THEN
  11.     INSERT INTO employee_info_update (update_column, old_value, new_value)
  12.     VALUES ('age', OLD.age, NEW.age);
  13.   END IF;
  14.   
  15.   -- 添加其他需要更新的字段的判断和插入语句
  16.   
  17. END;
复制代码


3. 为了判断更新了哪几个字段，你需要在触发器中逐个字段进行判断，并将更新的字段名称和值插入到新的表中。以上面的代码为例，我们判断了department和age这两个字段是否有变化，并将其更新结果保存到employee_info_update表中。

通过以上的修改，你可以实现在更新数据表时将更新字段名称、字段原值和现值保存在一张新表中。希望这些解决方案能对你有所帮助！如果还有其他问题，请随时提问。

以上回复来自 FishC AI 小助理，如未能正确解答您的问题，请继续追问。

最佳答案

FishC

查看完整内容

你遇到的困难都可以通过编写一些额外的代码来解决。让我们一步一步地来解决这些问题。 1. 首先，你可以将old_value和new_value的数据类型定义为JSON或者文本类型，以便能够保存任意类型的值。下面是修改后的表创建语句： 2. 当一条UPDATE语句更新多个字段时，你需要在触发器中使用FOR EACH STATEMENT来处理整个更新语句，而不是每行触发。然后，你可以使用NEW和OLD关键字来获取旧值和新值，并使用IF条件判断每个字段是否 ...
小甲鱼最新课程 -> https://ilovefishc.com
回复

使用道具 举报

FishC
发表于 2024-1-1 15:54:00 | 显示全部楼层    本楼为最佳答案   
你遇到的困难都可以通过编写一些额外的代码来解决。让我们一步一步地来解决这些问题。

1. 首先，你可以将old_value和new_value的数据类型定义为JSON或者文本类型，以便能够保存任意类型的值。下面是修改后的表创建语句：

  1. CREATE TABLE employee_info_update (
  2.     update_column VARCHAR(20),
  3.     old_value TEXT,
  4.     new_value TEXT
  5. );
复制代码


2. 当一条UPDATE语句更新多个字段时，你需要在触发器中使用FOR EACH STATEMENT来处理整个更新语句，而不是每行触发。然后，你可以使用NEW和OLD关键字来获取旧值和新值，并使用IF条件判断每个字段是否有变化。以下是修改后的触发器创建语句：

  1. CREATE TRIGGER update_employee_info
  2.   AFTER UPDATE ON employee_1
  3.   FOR EACH STATEMENT
  4. BEGIN
  5.   IF NEW.department != OLD.department THEN
  6.     INSERT INTO employee_info_update (update_column, old_value, new_value)
  7.     VALUES ('department', OLD.department, NEW.department);
  8.   END IF;
  9.   
  10.   IF NEW.age != OLD.age THEN
  11.     INSERT INTO employee_info_update (update_column, old_value, new_value)
  12.     VALUES ('age', OLD.age, NEW.age);
  13.   END IF;
  14.   
  15.   -- 添加其他需要更新的字段的判断和插入语句
  16.   
  17. END;
复制代码


3. 为了判断更新了哪几个字段，你需要在触发器中逐个字段进行判断，并将更新的字段名称和值插入到新的表中。以上面的代码为例，我们判断了department和age这两个字段是否有变化，并将其更新结果保存到employee_info_update表中。

通过以上的修改，你可以实现在更新数据表时将更新字段名称、字段原值和现值保存在一张新表中。希望这些解决方案能对你有所帮助！如果还有其他问题，请随时提问。

以上回复来自 FishC AI 小助理，如未能正确解答您的问题，请继续追问。
小甲鱼最新课程 -> https://ilovefishc.com
回复

使用道具 举报

zzong2019
 楼主| 发表于 2024-1-1 16:38:50 | 显示全部楼层
FishC 发表于 2024-1-1 15:54
你遇到的困难都可以通过编写一些额外的代码来解决。让我们一步一步地来解决这些问题。

1. 首先，你可以 ...

create table employee_info_update(id int primary key not null,
                                  update_column VARCHAR(20),
                                  old_value MEDIUMTEXT,
                                                                                                                                        new_value MEDIUMTEXT,
                                                                                                                                        update_time datetime);
                                                                                                                                       
# 创建一个名为employee_info_update的数据表，用来存放employee_1 被更新过的记录（具体到主键名称、哪个字段，新旧值对比），并记录更新时间
                                                                                                                                       
create trigger update_employee_info
after update on employee_1
for each row
begin
      if new.age!=old.age then
                         
         insert into employee_info_update
         values(new.id,'age',old.age,new.age,now());
                  end if;
                       
                  if new.department!=old.department then
                         
         insert into employee_info_update
         values(new.id,'department',old.department,new.department,now());
                  end if;
                       
                        if new.post!=old.post then
                         
         insert into employee_info_update
         values(new.id,'post',old.post,new.post,now());
                  end if;
                       
end;
# 创建一个名为update_employee_info的触发器，在每次有更新时触发（字段级），发生更新后比对所有可能被更新的字段，如果old表上的取值与new表取值不同则说明该字段被更新，执行insert操作去增加一条该记录是哪个字段被更新的操作，包括记录主键，更新字段名称，字段原值、字段新值、更新时间                 

select * from employee_1;

update employee_1 set department='业务拓展二部',age=30 where id='2000016'
# 将id为2000013的员工所属机构调整为业务拓展二部，年龄修改为30岁

按照您刚才的回复修改代码如上，但是运行到update employee_1 set department='业务拓展二部',age=30 where id='2000016'这一句的时候报错如下：Duplicate entry '2000016' for key 'employee_info_update.PRIMARY' 应如何解决？
小甲鱼最新课程 -> https://ilovefishc.com
回复

使用道具 举报

FishC
发表于 2024-1-1 16:39:17 | 显示全部楼层
zzong2019 发表于 2024-01-01 16:38
create table employee_info_update(id int primary k ...


这个错误是由于触发器中插入数据时主键重复引起的。由于每次更新都会触发触发器，可能会导致多次尝试插入同一条记录，从而引发主键冲突错误。

为了解决这个问题，你可以根据需要自行调整方案：

1. 如果你希望每次更新只保留最新的一次变化信息，可以将employee_info_update表的id字段定义为自增长主键，而不是引用employee_1表的主键。这样每次插入新的变化信息时，id都会自动增加，不会发生重复。

2. 如果你希望保留所有的变化历史记录，可以修改触发器逻辑，判断是否已经存在相同的记录再进行插入。以下是修改后的触发器代码：

  1. CREATE TRIGGER update_employee_info AFTER UPDATE ON employee_1 FOR EACH ROW
  2. BEGIN
  3.   IF NEW.age != OLD.age THEN
  4.     IF NOT EXISTS (SELECT * FROM employee_info_update WHERE id = NEW.id AND update_column = 'age') THEN
  5.       INSERT INTO employee_info_update VALUES (NEW.id, 'age', OLD.age, NEW.age, NOW());
  6.     END IF;
  7.   END IF;

  9.   IF NEW.department != OLD.department THEN
  10.     IF NOT EXISTS (SELECT * FROM employee_info_update WHERE id = NEW.id AND update_column = 'department') THEN
  11.       INSERT INTO employee_info_update VALUES (NEW.id, 'department', OLD.department, NEW.department, NOW());
  12.     END IF;
  13.   END IF;

  15.   IF NEW.post != OLD.post THEN
  16.     IF NOT EXISTS (SELECT * FROM employee_info_update WHERE id = NEW.id AND update_column = 'post') THEN
  17.       INSERT INTO employee_info_update VALUES (NEW.id, 'post', OLD.post, NEW.post, NOW());
  18.     END IF;
  19.   END IF;
  20. END;
复制代码


这样修改之后，触发器会在插入之前检查是否已经存在相同的记录，避免重复插入导致的主键冲突错误。

希望这个解决方案能帮到你！如果还有其他问题，请随时提问。

以上回复来自 FishC AI 小助理，如未能正确解答您的问题，请继续追问。
小甲鱼最新课程 -> https://ilovefishc.com
回复

使用道具 举报

返回列表 发新帖
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

小黑屋|手机版|Archiver|鱼C工作室 ( 粤ICP备18085999号-1 | 粤公网安备 44051102000585号)

GMT+8, 2025-4-27 16:55

Powered by Discuz! X3.4

© 2001-2023 Discuz! Team.

快速回复 返回顶部 返回列表