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

zzong2019

尝试实现过程如下：

create table employee_info_update(update_column varchar(20),
                                  old_value varchar(20),
                                  new_value varchar(20));
                                                                                                                                        
create trigger update_employee_info 
before update on employee_1 for each row 
insert into employee_info_update 
values(?,old.?,new.?,now());

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

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或者文本类型，以便能够保存任意类型的值。下面是修改后的表创建语句：

CREATE TABLE employee_info_update (
    update_column VARCHAR(20),
    old_value TEXT,
    new_value TEXT
);

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

CREATE TRIGGER update_employee_info
  AFTER UPDATE ON employee_1
  FOR EACH STATEMENT
BEGIN
  IF NEW.department != OLD.department THEN
    INSERT INTO employee_info_update (update_column, old_value, new_value)
    VALUES ('department', OLD.department, NEW.department);
  END IF;
  
  IF NEW.age != OLD.age THEN
    INSERT INTO employee_info_update (update_column, old_value, new_value)
    VALUES ('age', OLD.age, NEW.age);
  END IF;
  
  -- 添加其他需要更新的字段的判断和插入语句
  
END;

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

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

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

跳转到最佳答案楼层

FishC

你遇到的困难都可以通过编写一些额外的代码来解决。让我们一步一步地来解决这些问题。

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

CREATE TABLE employee_info_update (
    update_column VARCHAR(20),
    old_value TEXT,
    new_value TEXT
);

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

CREATE TRIGGER update_employee_info
  AFTER UPDATE ON employee_1
  FOR EACH STATEMENT
BEGIN
  IF NEW.department != OLD.department THEN
    INSERT INTO employee_info_update (update_column, old_value, new_value)
    VALUES ('department', OLD.department, NEW.department);
  END IF;
  
  IF NEW.age != OLD.age THEN
    INSERT INTO employee_info_update (update_column, old_value, new_value)
    VALUES ('age', OLD.age, NEW.age);
  END IF;
  
  -- 添加其他需要更新的字段的判断和插入语句
  
END;

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

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

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

zzong2019

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' 应如何解决？

FishC

zzong2019 发表于 2024-01-01 16:38
create table employee_info_update(id int primary k ...

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

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

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

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

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

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

  IF NEW.post != OLD.post THEN
    IF NOT EXISTS (SELECT * FROM employee_info_update WHERE id = NEW.id AND update_column = 'post') THEN
      INSERT INTO employee_info_update VALUES (NEW.id, 'post', OLD.post, NEW.post, NOW());
    END IF;
  END IF;
END;

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

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

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