| Author |
Topic |
|
JoeNak
Constraint Violating Yak Guru
292 Posts |
 Posted - 2009-02-12 : 11:55:22
|
| I have a table with an identity and use a stored procedure to perform inserts into the table. I also have an Instead Of Delete trigger on the table to prevent any deletion. When looking through the table I see missing identities (ie 1, 2, 4, etc...). We have run a trace on the sql login that the application uses and all activity in the trace shows up in the table, but we still experience missing identities. I have looked through all other stored procedures and none are performing any deletes to this table. Has anyone seen anything like this? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-02-12 : 12:41:10
|
Yes, many times.When you insert a record that breaks any constraint such as unique or foreign key, the record is throwing an error, and the identity value is lost.
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
JoeNak
Constraint Violating Yak Guru
292 Posts |
Posted - 2009-02-12 : 13:38:40
|
| Shouldn't I be able to see that SQL that's breaking the constraint in a trace? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-02-12 : 13:42:49
|
It can be other reasons too, such as inserting the value 333 in a tinyint column.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-02-12 : 13:43:40
|
You shouldn't worry about gaps in an identity column! There are 2-4 billion of them...
E 12°55'05.63"N 56°04'39.26" |
|
|
|
JoeNak
Constraint Violating Yak Guru
292 Posts |
Posted - 2009-02-12 : 13:43:58
|
| Hmm, I've removed the only constraint on the table and I put some error handling around the insert, we'll see... |
|
|
|
JoeNak
Constraint Violating Yak Guru
292 Posts |
Posted - 2009-02-12 : 13:45:26
|
| I'm not really worried about the gaps, but the application that's inserting records should be inserting 2 records for a particular type of change and sporadically we only get one of the records. The developer assures me that he's calling the proc correctly every time, but I'm having a hard time explaining the missing records. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-02-12 : 15:02:45
|
Set up a trace to see how procedure is called.Is there a trigger on the table? Maybe the trigger is badly written?Can we see the procedure code?
E 12°55'05.63"N 56°04'39.26" |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-02-12 : 15:17:28
|
| also, check the eventlog of the application server to see if any sql errors are logged there.Be One with the OptimizerTG |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2009-02-12 : 16:27:20
|
| maybe it is an IdentityIncrement issue ?? could it be? |
|
|
|
JoeNak
Constraint Violating Yak Guru
292 Posts |
Posted - 2009-02-12 : 16:35:29
|
| Ok, the trigger is good, nothing in the app server logs, identity is set to increment by 1. I was finally able to capture some good data from profiler. It seems my COBOL developer has wrapped the proc call in a transaction. I'll need to dig a little deeper, but I think I have my answer. Thanks. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-02-12 : 17:25:40
|
Most triggers are badly written to handle one record only.The trigger works, because most of the time only one record is inserted.And when two records are inserted, one od them will fail, and in your case leave gaps in the identity sequence.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
|
Missing Identity Value