获取当前更新的列名称以在触发器中使用

发布于 2024-09-05 04:13:05 字数 501 浏览 1 评论 0原文

有没有办法实际获取已更新的列名称以便在触发器中使用它？

基本上，每当用户插入或更新表时，我都会尝试进行审计跟踪（在本例中，它与联系人表有关）

CREATE TRIGGER `after_update_contact`
    AFTER UPDATE ON `contact` FOR EACH ROW
    BEGIN
        INSERT INTO user_audit (id_user, even_date, table_name, record_id, field_name, old_value, new_value)
        VALUES (NEW.updatedby, NEW.lastUpdate, 'contact', NEW.id_contact, [...])
    END

如何获取已更新的列的名称，并从中获取OLD 和 NEW 值。如果连续更新多列甚至多行，是否可以对每次更新进行审核？

原文

Is there a way to actually get the column name that was updated in order to use it in a trigger?

Basically I'm trying to have an audit trail whenever a user inserts or updates a table (in this case it has to do with a Contact table)

CREATE TRIGGER `after_update_contact`
    AFTER UPDATE ON `contact` FOR EACH ROW
    BEGIN
        INSERT INTO user_audit (id_user, even_date, table_name, record_id, field_name, old_value, new_value)
        VALUES (NEW.updatedby, NEW.lastUpdate, 'contact', NEW.id_contact, [...])
    END

How can I get the name of the column that's been updated and from that get the OLD and NEW values of that column. If multiple columns have been updated in a row or even multiple rows would it be possible to have an audit for each update?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

客…行舟 2024-09-12 04:13:05

只需使用 OLD.colname <>每列的 NEW.colname 来检查并查找不同的列。不幸的是，触发器在 MySQL 中的使用有点受限。

回复收藏 0 原文

缪败 2024-09-12 04:13:05

试试这个代码...

create table sales (
  orderno INT, 
  sale INT,
  empsalary int,
  ts TIMESTAMP
);

create table history (
  updated varchar(20),
  oldvalue INT,
  newvalue INT
);

INSERT INTO sales 
  (orderno, sale, empsalary) 
VALUES
  (1,700,7000),
  (2,800,8000),
  (3,900,9000);

DROP TRIGGER test.instrigger;

DELIMITER ///

CREATE TRIGGER test.instrigger
AFTER UPDATE ON sales
FOR EACH ROW

BEGIN

    IF NEW.sale <> OLD.sale THEN  
        INSERT INTO history
          (updated, oldvalue, newvalue) 
        VALUES
          ('sale', OLD.sale,NEW.sale);
    END IF;
  
    IF NEW.empsalary <> OLD.empsalary THEN  
        INSERT INTO history
          (updated, oldvalue, newvalue)
        VALUES
          ('empsalary', OLD.empsalary,NEW.empsalary);
    END IF;
END;
///

DELIMITER ;

Try this code...

create table sales (
  orderno INT, 
  sale INT,
  empsalary int,
  ts TIMESTAMP
);

create table history (
  updated varchar(20),
  oldvalue INT,
  newvalue INT
);

INSERT INTO sales 
  (orderno, sale, empsalary) 
VALUES
  (1,700,7000),
  (2,800,8000),
  (3,900,9000);

DROP TRIGGER test.instrigger;

DELIMITER ///

CREATE TRIGGER test.instrigger
AFTER UPDATE ON sales
FOR EACH ROW

BEGIN

    IF NEW.sale <> OLD.sale THEN  
        INSERT INTO history
          (updated, oldvalue, newvalue) 
        VALUES
          ('sale', OLD.sale,NEW.sale);
    END IF;
  
    IF NEW.empsalary <> OLD.empsalary THEN  
        INSERT INTO history
          (updated, oldvalue, newvalue)
        VALUES
          ('empsalary', OLD.empsalary,NEW.empsalary);
    END IF;
END;
///

DELIMITER ;

回复收藏 0 原文

~没有更多了~