Skip to content
Advertisement

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.

gives

and

gives

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

But, my UPDATE trigger is as follows:

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

the records in the account_audit table become:

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.

Advertisement

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

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement