How to monitor speicific fields for change and timestamp.

[fatkatie]

I'm on a new path here and would appreciate any ideas you pros might have.  I think this involves triggers and another table but am not sure.

I have a project record that has fields that contain information.  It's important to know when some fields change.   For example, I have a field call DRAWING which contains a link to a project print.  Values in this link change whenever the print is modified.  It's important to know the date of the last change.

When the project page is opened for review, I want to show, to the right of this field (and a few others), the date/time is was last changed.   I want the time information field based, not record based.

My initial idea is to trigger after a record update and compare an 'identical' table against the project table.  After checking the fields of interest for change I would write the project table associated lastupdate fields with a date and then overwrite this 'identical' table with the new state of things.

This all could be done with code but it gets a bit onerous.  I was hoping to do it once in a stored procedure.

Comments?  Thank you.

10.1.38-MariaDB

[requinix]

Are there other applications writing to the database besides your own? Do you have any control over them?

[fatkatie]

Only the project managers have access to their pages (each pm owns his 'record(s)' and no other may edit)  The traffic is low.  It is unlikely two would hit the table at the same time.   It's impossible that two would hit the same record at the same time. The application is web based - login - no table locks are used.  This table is read by many other pages/reports - not modified.

 

Thank you.

[Barand]

Does this fit your requirements?

The test_project has two fields for which changes are to be recorded (monitorA and monitorB). Two log tables are maintained, one for each of the columns. When a value changes, the original and new values are logged along with the datetime of the change, so you have record of the changes and when.

CREATE TABLE `test_project` (
  `test_project_id` int(11) NOT NULL AUTO_INCREMENT,
  `proj_name` varchar(45) DEFAULT NULL,
  `monitorA` varchar(45) DEFAULT NULL,
  `monitorB` varchar(45) DEFAULT NULL,
  `creation_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`test_project_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

CREATE TABLE `test_loga` (
  `test_logA_id` int(11) NOT NULL AUTO_INCREMENT,
  `proj_id` int(11) DEFAULT NULL,
  `old_a` varchar(45) DEFAULT NULL,
  `new_a` varchar(45) DEFAULT NULL,
  `change_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`test_logA_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

CREATE TABLE `test_logb` (
  `test_logb_id` int(11) NOT NULL AUTO_INCREMENT,
  `proj_id` int(11) DEFAULT NULL,
  `old_b` varchar(45) DEFAULT NULL,
  `new_b` varchar(45) DEFAULT NULL,
  `change_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`test_logb_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

Trigger...

CREATE TRIGGER `test`.`test_project_AFTER_UPDATE` AFTER UPDATE ON `test_project` FOR EACH ROW
BEGIN
    IF (old.monitorA != new.monitorA) THEN
        INSERT INTO test_loga (proj_id, old_a, new_a) VALUES (new.test_project_id, old.monitorA, new.monitorA);
    END IF;
    IF (old.monitorB != new.monitorB) THEN
        INSERT INTO test_logb (proj_id, old_b, new_b) VALUES (new.test_project_id, old.monitorB, new.monitorB);
    END IF;
END
  

Insert a project record then update monitorA twice then monitorB once

INSERT INTO test_project (proj_name, monitorA, monitorB) VALUES
('Project One', 'AAA', 'BBB');

UPDATE test_project SET monitorA = 'AA2' WHERE test_project_id = 1;
UPDATE test_project SET monitorA = 'AA3' WHERE test_project_id = 1;
UPDATE test_project SET monitorB = 'BB4' WHERE test_project_id = 1;

Log tables...

mysql> select * from test_loga;
+--------------+---------+-------+-------+---------------------+
| test_logA_id | proj_id | old_a | new_a | change_date         |
+--------------+---------+-------+-------+---------------------+
|            1 |       1 | AAA   | AA2   | 2019-06-14 20:13:44 |
|            2 |       1 | AA2   | AA3   | 2019-06-14 20:14:34 |
+--------------+---------+-------+-------+---------------------+
2 rows in set (0.00 sec)

mysql> select * from test_logb;
+--------------+---------+-------+-------+---------------------+
| test_logb_id | proj_id | old_b | new_b | change_date         |
+--------------+---------+-------+-------+---------------------+
|            1 |       1 | BBB   | BB4   | 2019-06-14 20:54:05 |
+--------------+---------+-------+-------+---------------------+
1 row in set (0.00 sec)

For reports, subqueries find the latest dates for each project in the log files. If no changes, the COALESCE will show the project creation date as the latest date.

SELECT test_project_id
     , proj_name
     , monitorA
     , COALESCE(latesta, creation_date) as latest_A
     , monitorB
     , COALESCE(latestb, creation_date) as latest_B
FROM test_project p 
     LEFT JOIN (
                SELECT proj_id
                     , MAX(change_date) as latesta
                FROM test_loga
                GROUP BY proj_id
               ) a ON p.test_project_id = a.proj_id
     LEFT JOIN (
                SELECT proj_id
                     , MAX(change_date) as latestb
                FROM test_logb
                GROUP BY proj_id
               ) b ON p.test_project_id = b.proj_id
+-----------------+-------------+----------+---------------------+----------+---------------------+
| test_project_id | proj_name   | monitorA | latest_A            | monitorB | latest_B            |
+-----------------+-------------+----------+---------------------+----------+---------------------+
|               1 | Project One | AA3      | 2019-06-14 20:14:34 | BB4      | 2019-06-14 20:54:05 |
|               2 | Project Two | CCC      | 2019-06-14 22:01:30 | DDD      | 2019-06-14 22:01:30 |
+-----------------+-------------+----------+---------------------+----------+---------------------+  

 

Edited June 14, 2019 by Barand

[fatkatie]

So ... Barand... been there done that I see.  Thanks.

I'll digest this and let you know.  Looks good thou at first glance.

[fatkatie]

Being a trigger virgin the new. and old. references confused me, but now I'm off.  The code is good enough to almost use verbatim.  Thanks for your 'comment'.  Thanks again.

[Barand]

MySql manual - triggers

Quote

Within the trigger body, the OLD and NEW keywords enable you to access columns in the rows affected by a trigger. OLD and NEW are MySQL extensions to triggers; they are not case-sensitive.

In an INSERT trigger, only NEW.col_name can be used; there is no old row. In a DELETE trigger, only OLD.col_name can be used; there is no new row. In an UPDATE trigger, you can use OLD.col_name to refer to the columns of a row before it is updated and NEW.col_name to refer to the columns of the row after it is updated.

A column named with OLD is read only. You can refer to it (if you have the SELECT privilege), but not modify it. You can refer to a column named with NEW if you have the SELECT privilege for it. In a BEFORE trigger, you can also change its value with SET NEW.col_name = value if you have the UPDATE privilege for it. This means you can use a trigger to modify the values to be inserted into a new row or used to update a row. (Such a SET statement has no effect in an AFTER trigger because the row change will have already occurred.)

In a BEFORE trigger, the NEW value for an AUTO_INCREMENT column is 0, not the sequence number that is generated automatically when the new row actually is inserted.