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?