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
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?