SET NOCOUNT ON;
USE tempdb;
GO
CREATE TABLE dbo.tipologie (
  Id int NOT NULL PRIMARY KEY
  );
CREATE TABLE dbo.tipologia_lingue (
  IdLingua int NOT NULL, 
  Id int NOT NULL 
    CONSTRAINT fk_tipologia_lingue$is$tipologia FOREIGN KEY
      REFERENCES dbo.tipologie(Id)
  );
GO
INSERT INTO dbo.tipologie 
  VALUES (1), (2), (3);
INSERT INTO dbo.tipologia_lingue
  VALUES (1, 1),
      (2, 1), (2, 2),
      (3, 1), (3, 2), (3, 3);
GO
CREATE PROCEDURE dbo.usp (
  @id int 
  )
AS BEGIN

  SET XACT_ABORT ON
  BEGIN TRANSACTION;
  BEGIN TRY
    DELETE dbo.tipologie 
      WHERE Id = @id;
    IF (@@ERROR = 0)
      DELETE tipologia_lingue
        WHERE Id = @id;

    COMMIT;
  END TRY

  BEGIN CATCH
    ROLLBACK;
    -- ritorna l'errore
    DECLARE @ErrorMSG varchar(2000);
    SET @ErrorMSG = ERROR_MESSAGE();
    RAISERROR (@ErrorMSG, 16, 1);
    RETURN -100;
  END CATCH

END;
GO
EXEC dbo.usp 5;
GO
EXEC dbo.usp 1;
SELECT *
  FROM dbo.tipologia_lingue
  WHERE Id = 1;
SELECT * 
  FROM dbo.tipologie
  WHERE Id = 1;
GO
DROP PROCEDURE dbo.usp;
DROP TABLE dbo.tipologia_lingue, dbo.tipologie;
--<----------
Msg 50000, Level 16, State 1, Procedure usp, Line 23
The DELETE statement conflicted with the REFERENCE constraint "fk_tipologia_lingue$is$tipologia". The conflict occurred in database "tempdb", table "dbo.tipologia_lingue", column 'Id'.
IdLingua    Id
----------- -----------
1           1
2           1
3           1

Id
-----------
1