hi,

I have a fill in the blanks question which says,

In databases which do not support we must use a _________ to auto generate a numeric increment key.

my answer is TRIGGER. can it also be LAST_INSERT_ID(id+1)

I am confused about the second answer as that does not suit to the generic sense of the question.

A wise word is highly appreciated.

cheers

I think trigger won't help you. There is a restriction -

Quote:
Within a stored function or trigger, it is not permitted to modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger.



You could use a stored procedure that would insert values. Also, it is possible to use - 'MAX(id)+1'.

Definately _not_ LAST_INSERT_ID() since that is set by the autoincrement feature and without the autoincrement feature that function always returns 0.

And trigger is ruled out by the reason that the previous post pointed out.

Since this sounds like a generic DBMS question I think your answer should be SEQUENCE which is used by Oracle and Postgresql among others.

It's basically a named counter internally in the DBMS that you use to get an incremented value:

Code:
INSERT INTO
myTable
( id, someValue)
VALUES
(mySequence.nextval, 'somethingToStoreInTable')


The mySequence.nextval will increment mySequence by 1 and return the new value.