Delete trigger not working when executed

Question

Is there any posibility to delete from table2 (which has a reference from table1) after delete one row in table1? (eg: if I delete a CustomerID from Customers at the same time to delete the order he made (OrderID))

CREATE TRIGGER DelBilete
ON Curse 
AFTER DELETE
AS 
BEGIN
  DELETE FROM Bilete 
  WHERE IDCursa IN (SELECT IDCursa FROM Curse)
END

Answer 1

There are two ways to do this: either with a cascading delete (which you set up with your foreign key constraint):

ALTER TABLE [dbo].[Orders]  WITH NOCHECK ADD  CONSTRAINT    
[FK_Orders_Customer] FOREIGN KEY([customerId])
REFERENCES [dbo].[Customer] ([customerId])
ON DELETE CASCADE

... or with a trigger, as you have tried. (You might try something like this, actually. Disclaimer: this was from memory and not tested.)

CREATE TRIGGER DelBilete
ON Curse 
AFTER DELETE
AS 
BEGIN
  DELETE FROM Bilete WHERE IDCursa IN(SELECT IDCursa FROM deleted)
END
GO

If you just delete where the foreign key is present in the primary key table, you'll delete all your orders (or whatever Bilete contains)!

ETA: On getting a foreign key constraint error:

The delete statement conflicted with the reference constraint "FK_Bilete_Curse".

If you have a foreign key constraint, and you want to delete from the primary table (Curse) and have the related records deleted from the related table (Bilete), an AFTER trigger won't solve the problem, precisely because it is an AFTER trigger. It takes place after the records have been deleted. If they can't be deleted, the trigger doesn't fire. And if those records are referenced by other records, they can't be deleted.

So you then have two options:

1. The cascading delete, above;

2. An INSTEAD OF trigger. (See here for more details.)

An INSTEAD OF trigger in your case would look like this (again, from memory and documentation, not tested):

CREATE TRIGGER DelBilete
ON Curse 
INSTEAD OF DELETE
AS 
BEGIN
   DELETE FROM Bilete WHERE IDCursa IN(SELECT IDCursa FROM deleted)
   DELETE FROM Curse WHERE IDCursa IN (SELECT IDCursa from deleted)
END
GO

With an INSTEAD OF trigger, when you execute code to delete a record from Curse, the trigger runs instead. It deletes the related records from Bilete, and then executes the delete from Curse.

INSTEAD OF triggers do not run recursively, so the DELETE from Curse in the trigger doesn't force another run of the trigger.