title: For delete trigger is not working (Microsoft SQL Server Management Studio)
body: <p>I have a table Universities and a table Cities. There is a m:n relationship between them, so the relationship is saved in a further table called CityUniversities. Now I wanted to write a trigger which deletes all relations between the tables Cities and Universities when deleting a university from Universities. However it's not working. I always receive the following error message when trying to delete a University:</p>
  
  <p>"Msg 547, Level 16, State 0, Line 2
  The DELETE statement conflicted with the REFERENCE constraint "FK_CityUniversities_Universities". The conflict occurred in database "Alumni_Dev", table "dbo.CityUniversities", column 'UniversityId'.
  The statement has been terminated."</p>
  
  <p>That's the trigger (for table Universities) which I have implemented and succesfully executed:</p>
  
  <pre><code>USE [Alumni_Dev]
  GO
  /****** Object:  Trigger [dbo].[deleteUni]    Script Date: 05/02/2012 11:42:43 ******/
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  
  ALTER TRIGGER [dbo].[deleteUni]
  ON  [dbo].[Universities] 
  FOR DELETE
  AS 
  DECLARE @UniId int
  SELECT @UniId = (SELECT UniversityId FROM deleted)
  BEGIN
  --Remove constraints
  DELETE FROM dbo.CityUniversities WHERE UniversityId = @UniId;
  END  
  </code></pre>
  
  <p>I already compared my solution with several references, however I can't find anything that could be wrong. Could it be that there are any wrong settings in one of the affected tables?</p>
  
  <p>I'd be happy to hear any suggestions.</p>
  
  <hr>
  
  <p>I finally solved my problem with the following trigger (thx to Nikola):</p>
  
  <pre><code>ALTER TRIGGER [dbo].[deleteUni]
  ON  [dbo].[Universities] 
  INSTEAD OF DELETE
  AS 
  DECLARE @UniId int
  SELECT @UniId = UniversityId FROM deleted
  BEGIN
  --Remove constraints
  DELETE FROM dbo.CityUniversities WHERE UniversityId = @UniId;
  DELETE FROM dbo.Universities WHERE UniversityId = @UniId;
  END
  </code></pre>