0

My first question on stackoverflow:

I am working with a MYSQL-database

I have a table that I want to track the changes for. My specific goal is to get the name of the updated column(s) into the history table that tracks the original table.

SO far I have made a history table the table accountuser that has 4 extra columns for auditing. I want to write the name of the updated column in the column updatedcolumn, any help on how to achieve this? In below query this is the where I need help: [Here I want the name of the updated column]

CREATE TABLE hefboom.accountuser_history LIKE hefboom.accountuser;

ALTER TABLE hefboom.accountuser_history MODIFY COLUMN Id int(11) NOT NULL, 
   DROP PRIMARY KEY, ENGINE = MyISAM, ADD action VARCHAR(8) DEFAULT 'insert' FIRST, 
   ADD revision INT(6) NOT NULL AUTO_INCREMENT AFTER action,
   ADD dt_datetime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER revision,
   ADD updatedcolumn VARCHAR (50) DEFAULT NULL AFTER dt_datetime,
   ADD PRIMARY KEY (Id, revision);


DROP TRIGGER IF EXISTS hefboom.accountuser__ai;
DROP TRIGGER IF EXISTS hefboom.accountuser__au;
DROP TRIGGER IF EXISTS hefboom.accountuser__bd;

CREATE TRIGGER hefboom.accountuser__ai AFTER INSERT ON hefboom.accountuser    
FOR EACH ROW
INSERT INTO hefboom.accountuser_history SELECT 'insert', NULL, NOW(), [updatedcolumns can be left NULL]
FROM hefboom.accountuser AS d WHERE d.Id = NEW.Id;

CREATE TRIGGER hefboom.accountuser__au AFTER UPDATE ON hefboom.accountuser FOR EACH ROW
INSERT INTO hefboom.accountuser_history SELECT 'update', NULL, NOW(), [updatedcolumns can be left NULL]
FROM hefboom.accountuser AS d WHERE d.Id = NEW.Id;

CREATE TRIGGER hefboom.accountuser__bd BEFORE DELETE ON hefboom.accountuser FOR EACH ROW
INSERT INTO hefboom.accountuser_history SELECT 'delete', NULL, NOW(), [Here I want the name of the updated column]
FROM hefboom.accountuser AS d WHERE d.Id = OLD.Id;

My second question would be if more then one row would be added to the history table if more then one column is updated within one transaction?

Any help is appreciated.

zlatan uzman
  • 1
  • 2nd question: no, trigger operates on a row basis, not on column basis (`for each row` should have been a giveaway). You either have to log on a row basis, or repeat the insert as many times as the number of fields changed. Honestly, I would log on a row basis because it is easier to determine what the entire row was at any point of time. – Shadow Oct 27 '16 at 12:24
  • OK thanks maybe it would be better if I then for each row only insert the updated values in the history table and leave the other columns empty. Can this be achieved? In that case I would not need the column updatedcolumn in the history table. – zlatan uzman Oct 27 '16 at 12:40

0 Answers0