Trigger that will stop from deleting if a foreign key record is not deleted

I've got two tables: Countries (Id, Name) and Cities (Id, CountryId, Name), CountryId is a Foreign Key to Cities.

I need a trigger that will prevent deleting certain Cities if the Country is not being deleted, in other words certain Cities can only be deleted when Country is being deleted.

CREATE TABLE Countries 
(
    Id [int] IDENTITY(1,1) PRIMARY KEY NOT NULL, 
    Name [VARCHAR] (100) NOT NULL
);

INSERT INTO Countries VALUES ('Germany'), ('Spain'), ('Italy');

CREATE TABLE Cities 
(
    Id [int] IDENTITY(1,1) PRIMARY KEY NOT NULL, 
    CountryId [int], 
    Name [VARCHAR] (100) NOT NULL,
    CONSTRAINT FK_Cities_Countries 
        FOREIGN KEY (CountryId) REFERENCES Countries(Id)
);

INSERT INTO Cities VALUES 
(1, 'Berlin'), (1, 'Dresden'), (2, 'Madrid'), (2, 'Barcelona'), (3, 'Rome'), (3, 'Milano');

ERP system allows to delete records both from Cities and Countries (when Country deletion action is called first records from Cities are deleted then from the Countries. I need to block deletion from Cities if Country is not being deleted i.e.

DELETE FROM Cities WHERE Name='Berlin' --needs to be stopped
DELETE FROM Countries WHERE Name='Germany' --needs to go

Hope it is clear now.

As Scott Hodgin pointed out, you can't delete a country meanwhile there are cities belonging to this country.

DELETE FROM Countries WHERE Name = 'Germany';

Msg 547 Level 16 State 0 Line 1
The DELETE statement conflicted with the REFERENCE constraint "FK_Cities_Countries". The conflict occurred in database "db", table "dbo.Cities", column 'CountryId'.

You could change NO ACTION by ON DELETE CASCADE

CONSTRAINT FK_Cities_Countries 
    FOREIGN KEY (CountryId) REFERENCES Countries(Id)
    ON DELETE CASCADE

But according to your question you want a trigger that blocks delete action for some Cities if it Country still exists.

CREATE TRIGGER trgCities_Delete
ON Cities
INSTEAD OF DELETE
AS
BEGIN

    DELETE c
    FROM   Cities c
    JOIN   deleted d
    ON     c.ID = d.ID
    WHERE  NOT EXISTS (SELECT 1
                       FROM   Countries
                       WHERE  Id = d.CountryId);
END

Then when you try to create the trigger you'll receive next error:


Msg 2113 Level 16 State 1 Line 1
Cannot create INSTEAD OF DELETE or INSTEAD OF UPDATE TRIGGER 'trgCities_Delete' on table 'Cities'. This is because the table has a FOREIGN KEY with cascading DELETE or UPDATE.

Humm, that's a catch-22 situation.

Ok, let me remove ON DELETE CASCADE action and now I try to delete some city.

DELETE FROM Cities WHERE Name = 'Barcelona';

SELECT * FROM Cities;

Id | CountryId | Name     
-: | --------: | :--------
 1 |         1 | Berlin   
 2 |         1 | Dresden  
 3 |         2 | Madrid   
 4 |         2 | Barcelona
 5 |         3 | Rome     
 6 |         3 | Milano

Barcelona is still there. The trigger works as expected.

Now try to delete 'Spain':

DELETE FROM Countries WHERE Name = 'Spain';

Msg 547 Level 16 State 0 Line 1
The DELETE statement conflicted with the REFERENCE constraint "FK_Cities_Countries". The conflict occurred in database "db", table "dbo.Cities", column 'CountryId'.

Catch-22 again. I can't delete Barcelona neither Spain!! That's a bit mental.

Let me suggest another approach. If you need to avoid deletes, flag the affected records using some field and let foreign keys do their job.

ALTER TABLE Cities ADD Blocked int NULL;

UPDATE Cities
SET    Blocked = 1
WHERE  Name = 'Berlin';

Now change the trigger by:

ALTER TRIGGER trgCities_Delete
ON Cities
INSTEAD OF DELETE
AS
BEGIN

    DELETE c
    FROM   Cities c
    JOIN   deleted d
    ON     c.ID = d.ID
    AND    c.Blocked <> 1;

END

DELETE FROM Cities WHERE Name = 'Berlin';
SELECT * FROM Cities;

Id | CountryId | Name      | Blocked
-: | --------: | :-------- | ------:
 1 |         1 | Berlin    |       1
 2 |         1 | Dresden   |       0
 3 |         2 | Madrid    |       0
 4 |         2 | Barcelona |       0
 5 |         3 | Rome      |       0
 6 |         3 | Milano    |       0

Well Berlin still there, now if you want to delete Spain you must delete Madrid and Barcelona before.

DELETE FROM Cities WHERE Name IN ('Barcelona', 'Madrid');
DELETE FROM Countries WHERE Name = 'Spain';
SELECT * FROM Cities;

Id | CountryId | Name    | Blocked
-: | --------: | :------ | ------:
 1 |         1 | Berlin  |       1
 2 |         1 | Dresden |       0
 5 |         3 | Rome    |       0
 6 |         3 | Milano  |       0

dbfiddle here

StackExchange Database Administrators Q#200533, answer score: 3