--- In Firebird-Architect@yahoogroups.com, Jim Starkey <jas@...> wrote:

>
> Lester Caine wrote:
> > Adam wrote:
> >
> >> Running an update which affects 0 rows would not fire any update

trigger.

> >>
> >> So if it tried insert then update:
> >>
> >> Before insert trigger -> attempt insert -> fails -> before update
> >> trigger -> update -> after update trigger.
> >>
> >> Or if it tried update and insert:
> >>
> >> The update wouldn't affect any row, so no update triggers are fired,
> >> therefore the insert is attempted.
> >>
> >> So depending on the methodology chosen, the before insert

triggers may

> >> be fired.
> >>
> >
> > Good pickup Adam - I knew there was a reason I try the update first ;)
> >
> >
> Rather than trying either statement, the internal Falcon engine
> (inaccessible from MySQL) first fetches the record. If it finds a
> record, it updates it, otherwise it does an insert.

Hi Jim,

That sounds like a more sensible solution to me.

Can I ask how the 'insert or update'/'update or
insert'/'replace'/'substitute'/'whatever is decided' will handle MGA
isolation though (from my understanding, not an issue under MySQL due
to design).

Consider snapshot transactions T1, T2.

T1: Starts
T2: Starts
T1: insert or update into ..... values (1) ....
T2: insert or update into ..... values (1) .... (same DML)

Is T2 allowed to see the record (attempting and failing the update due
to lock conflict), or does T2 attempt to insert a second record,
because the exists check if performed under transaction isolation
would fail?

Adam