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.
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));