MariaDB 10.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version()


10.6.9-MariaDB-1:10.6.9+maria~deb11


CREATE TABLE account
(
  acct_id      INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  acct_name    VARCHAR (255) NOT NULL,
  acct_balance DECIMAL (15, 2),

  tax_rate     DECIMAL(3,2),  

  acct_opened  DATE DEFAULT DATE(NOW()),
  
  CONSTRAINT bal_positive_ck CHECK (acct_balance >= 0),

  CONSTRAINT acct_name_uq UNIQUE (acct_name)  -- required for FK definition in account_audit tabke  
);
CREATE TABLE account_audit
(
  acct_id      INTEGER,
  txn_id       INTEGER NOT NULL AUTO_INCREMENT,
  acct_name    VARCHAR (255) NOT NULL,  -- name not required - is determined by the account "master" table
                                        -- left in to aid legibility! 
  acct_balance DECIMAL (15, 2),

  tax_rate     DECIMAL(3,2),  

  -- acct_opened  No need for this field - contained in account "master" table

  valid_from   DATE DEFAULT NOW() NOT NULL,  
  
  valid_to     DATE DEFAULT '2038-01-19' NOT NULL,
  
  CONSTRAINT aa_acct_txn_pk PRIMARY KEY (acct_id, txn_id),
  
  KEY(txn_id), -- this is required by MariaDB!
  
  
  CONSTRAINT a_a_audit_id_fk   FOREIGN KEY (acct_id)   REFERENCES account (acct_id),
  CONSTRAINT a_a_audit_name_fk FOREIGN KEY (acct_name) REFERENCES account (acct_name),

  CONSTRAINT from_lt_to_ck CHECK (valid_from <= valid_to),

  CONSTRAINT bal_positive_ck CHECK (acct_balance >= 0)
  
  --
  -- could have more checks in real life - omitted for demonstration purposes
  --
  
);
CREATE TRIGGER testtrigger_ins 
AFTER INSERT ON account
  FOR EACH ROW BEGIN
    INSERT INTO 
      account_audit (acct_id, acct_name, acct_balance, tax_rate)
      VALUES 
      (
        NEW.acct_id, 
        NEW.acct_name, 
        NEW.acct_balance, 
        NEW.tax_rate

        -- valid_from defaults to NOW() 
        -- valid_to defaults to '2038-01-19' for new accounts

      );
END;
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_id = NEW.acct_id));
    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;
-- 
-- CREATE some accounts with an old acct_opened date! In the audit table, valid_to
-- is 2038 which is fine!
--

INSERT INTO account (acct_name, acct_balance, tax_rate, acct_opened) VALUES 
('Bill',  100.00, 0.1, '2019-11-01'), ('Ben' , 1000.00, 0.1, '2019-11-01');

Records: 2  Duplicates: 0  Warnings: 0


SELECT * FROM account;


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


SELECT * FROM account_audit;


acct_id
txn_id
acct_name
acct_balance
tax_rate
valid_from
valid_to


1
1
Bill
100.00
0.10
2023-09-06
2038-01-19

2
2
Ben
1000.00
0.10
2023-09-06
2038-01-19


SELECT SLEEP(1); -- to give visible differences in TIMESTAMPs!


SLEEP(1)


0


UPDATE account 
SET acct_balance = acct_balance + 50 WHERE acct_id = 1;

Rows matched: 1  Changed: 1  Warnings: 0


SELECT * FROM account;


acct_id
acct_name
acct_balance
tax_rate
acct_opened


1
Bill
150.00
0.10
2019-11-01

2
Ben
1000.00
0.10
2019-11-01


SELECT * FROM account_audit;


acct_id
txn_id
acct_name
acct_balance
tax_rate
valid_from
valid_to


1
1
Bill
100.00
0.10
2023-09-06
2023-09-06

1
3
Bill
150.00
0.10
2023-09-06
2038-01-19

2
2
Ben
1000.00
0.10
2023-09-06
2023-09-06


UPDATE account 
SET acct_balance = acct_balance + 50 WHERE acct_id = 1;

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.10
2023-09-06
2023-09-06

1
3
Bill
150.00
0.10
2023-09-06
2023-09-06

1
4
Bill
200.00
0.10
2023-09-06
2038-01-19

2
2
Ben
1000.00
0.10
2023-09-06
2023-09-06


INSERT INTO account_audit 
(acct_id, acct_name, acct_balance, tax_rate, valid_from, valid_to)  
VALUES
(1, 'Bill', 1000000, 0.5, '2019-11-01', '2020-03-31'),
(1, 'Bill',     100, 0.2, '2020-04-01', '2020-07-31'),
(1, 'Bill',   50000, 0.5, '2020-08-01', '2020-12-31');

Records: 3  Duplicates: 0  Warnings: 0


SELECT * FROM account_audit
ORDER BY acct_id, valid_to DESC;


acct_id
txn_id
acct_name
acct_balance
tax_rate
valid_from
valid_to


1
4
Bill
200.00
0.10
2023-09-06
2038-01-19

1
1
Bill
100.00
0.10
2023-09-06
2023-09-06

1
3
Bill
150.00
0.10
2023-09-06
2023-09-06

1
7
Bill
50000.00
0.50
2020-08-01
2020-12-31

1
6
Bill
100.00
0.20
2020-04-01
2020-07-31

1
5
Bill
1000000.00
0.50
2019-11-01
2020-03-31

2
2
Ben
1000.00
0.10
2023-09-06
2023-09-06


SELECT
  *,
  DATEDIFF(
  CASE
    WHEN (valid_to >  CURDATE()) THEN CURDATE() 
    ELSE valid_to
  END,  
  valid_from) + 
  CASE
    WHEN valid_from = valid_to THEN 0
    ELSE 1
  END
  AS t_days,
  valid_to - valid_from
FROM account_audit
ORDER BY acct_id, valid_to DESC;


acct_id
txn_id
acct_name
acct_balance
tax_rate
valid_from
valid_to
t_days
valid_to - valid_from


1
4
Bill
200.00
0.10
2023-09-06
2038-01-19
1
149213

1
1
Bill
100.00
0.10
2023-09-06
2023-09-06
0
0

1
3
Bill
150.00
0.10
2023-09-06
2023-09-06
0
0

1
7
Bill
50000.00
0.50
2020-08-01
2020-12-31
153
430

1
6
Bill
100.00
0.20
2020-04-01
2020-07-31
122
330

1
5
Bill
1000000.00
0.50
2019-11-01
2020-03-31
152
9230

2
2
Ben
1000.00
0.10
2023-09-06
2023-09-06
0
0
acct_id	txn_id	acct_name	acct_balance	tax_rate	valid_from	valid_to
1	1	Bill	100.00	0.10	2023-09-06	2038-01-19
2	2	Ben	1000.00	0.10	2023-09-06	2038-01-19
acct_id	txn_id	acct_name	acct_balance	tax_rate	valid_from	valid_to
1	4	Bill	200.00	0.10	2023-09-06	2038-01-19
1	1	Bill	100.00	0.10	2023-09-06	2023-09-06
1	3	Bill	150.00	0.10	2023-09-06	2023-09-06
1	7	Bill	50000.00	0.50	2020-08-01	2020-12-31
1	6	Bill	100.00	0.20	2020-04-01	2020-07-31
1	5	Bill	1000000.00	0.50	2019-11-01	2020-03-31
2	2	Ben	1000.00	0.10	2023-09-06	2023-09-06