MySQL I have a table with an auto-increment field, which is the primary key:

CREATE TABLE myTable ( id INT AUTO_INCREMENT PRIMARY KEY, field VARCHAR(45) );

In the second field, I want to write the value that should be calculated based on the value of the primary key.

This does not work:

 CREATE TRIGGER `myTable_AFTER_INSERT` AFTER INSERT ON `myTable` FOR EACH ROW BEGIN UPDATE myTable SET field=CONCAT(NEW.id,"T") WHERE id=NEW.id; END;

When I try to add, I get a MySQL error:

Can't update table "myTable" is in stored function.

And the next option doesn't work either:

 CREATE TRIGGER `myTable_BEFORE_INSERT` BEFORE INSERT ON `myTable` FOR EACH ROW BEGIN SET NEW.field=CONCAT(NEW.id,"T"); END;

Because in this case NEW.did is not yet defined.

How do I solve the problem? Thank!

    2 answers 2

    It will work in this form:

     CREATE TRIGGER `myTable_BEFORE_INSERT` BEFORE INSERT ON `myTable` FOR EACH ROW BEGIN SET @new_field = ""; SELECT AUTO_INCREMENT INTO @new_field FROM information_schema.tables WHERE table_name = 'myTable' AND table_schema = DATABASE(); SET NEW.field=CONCAT("A",@new_field); END;

      Borik Bobrujskov, this will work only for the insert of one record. For the inserts of many records - only the first record will have the correct value.

      Slightly redundant "trigger", but flexible and working:

       CREATE TRIGGER `myTable_BEFORE_INSERT` BEFORE INSERT ON `myTable` FOR EACH ROW BEGIN SET NEW.id = IF(NEW.id > 0, NEW.id, (SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='myTable')); IF NEW.field IS NULL THEN SET NEW.field = NEW.id; END IF; END;