CODEWITHSUNDEEP
mysqltriggers
Othman
Othman

Reputation: 3018

Identifying the edited columns in the database

At the beginning the application have an admins and employees and records.

The records table has many columns than can be changed by any employee. However, this change can not be submitted. When the admin approve for an edit the record will show up again in the system.

I was trying to identify the column name and the value, and send it to another table using triggers on UPDATE.

So when the employee edits any record, the record will be disabled in the system. Also the admin will be able to know which values has been changed.

Is this possible in databases ? 

Records Table
-------------------------------------------------------------------
record_id       record_name     record_serial       record_active
-------------------------------------------------------------------
    1            something          5151                 YES

When an update happens to the record_serial, such as from 5151 to 9844 I need to do this.

Records_changes
-------------------------------------------------------------------
change_id          record_col       record_old_val      record_new_val
-------------------------------------------------------------------
    1            record_serial          5151                 9844

At the same time 

-------------------------------------------------------------------
record_id       record_name     record_serial       record_active
-------------------------------------------------------------------
    1            something          9844                 NO

I can do it using my application, but if there is anyway to do it using the database it would be much nicer.

I will use this to track the changes, and also create a history for the records old values.

I am using MySQL

Upvotes: 1

Views: 57

Answers (2)

peterm
peterm

Reputation: 92795

You can do something like this

DELIMITER $$
CREATE TRIGGER tg_bu_records
BEFORE UPDATE ON records
FOR EACH ROW
BEGIN
  IF NOT (OLD.record_serial <=> NEW.record_serial AND
          OLD.record_name <=> NEW.record_name) THEN
    SET NEW.record_active = 0;
  END IF;
END$$

CREATE TRIGGER tg_au_records
AFTER UPDATE ON records
FOR EACH ROW
BEGIN
  IF NOT (OLD.record_serial <=> NEW.record_serial) THEN
    INSERT INTO records_changes (record_col, record_old_val, record_new_val)
    VALUES ('record_serial', OLD.record_serial, NEW.record_serial);
  END IF;
  IF NOT (OLD.record_name <=> NEW.record_name) THEN
    INSERT INTO records_changes (record_col, record_old_val, record_new_val)
    VALUES ('record_name', OLD.record_name, NEW.record_name);
  END IF;
END$$
DELIMITER ;

Note: The trick is to change record_active flag in BEFORE trigger because it's the only event when you can change values of a row being updated/inserted in a table on which you defined that trigger. Now in AFTER trigger we record the changes that have been made.

Here is SQLFiddle demo

Upvotes: 1

brak2718
brak2718

Reputation: 84

A good database pattern for this, if I understand your problem correctly, is to use versioned rows. You add columns (or additional referenced tables) with meta data about when/who/what the edit was about. When a record is edited, a new row is inserted. Previous versions are never modified or deleted. Then it's up to your application logic to decide what to do with all of this.

Upvotes: 0

Related Questions