MySQL Trigger To Update New Row

We are in the process of migrating between 2 systems and need to have 2 fields for one of our database tables that always stay in sync. Here is the table structure:

CREATE TABLE `example` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `object_id` int(11) NOT NULL DEFAULT '0',
  `value` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `object_id` (`object_id`)
);

Every time one of the systems inserts a new row we need to have object_id set to id. We can't use 'before insert' since the id column is an auto_increment column so it's value is NULL before insert and due to the limitations of the MySQL 'after insert' on triggers I can't do the following:

CREATE TRIGGER insert_example 
  AFTER INSERT ON  example 
  FOR EACH ROW 
  SET 开发者_开发问答NEW.object_id = NEW.id;

I can't update the code for either system so I need a way to accomplish this on the database side. Both systems are going to be inserting new rows. How can I accomplish this?

Using a trigger which fires before the insert should do the job

CREATE TRIGGER insert_example 
  BEFORE INSERT ON  example 
  FOR EACH ROW 
  SET NEW.object_id = NEW.id;

EDIT:

As the OP pointed out NEW.id won't work with auto-increment; one could use the following trigger (use at own risk):

CREATE TRIGGER insert_example 
      BEFORE INSERT ON  example 
      FOR EACH ROW 
      SET NEW.object_id = (
            SELECT AUTO_INCREMENT 
            FROM information_schema.TABLES 
            WHERE TABLE_SCHEMA = DATABASE() 
            AND TABLE_NAME = 'example'
      );

But I'd rather re-think this somewhat strange requirement - why do you need the pk value twice in the table?

Is there any reason you cant use a BEFORE INSERT trigger?

I've always seen AFTER INSERT triggers as a method to manipulate other tables rather than the table for which the trigger was executed on.

Rule of thumb, manipulate table the trigger is running on = BEFORE INSERT, manipulate other tables AFTER INSERT :)

I think your trigger will never create in the first place because you can't refer NEW.column_name in an AFTER INSERT trigger.

Try doing this in a BEFORE INSERT trigger (PLEASE IGNORE THIS FIX AS IT WILL NOT WORK):

CREATE TRIGGER `insert_example` BEFORE INSERT ON `t`
FOR EACH ROW 
SET NEW.`object_id` = NEW.`id`;

Please change the table and column names as per your schema.

Hope this helps.