SQL Server：INSTEAD OF触发器会影响参照完整性检查吗？

I would like to set a foreign key to null if what it refers to doesn't exist. However, I would like all the other referential integrity checks to work as normal.

如果它所引用的内容不存在,我想将外键设置为null。但是,我希望所有其他参照完整性检查正常工作。

Will an INSTEAD OF trigger force me to write all the integrity checks?

INSTEAD OF触发器会强制我写出所有完整性检查吗?

Since triggers cannot change the inserted table and data cannot exist in the target table because of referential integrity, is there a way to null these foreign keys so that records can save?

由于触发器无法更改插入的表,并且由于引用完整性而无法在目标表中存在数据,是否有办法将这些外键置空以便记录可以保存?

EDIT: The biggest mistake I made is not knowing that the database won't make any changes if an INSTEAD OF trigger exists. The trigger must make the changes to the table itself.

编辑:我犯的最大错误是不知道如果存在INSTEAD OF触发器,数据库将不会进行任何更改。触发器必须对表本身进行更改。

2 个解决方案

#1

As @Catcall points out, the ON DELETE SET NULL referential action does what you appear to want.

正如@Catcall指出的那样,ON DELETE SET NULL引用操作可以实现您想要的效果。

That said, yes you can write an INSTEAD OF trigger to do the same (or a variation on a theme) without having to take care of all the referential integrity constraints yourself. Here's a brief example:

也就是说,是的,您可以编写一个INSTEAD OF触发器来执行相同的操作(或主题的变体),而无需自己处理所有参照完整性约束。这是一个简短的例子:

Tables and test data:

表格和测试数据:

CREATE TABLE T1 (ID INTEGER NOT NULL UNIQUE);
CREATE TABLE T2 (ID INTEGER REFERENCES T1 (ID));

INSERT INTO T1 VALUES (1), (2), (3);
INSERT INTO T2 VALUES (1), (2), (3), (2), (3), (3);

The trigger:

-- 'CREATE TRIGGER' must be the first statement in a batch.
CREATE TRIGGER tr__T1__instead_of_delete
ON T1
INSTEAD OF DELETE
AS
BEGIN;

UPDATE T2
   SET ID = NULL
 WHERE EXISTS (
               SELECT * 
                  FROM deleted
                 WHERE deleted.ID = T2.ID
              );

DELETE
  FROM T1
 WHERE EXISTS (
               SELECT * 
                  FROM deleted
                 WHERE deleted.ID = T1.ID
              );

END;

Test the trigger:

测试触发器:

DELETE 
  FROM T1 
 WHERE ID = 3;

#2

If a foreign key is nullable, you can use ON DELETE SET NULL, which will update the foreign key to NULL if the referenced row is deleted.

如果外键可以为空,则可以使用ON DELETE SET NULL,如果删除引用的行,则会将外键更新为NULL。

ON DELETE SET DEFAULT is another possibility, depending on your application.

ON DELETE SET DEFAULT是另一种可能性,具体取决于您的应用程序。

#1