How to create a trigger that generate a random specific output to insert into a table in MariaDB?

Question

I have an account table which is laid out in 3NF:

CREATE TABLE account (
    account_id INT AUTO_INCREMENT,
    customer_id INT NOT NULL,
    account_type_id VARCHAR(15) NOT NULL,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    opening_balance DECIMAL(10,2) NOT NULL DEFAULT 51,
    account_number INT(8) NOT NULL,
    sort_code VARCHAR(8) NOT NULL,
    created_date DATE DEFAULT now(),
    branch_id INT NOT NULL,
    PRIMARY KEY (account_id),
    UNIQUE(sort_code, account_number)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1; 

However, I want to know if there is a way to create a trigger that upon inserting into this table a unique sort_code and account_number can be generated. The account number should just be a unique 8 digit, however, the first 3 digits will contain the id of branch_id from a separate branch table, e.g an account belonging to branch_id 700 should have an account number starting with 700. And sort code should have this format "XX-XX-XX" where the X's numbers and this should also be checked to be unique. Is it possible to make a trigger to do this in MySQL/mariadb?

Answer 1

There are probably more than one way to do this, but here is one idea.

You don't have to use triggers.

Use MariaDB sequences to generate unique numbers - assuming it's OK that the numbers are created sequentially.

Assuming you are designing a system for one particular bank, and the digits in the sort code are used to identify the bank and branch, then:

• The first two digits of the sort code identifies the bank and will always be the same, so you don't need to store these.

• Put the branch digits in the same column as the account number digits.

• Create one sequence per branch where you specify a unique range for each, defined by the sequence's MINVALUE and MAXVALUE.

• For display purposes, you can decode into sort code and account number by using generated columns.

Basic example code for sequences

CREATE SEQUENCE seq_branch_1 
  MINVALUE 100000000 
  MAXVALUE 199999999 
  START WITH 100000000 
  INCREMENT BY 1;

CREATE SEQUENCE seq_branch_2
  MINVALUE 200000000 
  MAXVALUE 299999999 
  START WITH 200000000 
  INCREMENT BY 1;

And so on.

At the time of creating an account you presumably know which branch it's for, so:

INSERT INTO account(customer_id, account_type_id, ... unique_account_number, ...)
  VALUES (416, 2, ..., NEXTVAL(seq_branch_2), ...);