I've got two tables: Countries (Id, Name) and Cities (Id, CountryId, Name), CountryId is a Foreign Key to Cities.
ALTER TABLE WITH CHECK ADD CONSTRAINT FOREIGN KEY REFERENCES ON DELETE CASCADE An advantage here is that the delete is one atomic statement reducing (though, as usual, not 100% removing) the need to worry about transaction and lock settings.
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.
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.
Hope it is clear now.
McNets16.4k55 gold badges2424 silver badges6060 bronze badges
Przemyslaw WojdaPrzemyslaw Wojda
1 Answer
As Scott Hodgin pointed out, you can't delete a country meanwhile there are cities belonging to this country.
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
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:
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.Barcelona is still there. The trigger works as expected.
Now try to delete '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.
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
Paul White♦56.4k1414 gold badges297297 silver badges471471 bronze badges
McNetsMcNets16.4k55 gold badges2424 silver badges6060 bronze badges
Not the answer you're looking for? Browse other questions tagged sql-servertrigger or ask your own question.
I'm trying to delete all users but getting the error:
The query:
Seems I need to use
Mark Storey-Smithon delete cascade;
but I'm stuck.29.5k55 gold badges7777 silver badges118118 bronze badges
DarkmageDarkmage
5 Answers
You don't need to use the on delete cascade. Somebody (the schema design author) had made sure you cannot delete a person that is still referenced by an article. It succeeded, you were just trying to do this and was blocked, kudos to the designer.
Now go and talk with that somebody that designed the schema and knows the constraints and ask him how to properly delete the records you're trying to delete, in the correct order and taking the proper precautions to keep the database consistent.
Community♦
Remus RusanuRemus Rusanu47.6k33 gold badges8282 silver badges160160 bronze badges
You have two real choices here, you can disable constraints on the table. This usually not a great idea as you can end up with a bad data condition if you're messing with data that relates to other tables, but not know the full extent of your schema and it may suit your purposes:
Remember to turn the constraint back on after the delete with
The second choice would be to drop and re-add the constraint with the ON DELETE CASCADE option using:
Based on your FK name it looks like your parent table is M02ArticlePersons and the parent column is M06Persons.
If you did not author this schema please try to consider why the constraints may be present, and understand that violating them in this manner may have unintended side effects.
AhrotahnteeAhrotahntee
dbo.M02ArticlePersons table of column M06PersonId is reffered in another table.So before delete statement, disable this relationships and try again
below is for disbling the foreign key
and this is to enable it
Hope this will work
Navin 431Navin 431
There is another manual option too:
You can go to the child table and delete the child rows referenced by the parent key. Then you can delete the parent row. This is essentially what the cascade delete does. This way, you do not have to drop/recreate/alter your constraints.
StanleyJohnsStanleyJohns5,68022 gold badges1717 silver badges4444 bronze badges
This little code will help for any table that you want to delete records from. It takes care of referential integrity as well ...
Below code will generate DELETE statements .. Just specify the schema.table_Name
Kin ShahKin Shah54.6k44 gold badges8585 silver badges197197 bronze badges