Mysql trigger to copy id from one column to another in same table when record is created

I need to copy id field from one column to another when new record is created. Number must be same in both column and in best case it should be inserted in same moment in to the database. Best solution which i found is probably to use triggers.

I created sql like this:

TRIGGER `db`.`copy_id_into_mbc_id` BEFORE INSERT ON `my_table` FOR EACH ROW
BEGIN
SET NEW.mbc_id = (SELECT AUTO_INCREMENT FROM information_schema.TABLES where TABLE_SCHEMA='mbc' and TABLE_NAME='my_table');
END

This solution seems working but i need to know if it is safe. I read some old topic as some peoples says it can cause race condition. I’m not sure in which case this is risky as from my test it seems fine.

Is there any better way to be sure that both column have exactly same value when it is inserted? Id field is auto incremented and in code i don’t know it until record is created but update second field in this moment is to late for me.

So my question are: With this trigger value in both column will be always same when new record is created? Is there better way to keep both value same in mysql 5.5 or in doctrine/php ?

Source: Symfony Questions

Was this helpful?

0 / 0

Leave a Reply 0

Your email address will not be published. Required fields are marked *