MySQL 5.7

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE account (
  `acct_id`  int,
  `acct_name` VARCHAR(6),
  `acct_balance` INTEGER,
  `tax_rate` FLOAT,
  `acct_opened` VARCHAR(10)
);

INSERT INTO account
  (`acct_id`, `acct_name`, `acct_balance`, `tax_rate`, `acct_opened`)
VALUES
  ('1', 'Bill', '100.00', '0.10', '2019-11-01'),
  ('2', 'Ben', '1000.00', '0.10', '2019-11-01');

Records: 2  Duplicates: 0  Warnings: 0


CREATE TABLE account_audit (
  `acct_id` int ,
  `txn_id` INTEGER,
  `acct_name` varchar(19),
  `acct_balance` DECIMAL(10,2),
  `tax_rate` INTEGER,
  `valid_from` DATE,
  `valid_to` DATE
);

INSERT INTO account_audit
  (`acct_id` , `txn_id`, `acct_name`, `acct_balance`, `tax_rate`, `valid_from`, `valid_to`)
VALUES
  ('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');

Records: 2  Duplicates: 0  Warnings: 0


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 (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;
UPDATE account 
SET acct_balance = acct_balance + 50 WHERE acct_name = 'Bill';

Rows matched: 1  Changed: 1  Warnings: 0


SELECT * FROM account_audit


acct_id
txn_id
acct_name
acct_balance
tax_rate
valid_from
valid_to


1
1
Bill
100.00
0
2021-11-07
2024-09-17

2
2
Ben
1000.00
0
2021-11-07
2038-01-19

1
null
Bill
150.00
0
null
null

acct_id	txn_id	acct_name	acct_balance	valid_from	valid_to
1	1	Bill	100.00	2021-11-07	2024-09-17
2	2	Ben	1000.00	2021-11-07	2038-01-19
1	null	Bill	150.00	null	null