Delay affecting key violation?!

Hello!
I'm having a little problem with SQL Server (this doesn't happen in Access):
Using ADO:
I begin a transaction
I insert a row into table X, but the primary key already exists so(an error
should be raised immediately)
I insert a related row into a related table X.
I do some more things
Hey! Error?! Key violation?! Where? At table X (my first insert)...
uhhhhhhhhh!!!
Is there any way to make SQL Server send an error immediately if the insert
violate the primary key?
I just don't get this :(
Thanks for all your help.
Jorge Carvalho
rdc02271@.yahoo.comset xact_abort on
"Jorge C" wrote:

> Hello!
> I'm having a little problem with SQL Server (this doesn't happen in Access
):
> Using ADO:
> I begin a transaction
> I insert a row into table X, but the primary key already exists so(an erro
r
> should be raised immediately)
> I insert a related row into a related table X.
> I do some more things
> Hey! Error?! Key violation?! Where? At table X (my first insert)...
> uhhhhhhhhh!!!
> Is there any way to make SQL Server send an error immediately if the inser
t
> violate the primary key?
> I just don't get this :(
> Thanks for all your help.
> Jorge Carvalho
> rdc02271@.yahoo.com
>
>|||Thanks but little problem:
If I set transaction mode to explicit the entire transaction will be rolled
back, neglecting my commit or rollback statement.
So what's the point of having explicit transaction mode when you are unable
to: control the error the minute it happens + make necessary changes and try
again without rolling back the transaction?
Thanks.
Jorge Carvalho
rdc02271@.yahoo.com
"Ray D" <RayD@.discussions.microsoft.com> escreveu na mensagem
news:F78827F1-F1BC-4ADF-B25A-F517529B9C12@.microsoft.com...
> set xact_abort on
> "Jorge C" wrote:
>
>|||You are correct that an error should be raised as soon as you insert a
row that violates a constraint. However, what you haven't told us is
the exact "key violation" error message you are getting? Was it the
foreign key or primary key constraint? It seems like the second insert
caused the error so maybe the message is "INSERT statement conflicted
with COLUMN FOREIGN KEY constraint", which might explain why you see it
as an error "at table X".
Also, check if there are any triggers on these tables. Maybe a trigger
is at fault.
David Portas
SQL Server MVP
--|||Hi!
The primary key at the first table is being violated.
Our scheme is like this: (if you have a better system please share :) )
1)The user fills out an invoice (header+details+some other info)
2)Then we try to save the invoice using the latest invoice number+1;but
since we have more one application doing the same with the same database
(multi-user) the insert for the first table may fail (invoice header). So we
need to change the invoice number and try again.
The primary key violation message refers to this first operation.
Thanks.
Jorge Carvalho
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> escreveu na mensagem
news:1112358100.042970.307850@.z14g2000cwz.googlegroups.com...
> You are correct that an error should be raised as soon as you insert a
> row that violates a constraint. However, what you haven't told us is
> the exact "key violation" error message you are getting? Was it the
> foreign key or primary key constraint? It seems like the second insert
> caused the error so maybe the message is "INSERT statement conflicted
> with COLUMN FOREIGN KEY constraint", which might explain why you see it
> as an error "at table X".
> Also, check if there are any triggers on these tables. Maybe a trigger
> is at fault.
> --
> David Portas
> SQL Server MVP
> --
>|||That isn't an efficient strategy for generating an incrementing key. Also,
as a matter of design principle it's not a good idea to build a system that
generates errors as part of its correct behaviour.
Max invoice number + 1:
INSERT INTO Invoices (invoice_num,... /* other columns */)
SELECT COALESCE(MAX(invoice_num),0)+1, ... /* other data */
FROM Invoices
If it isn't a problem to have gaps in the sequence of invoice numbers then
you might also consider using the IDENTITY property to generate the
sequence.
Don't forget to declare your table's natural key. Maybe (customer,
invoice_date) for example.
David Portas
SQL Server MVP
--