Cascade delete when combined column values are unique foreign key

Question

I have the following tables:

[locations]
id PK
title
description

[floors]
id PK
locationid FK
floorlevel
description

[rooms]
id PK
locationid FK
floorid FK
description

[features]
id PK
title

[object_features]
id PK
featureid FK
objectid FK (linked to either [locations].id,[floors].id or [rooms].id)
objectype (value of 1=location, 2=floor, 3=room)

Now if location X is deleted from [locations] I want to delete related rooms and floors (yes rooms can also be related directly to a location and not through a floor), that's done easily with a cascade delete.

But each of the different object types (location, floor and room) can also have 0 or more features, which are stored in [object_features] Meaning that [object_features].objectid and [object_features].objecttype might have the same value more than once, but it's a unique combination when combined with [object_features].featureid.

So when a location is deleted, I also want to delete all rows in [object_features] that are related to the rows deleted in [locations], [floors] and/or [rooms] because of that location X deletion. I feel like I need to add some logic on each table ([locations],[floors],[rooms]) that takes action when a row is deleted. I feel like a trigger might do the trick, but am not sure if that's the recommended approach. And also am unsure how to implement it, an example would be great.

Already checked here: CASCADE DELETE on two foreign key constraints, but seems a different solution.

DDL

CREATE TABLE [dbo].[locations](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [title] [nvarchar](500) NOT NULL,
 CONSTRAINT [PK_homes] PRIMARY KEY NONCLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO



CREATE TABLE [dbo].[location_floors](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [locationid] [int] NOT NULL,
 CONSTRAINT [PK_location_floors] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[location_floors]  WITH CHECK ADD  CONSTRAINT [FK_location_floors_locations] FOREIGN KEY([locationid])
REFERENCES [dbo].[locations] ([id])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[location_floors] CHECK CONSTRAINT [FK_location_floors_locations]
GO


CREATE TABLE [dbo].[location_rooms](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [locationid] [int] NOT NULL,
    [floorid] [int] NULL,
 CONSTRAINT [PK_location_rooms] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[location_rooms]  WITH CHECK ADD  CONSTRAINT [FK_location_rooms_locations] FOREIGN KEY([locationid])
REFERENCES [dbo].[locations] ([id])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[location_rooms] CHECK CONSTRAINT [FK_location_rooms_locations]
GO


CREATE TABLE [dbo].[features](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [title] [nvarchar](50) NOT NULL,
    [createdate] [datetime] NOT NULL,
 CONSTRAINT [PK_voorzieningen] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[features] ADD  CONSTRAINT [DF_voorzieningen_createdate]  DEFAULT (getdate()) FOR [createdate]
GO


CREATE TABLE [dbo].[object_features](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [objectid] [int] NOT NULL,
    [objecttype] [tinyint] NOT NULL,
    [featureid] [int] NOT NULL,
    [createdate] [datetime] NOT NULL,
 CONSTRAINT [PK_location_voorzieningen] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[object_features] ADD  CONSTRAINT [DF_location_voorzieningen_createdate]  DEFAULT (getdate()) FOR [createdate]
GO

ALTER TABLE [dbo].[object_features]  WITH CHECK ADD  CONSTRAINT [FK_object_features_features] FOREIGN KEY([featureid])
REFERENCES [dbo].[features] ([id])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[object_features] CHECK CONSTRAINT [FK_object_features_features]
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'1=location, 2=floor, 3=room' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'object_features', @level2type=N'COLUMN',@level2name=N'objecttype'
GO

Answer 1

An INSTEAD OF DELETE trigger may be used in SQL Server to perform a cascaded delete in cases where DRI isn't permitted due to multiple update paths.

Below is an example that performs the needed deletes in the proper order. Just follow a similar pattern for triggers on the other tables that need cascaded deletes.

CREATE TRIGGER tr_location_delete
ON dbo.locations
INSTEAD OF DELETE
AS
SET NOCOUNT ON;

--delete rooms for location
DELETE rooms
FROM dbo.rooms
JOIN dbo.floors ON floors.id = rooms.floorid
JOIN deleted ON deleted.id = rooms.floorid;

--delete floors for location
DELETE floors
FROM dbo.floors
JOIN deleted ON deleted.id = floors.locationid;

--delete location
DELETE locations
FROM dbo.locations
JOIN deleted ON deleted.id = locations.id;
GO