Inserted row deletes after trigger

I'm hoping someone has seen this before because I have no idea what could be causing it.

I have an SQL 2005 database with multiple tables and several triggers on the various tables all set to run after insert and update.

My program inserts a record into the "items" via a SP that returns the index of the newly added row. The program then inserts a row into another table that is related to items. When the row is inserted into the second table it gets an error that it cannot insert the record because of a foreign key restraint. Checking the items table shows the record that was just inserted in there is now deleted.

The items record is only deleted when I have my trigger on that table enabled. Here is the text of the trigger:

GO
SETANSI_NULLSON
GO
SETQUOTED_IDENTIFIERON
GO

ALTERTRIGGER [dbo].[TestTrigger]
ON [dbo].[items]
AFTERINSERT

AS
BEGIN

SETNOCOUNTON;

INSERTINTO tblHistory(table_name, record_id, is_insert)
VALUES('items', 123, 1)

END

tblHistory's field types are (varchar(50), BigInt, bit).

As you can see there is nothing in the trigger to cause the items record to be deleted, so I have no idea what it could be? Anyone ever see this before?

Thanks in advance!

Hey,

I don't know that the row is deleted, but that the row doesn't get actually inserted for some reason. What do the two insertions look like? In SQL or ADO.NET code? Could it be that the first item doesn't get inserted, then returns a number that doesn't match an entry in that table, and that is why you get an error for the second insert?

|||

No, the first item is inserted and the returned value is exactly what it should be. When we test it without the trigger enabled and it all works, the new primary key value is the next value after the one that disapeared (i.e. if the record that was deleted was 5 the next one that works is 6).

|||

Are you using @.@.identity?

|||

If one of the follow on triggers fails, for whatever reason, the insert statement will be rolled back.

I suggest commenting out the triggers one by one (from last run to first run) until you figure out which one is the problem.

(Or learn to use the debugger in sql server.)

|||

David is correct in that the trigger code is considered part of the insert transaction.

If the trigger fails, then the entire "transaction" is rolled back, including the insert. If tblHistory has a foreign key constraint, and the trigger fails because of it, then you will get exactly what you are describing. The record is inserted partially (uncommitted), the trigger is fired, an error is encountered, then the insert is rolled back and the error from the trigger is sent to the client.

|||

It couldn't have been the trigger failing, b/c there are no constraints on the history table and while yo uare correct the error would have been returned as if it was coming from the insert statement I said above "When the row is inserted into the second table it gets an error that it cannot insert the record because of a foreign key restraint." The error was not about hte history table.

Motley you actually had the answer. What was happening is the stored procedure ran and inserted the row into items, the trigger ran on that and inserted the row into web updates, the stored procedure then returned the @.@.Identity, but since that returns the last identity of any insert to the database it was returning the identity of the history table, not the items table. When the second insert was run it was trying to insert the wrong identity and failed the foreign key restraint, resulting in the entire transaction to fail and rollback, giving the appearance the items record had been deleted.

Thanks for your help!

|||

Use scope_identity(), not identity! scope_identity was created to avoid just this problem!

sql