I am having this issue with my newly created database. I created my table and tried adding a prefix when adding data like it is shown in this question How to make MySQL table primary key auto increment with some prefix and came up with this trigger to use:
CREATE TRIGGER tg_contracts_insert BEFORE INSERT ON contracts
FOR EACH ROW
BEGIN
INSERT INTO contracts_seq VALUES (NULL);
SET NEW.cid = CONCAT('CN', LPAD(LAST_INSERT_CID(), 5, '0'));
END
cid being my primary key to be auto-inserted. I was apparently able to add the trigger However, when I tried adding my values on the main table, "contracts", with this:
INSERT INTO contracts (name, conres, conven, start, end, tipo) VALUES ('Roberto Grajales', 'Pedro', 'Pascual', '2022-12-12', '2022-08-08', 'Servicio');
name, conres, conven being VARCHAR type, start and end DATE and tipo being ENUM. I get the following error message:
FUNCTION database.LAST_INSERT_CID does not exist
Anyone has any idea why and how to solve this? Thanks!
