如何从 mysql 最后修改的列名称

标签: MySQL
发布时间: 2015/6/12 0:50:35
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

它是可以从记录获取特定更新的列的名称吗?

如果是这样,请帮助我。因为我创建了一个历史表来存储已更新的列和它们的值从表的其余部分。

DROP TRIGGER IF EXISTS testing.hisupdate;


CREATE TRIGGER testing.hisupdate AFTER
UPDATE ON testing.emp
FOR EACH ROW
INSERT INTO testing.history (`id`,`revision_id`,`trx_code`, `trx_method`, `column_name`, `old_value`, `new_value`, `trx_timestamp`, `user_id`)
SELECT
  (SELECT emp_id
   FROM testing.emp
   ORDER BY last_update DESC LIMIT 1),
  (SELECT p.revision_id
   FROM testing.history AS p
   WHERE p.id=
       (SELECT emp_id
        FROM testing.emp
        ORDER BY last_update DESC LIMIT 1)
   ORDER BY trx_timestamp DESC LIMIT 1) + 1, 'updt', 'update','null',
  (SELECT p.new_value
   FROM testing.history AS p
   WHERE p.id=
       (SELECT emp_id
        FROM testing.emp
        ORDER BY last_update DESC LIMIT 1)
   ORDER BY trx_timestamp DESC LIMIT 1), concat(p.emp_id,"/",p.emp_name,"/",p.salary,"/") AS new_value,
                                         NOW(),
                                         '101'
FROM testing.emp AS p
WHERE p.emp_id = new.emp_id;

我已经创建触发器,但寻找最后更新的列名称的子查询。 在此先感谢。

解决方法 1:

请尝试此代码...

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);

INSERT INTO sales (orderno,sale,empsalary) VALUES(2,800,8000);

INSERT INTO sales (orderno,sale,empsalary) VALUES(3,900,9000);

DROP TRIGGER test.instrigger;

分隔符 / /

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;

///

分隔符;

上一篇
赞助商