Getting id generated in a trigger for further requests

Question

I have a table with two columns:

caseId, referring to a foreign table column
caseEventId, int, unique for a given caseId, which I want to auto-increment for the same caseId.

I know that the auto-increment option based on another column is not available in mySql with InnoDb:

MySQL Auto Increment Based on Foreign Key

MySQL second auto increment field based on foreign key

So I generate caseEventId into a trigger. My table:

CREATE TABLE IF NOT EXISTS mydb.caseEvent (
  `caseId` CHAR(20) NOT NULL,
  `caseEventId` INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`caseId`, `caseEventId`),
  # Foreign key definition, not important here.
ENGINE = InnoDB;

And my trigger:

CREATE DEFINER=`root`@`%` TRIGGER `mydb`.`caseEvent_BEFORE_INSERT` BEFORE INSERT ON `caseEvent` FOR EACH ROW
BEGIN
    SELECT COALESCE((SELECT MAX(caseEventId) + 1 FROM caseEvent WHERE caseId = NEW.caseId),0)
        INTO @newCaseEventId;
    SET NEW.`caseEventId` = @newCaseEventId;
END

With this, I get my caseEventId which auto-increments.

However I need to re-use this new caseEventId in further calls within my INSERT transaction, so I place this id into @newCaseEventId within the trigger, and use it in following instructions:

START TRANSACTION;
INSERT INTO mydb.caseEvent (caseId) VALUES ('fziNw6muQ20VGYwYPW1b');
SELECT @newCaseEventId;
# Do stuff based on @newCaseEventId
COMMIT;

This seems to work just fine but... what about concurrency, using connection pools etc...?

Is this @newCaseEventId variable going to be shared with all clients using the same connection, can I run into problems when my client server launches two concurrent transactions? This is using mysql under nodejs.

Is this safe, or is there a safer way to go about this? Thanks.

Edit 2020/09/24

FYI I have dropped this approach altogether. I was trying to use the db in a way it isn't meant to be used.

Basically I have dropped caseEventId, and any index which is supposed to increment nicely based on a given column value.

I rely instead on properly written queries on the read side, when I retrieve data, to recreate my caseEventId field...

user_5193536 · Answer

That is no problem, the user defined variables a per client.

That means every user has its own use defined varoables

User-defined variables are session specific. A user variable defined by one client cannot be seen or used by other clients. (Exception: A user with access to the Performance Schema user_variables_by_thread table can see all user variables for all sessions.) All variables for a given client session are automatically freed when that client exits. see manul

Getting id generated in a trigger for further requests

1 Answers