SQL Server: will an INSTEAD OF trigger affect referential integrity checks?

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.

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

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.

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 DEFAULT is another possibility, depending on your application.

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

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:

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;