--- In IBObjects@yahoogroups.com, joe@j... wrote:

> > >I guess I'm basically asking two questions: 1) Does the
> > >insert reserve the key, or does the commit reserve it?
> >
> > As above. The Post of the insert doesn't reserve anything. The

first to

> > commit wins and the other(s) get key violations.
> >
> > > 2) Does in insert
> > >trigger a key violation, or does the commit trigger it?
> >
> > See above.
>
> Ok, some clarifications:
>
> If User A and User B both insert with the same key before either

commits, will both

> INSERTS succeed without Key Violations, and the Key Violation occurs

with the

> second COMMIT (not INSERT)?

Trigger are fired depending on what event you have chained it to, but
generally, you have not to make confusion between a BeforeInsert and
AfterInsert EVENT of the query, and the similar triggers of the table.
When you use myTable.Insert, it's a CLIENT ONLY stuff, nothing is sent
to the server, the server knows NOTHING about you inserting.
When you POST the record (after the insert), the client (IBO) sends a
INSERT xxx SLQ command to the server. THEN the server fires the
BeforeInsert and the AfterInsert trigger, that means "before insertig
the record in the database" and "after having inserted the record".
The same about Edit (client only stuff) that, once posted, becomes an
UPDATE xxx SQL command.
So if you POST a inserted/modified record, the server tries to use the
new values. A part from a onld Interbase bug, that Firebird has fixed
for sure, but I think IB 6.5 and 7 have too, the triggers
BeforeInsert (or BeforeUpdate) are fired, the record value added
(updated) in the database, and doing so if constraints errors occurs
(i.e. unique key violation) an exception is sent to the client.

>
> What if User A inserts and commits, then User B inserts? Will User

B get a Key

> Violation on the INSERT, or on it's COMMIT?

In the POST ;) But this leaves the client in a undefined state. You
can react two ways:
a) ask the user another key, and try again Post + Commit
b) rollback

>
> Can both inserts and commits generate Key Violations (depending on

the situation),

> or is it only commits?

AFAIK ony post

>
> Another question: If I start a transaction and try to insert (or

insert and commit) and

> get a Key Violation, and I need to change the query (or paramaters)

and retry, what

> exactly would I do?

You have to give the server valid values, so change key values and try
again. In any case, don't leave the situation without a successful
Commit or a Rollback, this would leave the transaction open for a long
time.

> Rollback transaction, Start transaction, change SQL, Execute again
> or, do I even have to bother rolling back and restarting? Can I

just change the SQL

> and re-Execute? What if the insert succeeded, but the commit failed?
>
> -Joe

Well, apart from cuncurrent transaction problems, an argumenti I have
to deeply investigate, and if you use "read committed" transaction
isolation level, think about transaction like "the final destiny" of
the work you have done since it's start.
you can:
start a transaction
add / delete /edit how many records you want
rollback -----> you loose ALL what you have done since Start Transaction
or
Commit -----> tell "FINAL DECISION", "NO REVERSABLE STATE" to what you
have done.

Simple sample situation: put that you have to delete a customer and
all his orders (let's forget about referential integrity for a moment).
And put that you want to do this in a Paradox-like way.
So you have to:
a) loop throuh his orders, deleting then one by one
b) perform some calculations, display, and so on (just for example)
c) delete the customer

without transaction, if an error occurs in a) or b) or c), you are in
a inconsistent state (i.e. only some orders deleted, or deleted all
orders but not the customer)
with transaction, you start the transaction, use try..except, do
inside it all steps a-c, if an error occurs, except do the RollBack,
so the database is left as it was before, if no error occurred, you
Commit, telling the database "ok, make all this stuff DEFINITIVE".

try
a)
b)
c)
Commit;
except
RollBack
end;

Read some interesting tech sheets in the Ibobject site, and
http://www.cvalde.com/ has some very interesting articles (well, it
had...)
regards
Marco Menardi