1

In MySQL I need to know if it's possible in a BEFORE INSERT trigger to find out if the primary key ID (NEW.Id) is being automatically generated by the auto-increment value or if the ID was manually set.

The reason for this is that in my application rows can both be inserted with a set ID OR rely on the auto increment value, but I need to know this.

What is the value of NEW.Id in a before trigger if left empty when inserting a row? It doesn't seem to be NULL, so is it already set to the current auto increment value? But how to find out then if this is happening?

UPDATE: It seems the value of the integer-type primary key (NEW.Id) in a BEFORE INSERT trigger is 0 (not NULL!), even when there is no default value set for this column.

Dylan
  • 9,129
  • 20
  • 96
  • 153
  • Why do you need to know this? (No idea if it is possible) – ypercubeᵀᴹ May 17 '13 at 17:39
  • I'm implementing my own sequence solution with a separate table that holds the new sequence values for each table. But I still would like to use the auto-increment feature of MySQL too... – Dylan May 17 '13 at 17:42

1 Answers1

0

you need to put the trigger over the AFTER option, than you will be last returned to ID, if you put the trigger on the BEFORE option, the action performed will give you zero value because no have a any value.

Carlos Rodriguez
  • 833
  • 8
  • 12