0

I have a MySQL database where I have a table capturing all of the meter readings as and when they are done, with main columns:

  • Reading ID (Primary Key)
  • Date
  • Time
  • Last Reading
  • Final Reading

I am enquiring to see if the following is possible: whenever I insert a new record, the Final Reading from the previously inserted record carried over to the Last Reading field automatically, so as to minimize human error during data input.

Example (in order of fields above):

1, 30/05/2021, 21:45, 0.00, 1.23

2, 30/05/2021, 22:00, 1.23 <-- (Automatically inserted), 4.56

3, 30/05/2021, 22:15, 4.56 <-- (Automatically inserted), ...

I was reading about the LastInsertID() feature in MySQL, but I doubt if this will do the trick, or will it?

8
  • You should always specify the exact version of the DBMS you're working with. And why do you think storing the previous value is a good idea? You should never store in the database something that can be calculated at query time.
    – mustaccio
    May 30, 2021 at 18:18
  • What is the reason for to store the data which can be obtained easily during data retrieving? this is over-defined data which is considered bad practice.
    – Akina
    May 31, 2021 at 6:03
  • I'm confused -- Does the meter provide 2 readings every time you "read the meter"?
    – Rick James
    May 31, 2021 at 15:35
  • @mustaccio, think of it this way - I am on shift today, I am entering the last value as seen from the database table so as not to type in the last value myself so if this can be like the "default value" and then picked up from there and then when the database is closed (when I go off shift), the "final reading" will now be the "last value" for the next one who opens up the database for input... Jun 1, 2021 at 7:42
  • @Akina, do elaborate on the bad practice...? Jun 1, 2021 at 7:42

1 Answer 1

0

You can solve this by using a BEFORE INSERT TRIGGER

CREATE TABLE mytable (
    Reading_ID BIGINT AUTO_INCREMENT Primary Key
    ,`Date` date
    ,`Time` TIME
    ,Last_Reading Float
    ,Final_Reading Float
)
CREATE TRIGGER before_mytable_insert
BEFORE INSERT
ON mytable FOR EACH ROW
BEGIN
    DECLARE _lastvalue FLoat;
    
    SELECT Final_Reading
    INTO _lastvalue
    FROM mytable
    ORDER BY Reading_ID DESC
    LIMIT 1;
    
    IF _lastvalue IS NULL THEN
        SET NEW.Last_Reading = 0.0;
    ELSE
      SET NEW.Last_Reading = _lastvalue;
    END IF; 

END
INSERT INTO mytable (`Date`,`Time`,Final_Reading) VALUES (Current_dATe(),CURRENT_TIME(),1.25)
INSERT INTO mytable (`Date`,`Time`,Final_Reading) VALUES (Current_dATe(),CURRENT_TIME(),4.46)
SELECT * FROM mytable
Reading_ID | Date       | Time     | Last_Reading | Final_Reading
---------: | :--------- | :------- | -----------: | ------------:
         1 | 2021-05-30 | 19:12:07 |            0 |          1.25
         2 | 2021-05-30 | 19:12:07 |         1.25 |          4.46

db<>fiddle here

4
  • Dear nbk, Thank You for your response of which I will try and revert back later. With Kind Regards, Idris May 31, 2021 at 4:16
  • I tried this but it did not work but I notice if I use SESSION keys for before adding the record, this can work whilst the front-end app/browser is still open.. Jun 1, 2021 at 7:45
  • this works on server side, so everytime you enter a new value the trigger autmatucally adds he value, session variable are on he side of the webserver and have nothing to do driectly with the database. maybe you should think about the concept and write it as question down, buz dba will not be the place. so completely i can't follow your argumentation
    – nbk
    Jun 1, 2021 at 7:50
  • I understand you and shall look into it further - Thank You. Jun 1, 2021 at 10:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.