At 05:10 AM 14/04/2008, Jiri Cincura wrote:

>On 4/13/08, Jochem Maas <jochem@...> wrote:
>> the field doesn't allow a NULL, that's why you get the error.
>> one way to tackle this is use an insert trigger that sets
>> the new.id (from the relevant generator) when the given insert id
>> is not set ... and change your query to:
>
>I have a before insert trigger.
>
>> update or insert into polls (question, visible) values (?, ?) returning new.id
>
>But this isn't same as mine. This will do insert every time.

It's not the same as yours, Jiri, but it is correct. You have only two (or three) ways you can do this:

1. Rely on the trigger, in which case your INSERT statement must be

insert into polls (question, visible)
values (?,?)
returning new.id', $this->id, $this->question,
$this->visible);

But you don't want to use this way, because you want to use the UPDATE OR INSERT syntax. That leaves you two ways:

2. Prequery the generator into a variable and then apply that value to the first parameter of the original syntax. If the driver doesn't already have a function that does this, you could easily write one, making it generic.

or
3. Change your trigger to be

if (new.id is null or new.id = -999) then
new.id = gen_id(mygen, 1);

Then always assign -999 to the param for id. (I don't like this one but it does work...)

Helen