Question

我有一席大学和一席城市。他们之间有小:关系,因此在另一个称为城市大学的表格中拯救了这种关系。现在我想写一个触发点,在大学大学毕业时删除各大学和大学之间的所有关系。但它没有工作。在试图删除大学时,我总是收到以下错误信息:

"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."

这是我已经执行并成功实施的触发因素(表大学):

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

我已经把我的解决办法与几个方面进行了比较,但我找不到任何可能错误的东西。受影响的表格中是否有错误的环境?

我很高兴听到任何建议。

我最后用以下触发因素(从X到Nikola)解决了我的问题:

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

Answer 1

不能利用Trigger从其他表格中删除孤儿记录,因为限制是在触发器被引爆之前检查的。 http://msdn.microsoft.com/en-us/library/a933119%28v=sql.80%29.aspx”rel=“nofollow”

您也可使用INSTEAD OF 触发器,如果你想要在删除之前做一些检查,但不要忘记添加del 大学,其中......在触发器末,实际上删除记录。

关于触发点的两点:

Add SET NOCOUNT ON at the beginning of trigger body to avoid posibility of sql statements inside trigger changing the value of "rows affected" which you might need in calling code,
Don t将删除和插入表格视为只包含一个记录。当你试图删除两所大学时,将会感到惊讶。你应该加入一个表格,以删除或插入表格,将其用作过滤器,或者使用:
```
delete dbo.CityUniversities
  from dbo.CityUniversities
 inner join deleted
    on dbo.CityUniversities.UniversityId = deleted.UniversityId 
```

或

delete dbo.CityUniversities
 where exists (select null
                 from deleted
                where deleted.UniversityId =dbo.CityUniversities.UniversityId)

Oh,

SELECT @UniId = (SELECT UniversityId FROM deleted)

is equivalent to:

SELECT @UniId = UniversityId FROM deleted

友情链接