MySQL触发表中的更改

I want to log any UPDATE on any table using mysql, but within the log I want to record the 'old' value and the 'new' one.

For instance I have this table:

例如,我有这个表:

user:

+-------------------+
| id                |
| email             |
| password          |
| birthdate         |
+-------------------+

values:

1:'test@test.com':'123456':'1995-07-23'

And I decide to change the birthday for id=1 to '1996-07-23'

我决定将id = 1的生日改为'1996-07-23'

So, I would like to log into 'history' table the following:

所以,我想登录“历史”表格如下:

table_name: 'user', field_name: 'birthdate', previous_value: '1995-07-23', new_value: '1996-07-23'

table_name:'user',field_name:'birthdate',previous_value:'1995-07-23',new_value:'1996-07-23'

history table:

+-------------------+
| id                |
| table_name        |
| field_name        |
| previous_value    |
| new_value         |
| history_datetime  |
+-------------------+

Any advise to achieve that ?

有任何建议去实现吗?

1 个解决方案

#1

You can try this

你可以试试这个

CREATE TRIGGER user_update
AFTER UPDATE ON user
FOR EACH ROW
  INSERT INTO history (obj_id, table_name, field_name, previous_value, new_value, history_datetime)
  SELECT NEW.id, 'user', 'email', OLD.email, NEW.email, NOW()
    FROM user
   WHERE IFNULL(NEW.email, '') <> IFNULL(OLD.email, '')
  UNION ALL
  SELECT NEW.id, 'user', 'birthdate', OLD.birthdate, NEW.birthdate, NOW()
    FROM user
   WHERE IFNULL(NEW.birthdate,'') <> IFNULL(OLD.birthdate, '');

It one-statement trigger

它是一个语句触发器

It logs changes only if new value differs from old value.

仅当新值与旧值不同时,它才会记录更改。

It takes care of NULLs.

它负责NULL。

Here is SQLFiddle demo

这是SQLFiddle演示

Note: id column in history table made auto_increment, and additional obj_id column has been introduced to reference an id of an object being logged

注意:历史表中的id列使auto_increment成为可能,并引入了额外的obj_id列来引用正在记录的对象的id

#1