Create Trigger in mysql with call to stored procedure and auto increment of a table

i have a DB with multiple Properties which each has its own DB ID. I have a centralized DB to collect all the data coming from those DB’s.

In order to differentiate the data and not running into duplicate use of an auto increment value, i want to run a trigger before each insert.

This trigger should call a stored procedure to get the respective DB ID and concat this with the auto increment value from the respective table.

I know from Oracle you can concat a stored procedure and sequence. Looking for something like this in mysql.

Below is my trigger which throws an error:

CREATE TRIGGER order_item_sequence 
BEFORE INSERT ON order_item
FOR EACH ROW
BEGIN
  IF (NEW.order_item_seq_id IS NULL) THEN
    SET NEW.order_item_seq_id = CONCAT(call my_dbid, NEW.table_id);
  END IF;
END

New.table_id is my auto increment column in table order_item and call my_dbid is my stored procedure.

I tested the stored procedure, it is fine. The error is not giving me a real fact, just saying syntax is not correct. Maybe I am missing something?


Source: stackoverflow-php