Bug #30676 Trigger BEFORE INSERT should run after auto-numbering

• Submitted: 28 Aug 2007 18:19
• Modified: 3 Sep 2009 8:59
• Reporter: Filipe Martins
• Status: Duplicate
• Category: MySQL Server: Stored Routines
• Severity: S4 (Feature request)
• Version: 5.0.45
• OS: Windows (XP SP2)
• CPU Architecture: Any
• Tags: auto-numbering, BEFORE INSERT

[28 Aug 2007 18:19] Filipe Martins

Description:
When the BEFORE INSERT trigger runs, there's still no value in the auto-numbered field. I think it would be great if it did, cause it's always very useful to know the Id of the record just created.

Of course, there may be a very good reason for things being as they are, but if that isn't the case I think this behavior would be more appropriate.

Thanks and keep up the great work.

Filipe Martins
http://www.i-senso.com

How to repeat:
DROP TABLE IF EXISTS `ttest`;
CREATE TABLE `ttest` (
  `Id` int(11) NOT NULL auto_increment,
  `OtherField` int(11) default 123,
  PRIMARY KEY  (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TRIGGER IF EXISTS `ttest_trigger`;
CREATE TRIGGER ttest_trigger BEFORE INSERT ON ttest
	FOR EACH ROW SET NEW.OtherField = NEW.Id;

INSERT INTO ttest (Id) VALUES (NULL);
SELECT * FROM ttest;

[3 Sep 2009 8:59] Sveta Smirnova

Thank you for the report.

This was discussed already in bug #9629. So I close the report as duplicate of bug#9629