8VjgTae7

1CREATE TABLE IF NOT EXISTS entity1_id (
2    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
3    entity_id INT UNSIGNED NOT NULL UNIQUE
4); 
5     
6CREATE TABLE IF NOT EXISTS entity1 (
7    id INT UNSIGNED PRIMARY KEY,
8    create_time DATETIME DEFAULT CURRENT_TIMESTAMP
9) ENGINE=InnoDB;
10
11CREATE TABLE IF NOT EXISTS id_counter (
12    entity_name CHAR(30)  PRIMARY KEY,
13    counter INT NOT NULL DEFAULT 1
14);
15
16INSERT INTO id_counter (entity_name) VALUES ('entity1'); 
17     
18DELIMITER |
19
20CREATE TRIGGER entity1_idgen BEFORE INSERT ON entity1
21FOR EACH ROW BEGIN
22    DECLARE cnt, eid INT UNSIGNED;
23
24    IF (NEW.id IS NULL) THEN
25        SET cnt = (SELECT counter FROM id_counter WHERE entity_name='entity1' FOR UPDATE);
26
27        SET eid = (SELECT entity_id FROM entity1_id WHERE id=cnt);
28        IF (eid IS NULL) THEN
29            SET @errMsg = CONCAT("table `entity1_id` doesn't have row where id=", cnt);
30            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT=@errMsg;
31        END IF;
32
33        SET NEW.id = eid;
34        UPDATE id_counter SET counter=counter+1 WHERE entity_name='entity1';
35    END IF;
36END|
37
38DELIMITER ;