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.

CodePudding user response：

That is because your your where clause is for all rows true

So change the update query to check against the table

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';

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 | 2021-11-07
      2 |      2 | Ben       |      1000.00 |        0 | 2021-11-07 | 2038-01-19
      1 |   null | Bill      |       150.00 |        0 | null       | null      

db<>fiddle here

CodePudding user response：

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