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.
Best Answer
As Scott Hodgin pointed out, you can't delete a country meanwhile there are cities belonging to this country.
You could change
NO ACTION
byON DELETE CASCADE
But according to your question you want a trigger that blocks delete action for some Cities if it Country still exists.
Then when you try to create the trigger you'll receive next error:
Humm, that's a catch-22 situation.
Ok, let me remove
ON DELETE CASCADE
action and now I try to delete some city.Barcelona is still there. The trigger works as expected.
Now try to delete 'Spain':
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.
Now change the trigger by:
Well Berlin still there, now if you want to delete Spain you must delete Madrid and Barcelona before.
dbfiddle here