MariaDB trigger to perform an UPDATE and then an INSERT on the same table

I currently have an account table and an account_audit table (all codes are available on a fiddle here).

I have two triggers that insert records into the account_audit table based on data inserted into the account table.

1 trigger is an ON INSERT trigger which just inserts these values – that works fine.

So, after inserting 2 records on my audit table – I have two records in both tables as below.

SELECT * FROM account;

gives

acct_id acct_name   acct_balance    tax_rate    acct_opened
      1 Bill            100.00          0.10     2019-11-01
      2 Ben            1000.00          0.10     2019-11-01

and

SELECT * FROM account_audit;

gives

acct_id txn_id  acct_name   acct_balance    tax_rate    valid_from  valid_to
      1      1       Bill         100.00        0.10    2021-11-07  2038-01-19
      2      2        Ben        1000.00        0.10    2021-11-07  2038-01-19

All is good – 2038 is MariaDB infinity – at least in temporal tables!

But, my UPDATE trigger is as follows:

CREATE TRIGGER testtrigger_upd 
AFTER UPDATE ON account
  FOR EACH ROW BEGIN
    UPDATE account_audit SET valid_to = NOW() 
    WHERE ((valid_to = '2038-01-19') AND (OLD.acct_name = NEW.acct_name));
    INSERT INTO 
      account_audit (acct_id, acct_name, acct_balance, tax_rate) 
      VALUES 
      (
        OLD.acct_id, 
        OLD.acct_name, 
        NEW.acct_balance, 
        OLD.tax_rate
        
        -- valid_from - NOW()!
        -- valid_to DEFAULTs to '2038-01-19' which is what we want for updates to balance 
      );
END;

So, when I UPDATE a balance on an account, I want the account_audit trail to reflect this and then to have the most recent (before the insert) balance record go to valid_to (today) and the new records valid_from today to valid_to in 2038 (infinity for MariaDB?).

The problem is that when I run this statement

UPDATE account 
SET acct_balance = acct_balance + 50 WHERE acct_name = 'Bill';  -- name is UNIQUE

the records in the account_audit table become:

acct_id txn_id  acct_name   acct_balance    tax_rate    valid_from  valid_to
      1      1       Bill         100.00    0.10        2021-11-07  2021-11-07
      1      3       Bill         150.00    0.10        2021-11-07  2038-01-19
      2      2        Ben        1000.00    0.10        2021-11-07  2021-11-07

You can see that Bill’s record has been inserted but the problem is that Ben’s record has also been updated with the date of today’s insert – I only want this to happen for Bill obviously!

I have WHERE ((valid_to = '2038-01-19') AND (OLD.acct_name = NEW.acct_name)); in my trigger and I have tried lots of others – WHERE OLD.acct_id = NEW.acct_id and I tried changing the UPDATE to use acct_id instead of name – nothing works. I tries swapping OLD. and NEW. order – try BEFORE or AFTER UPDATE – I really lose my head!

How can I rewrite trigger to update the account_audit table for only the person whose account it is and not for all accounts – that is to say, only update Bill’s account and not Ben’s one?

I know about the temporal table functionality – I don’t want to use that because this is for a study and I have to use the triggers!

Fiddle is here.

Answer

It seems to me that the where clause

WHERE ((valid_to = '2038-01-19') AND (OLD.acct_name = NEW.acct_name));

is valid for both Ben and Bill. So it would update for both of them.

A solution could be to change it slightly to:

WHERE ((valid_to = '2038-01-19') AND (acct_name = OLD.acct_name));