quote:

---

Originally posted by weitzhandler

CREATE TRIGGER [dbo].Contact_DELETE
   ON  Contact 
  INSTEAD OF DELETE 
AS 
BEGIN
	SET NOCOUNT ON;
DELETE FROM Phone WHERE ContactId = ContactId --Should refer to the desired delete-row's ContactId
DELETE FROM Contact WHERE ContactId = ContactId --Same as above
--I want that if the contact deletion fails, it should revert the deletion of the phones too and print an error.
END
GO

Shimmy

---

CREATE TRIGGER [dbo].[Contact_DELETE]
   ON  [dbo].[Contact] 
  INSTEAD OF DELETE 
AS 
BEGIN
	SET NOCOUNT ON;
DECLARE @ContactId int
SELECT @ContactId = ContactId FROM DELETED
DELETE FROM Phone Where Contact = @ContactId
DELETE FROM Contact Where ContactId = @ContactId --If this line fails to execute it should rollback previous line's transaction as well
END

quote:

---

Originally posted by visakh16

put them in a transaction and rollback if @@ERROR >0

---

ALTER TRIGGER [dbo].[Contact_DELETE]
   ON  [dbo].[Contact] 
  INSTEAD OF DELETE 
AS 
BEGIN
	    SET NOCOUNT ON;
         
    DECLARE @ContactId int
    SELECT @ContactId = ContactId FROM deleted

    BEGIN TRY
        BEGIN TRANSACTION
            DELETE FROM Phone Where Contact = @ContactId    
            DELETE FROM Contact Where ContactId = @ContactId
        COMMIT TRAN
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRAN
            DECLARE @ERROR_MESSAGE nvarchar(2048), @ERROR_SEVERITY int
            SELECT @ERROR_MESSAGE = ERROR_MESSAGE(), @ERROR_SEVERITY = ERROR_SEVERITY()
            RAISERROR(@ERROR_MESSAGE, @ERROR_SEVERITY, 1)
    END CATCH
END

CREATE TRIGGER dbo.trgContact_DELETE
ON		dbo.Contact
AFTER		DELETE
AS 

SET NOCOUNT ON

DELETE		p
FROM		Phone AS p
INNER JOIN	inserted AS i ON i.ContactID = p.ContactID