SQL Server delete trigger - row handle referred to a deleted row or a row marked for deletion

Question

I have a delete trigger on a table which is used to delete entries from a table in an another database.

CREATE TRIGGER [dbo].[Trigger_Contracts_Delete] ON [dbo].[Contracts]
AFTER DELETE NOT FOR REPLICATION
AS
  IF @@ROWCOUNT = 0 RETURN

  DELETE seconddb.dbo.second_table 
  WHERE contractId IN (SELECT d.ContractID FROM deleted d)

When I delete a record using my application (legacy app nothing know about its internals) I get the error "Row handle referred to a deleted row or a row marked for deletion"

However when I modified this trigger to add an additional select statement (SELECT d.ContractID FROM deleted d) just before delete.. I do not get the error message. What could be the reason for it to work when select statement is added, is it that by issuing select on "deleted" I have locked the "deleted" table ? it be issuing a

CREATE TRIGGER [dbo].[Trigger_Contracts_Delete] ON [dbo].[Contracts]
AFTER DELETE NOT FOR REPLICATION
AS
   IF @@ROWCOUNT = 0 RETURN

   SELECT d.ContractID FROM deleted d;

   DELETE seconddb.dbo.second_table 
   WHERE contractId IN (SELECT d.ContractID FROM deleted d)

Answer 1

If you alawys want to delete the records from the child table, why not set up cascading delte instead of a trigger?

Answer 2

your table(s) are missing primary keys! add primary keys and the problem will go away.