Agfdhyk

I've a problem with the delete-actions in a table-structure with Multiple Cascade Path's.

I know, the theme is often discussed here and a good description/solution can be found e.g. in
https://www.mssqltips.com/sqlservertip/2733/solving-the-sql-server-multiple-cascade-path-issue-with-a-trigger with the solution to use an INSTEAD OF Trigger.

But my table structure is a little bit different because of a nullable-foreign key. The next image shows the desired structure:

                            +-----------------+                       +----------------+

    +------(fk not null)----| ProductionSteps |<-----(fk not null)----| CUSTOMER-TABLE |

    |     delete cascade    +-----------------+     delete cascade    +----------------+

    |                                ^

    V                                |

+--------------------+          (fk nullable)

|  ProductionOrders  |        on delete set null

+--------------------+               |

    ^                                |

    |                       +-----------------+

    +------(fk not null)----| ProcessingData  |

          delete cascade    +-----------------+

And I don't want to use an INSTEAD OF Delete-Trigger for the ProductionSteps-Table because my tables are part of a delivered software product and the customer can extend the system with additional tables, e.g. a CUSTOMER-TABLE with an fk to ProductionSteps (delete cascade).
So by using the INSTEAD OF Trigger the customer has to extend the delivered Trigger for his CUSTOMER-TABLE and therefore the trigger isn't updatable.
Also the customer isn't familiar with triggers.

So I hope there's an alternative solution.

The sql-commands for creating the tables and testdata are added at the end. Inside there are also a detailed description including the error messages.
Environment: SQLServer 2016

The desired foreign keys are (like shown in the image):

• ProductionSteps: fk to ProductionOrders (not null, delete cascade)


• ProcessingData 1. fk to ProductionOrders (not null, delete
  cascade) and 2. fk to ProductionSteps (nullable, on delete set
  null)

Because of the multiple cascade path's, the "on delete set null" isn't feasible.

==> new version with changes:

• ProcessingData: fk to ProductionSteps now without delete-action,
  only nullable


• ProductionSteps: new FOR-DELETE-Trigger to set the
  fk in ProcessingData to NULL

When using this version, it seems, the trigger isn't executed when deleting records from ProductionSteps and so a SQL-Error occures.

Is there an alternative solution other than an INSTEAD OF delete-trigger?

-- -----------------------------------------------------------------------------------------------------

--      Step 1:     create table ProductionOrders

-- -----------------------------------------------------------------------------------------------------

CREATE TABLE [ProductionOrders](

    [Sequence] [bigint] IDENTITY(1,1) NOT NULL,

    [Code] [nvarchar](32) NOT NULL,

 CONSTRAINT [PK_ProductionOrders] PRIMARY KEY CLUSTERED 

(

    [Code] 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



-- -----------------------------------------------------------------------------------------------------

--      Step 2a:    create table ProductionSteps

-- -----------------------------------------------------------------------------------------------------

CREATE TABLE [ProductionSteps](

    [Sequence] [bigint] IDENTITY(1,1) NOT NULL,

    [ProductionOrderCode] [nvarchar](32) NOT NULL,

    [Code] [nvarchar](32) NOT NULL,

 CONSTRAINT [PK_ProductionSteps] PRIMARY KEY CLUSTERED 

(

    [ProductionOrderCode] ASC,

    [Code] 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



-- -----------------------------------------------------------------------------------------------------

--      Step 2b:    fk  ProductionSteps ==> ProductionOrders  (not null / delete cascade)

-- -----------------------------------------------------------------------------------------------------

ALTER TABLE [ProductionSteps]  WITH CHECK ADD  CONSTRAINT [FK_ProductionSteps_ProductionOrder] FOREIGN KEY([ProductionOrderCode])

REFERENCES [ProductionOrders] ([Code])

ON DELETE CASCADE

GO



ALTER TABLE [ProductionSteps] CHECK CONSTRAINT [FK_ProductionSteps_ProductionOrder]

GO



-- -----------------------------------------------------------------------------------------------------

--      Step 3a:    create table ProcessingData

-- -----------------------------------------------------------------------------------------------------

CREATE TABLE [ProcessingData](

    [Sequence] [bigint] IDENTITY(1,1) NOT NULL,

    [ProductionOrderCode] [nvarchar](32) NOT NULL,

    [WorkCenterCode] [nvarchar](32) NOT NULL,

    [ProductionOrderStepCode] [nvarchar](32) NULL,

    [ProductionStepCode] [nvarchar](32) NULL,

    [Order] [int] NOT NULL,

 CONSTRAINT [PK_ProcessingData] PRIMARY KEY CLUSTERED 

(

    [ProductionOrderCode] ASC,

    [WorkCenterCode] ASC,

    [Order] 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



-- -----------------------------------------------------------------------------------------------------

--      Step 3b:    fk  ProcessingData ==> ProductionOrders  (not null / delete cascade)

-- -----------------------------------------------------------------------------------------------------

ALTER TABLE [ProcessingData]  WITH NOCHECK ADD  CONSTRAINT [FK_ProcessingData_ProductionOrders] FOREIGN KEY([ProductionOrderCode])

REFERENCES [ProductionOrders] ([Code])

ON DELETE CASCADE

GO



ALTER TABLE [ProcessingData] CHECK CONSTRAINT [FK_ProcessingData_ProductionOrders]

GO



-- -----------------------------------------------------------------------------------------------------

--      Step 3c:    fk  ProcessingData ==> ProductionSteps  (nullable)

--

--              PROBLEM:    "on delete set null"  in the next SQL-command isn't feasible !!!

--                          executing the SQL-command __WITH__ including "on delete set null" causes an ERROR:

--

--                  Msg 1785, Level 16, State 0, Line 2 

--                  Introducing FOREIGN KEY constraint 'FK_ProcessingData_ProductionSteps' on table 'ProcessingData' may cause cycles or multiple cascade paths. 

--                  Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. 

--                  Msg 1750, Level 16, State 1, Line 2 

--                  Could not create constraint or index. See previous errors. 

--

-- -----------------------------------------------------------------------------------------------------

ALTER TABLE [ProcessingData]  WITH CHECK ADD  CONSTRAINT [FK_ProcessingData_ProductionSteps] FOREIGN KEY([ProductionOrderStepCode], [ProductionStepCode])

-- REFERENCES [ProductionSteps] ([ProductionOrderCode], [Code])  on delete set null

   REFERENCES [ProductionSteps] ([ProductionOrderCode], [Code])

GO



ALTER TABLE [ProcessingData] CHECK CONSTRAINT [FK_ProcessingData_ProductionSteps]

GO



-- -----------------------------------------------------------------------------------------------------

--      Step 4:     create new trigger for table  ProductionSteps  instead of "on delete set null"

-- -----------------------------------------------------------------------------------------------------

CREATE TRIGGER [TRG_ProcessingData_ProductionStepsDelete]

ON [ProductionSteps]

FOR DELETE

AS

BEGIN

    SET NOCOUNT ON

    UPDATE [ProcessingData]

    SET [ProductionOrderStepCode] = NULL, [ProductionStepCode] = NULL

    FROM [ProcessingData] PD

    INNER JOIN DELETED D

    ON PD.ProductionOrderStepCode = D.ProductionOrderCode

    AND PD.ProductionStepCode = D.Code

END

GO



ALTER TABLE [ProductionSteps] ENABLE TRIGGER [TRG_ProcessingData_ProductionStepsDelete]

GO



-- -----------------------------------------------------------------------------------------------------

--      Step 5:     create some test-records in tables

-- -----------------------------------------------------------------------------------------------------

INSERT INTO [ProductionOrders] ([Code])   VALUES   ('po1')

GO



INSERT INTO [ProductionSteps] ([ProductionOrderCode] ,[Code]) VALUES ('po1', 'ps1')

INSERT INTO [ProductionSteps] ([ProductionOrderCode] ,[Code]) VALUES ('po1', 'ps2')

GO



INSERT INTO [ProcessingData] ([ProductionOrderCode], [WorkCenterCode], [ProductionOrderStepCode],[ProductionStepCode],[Order])

     VALUES ('po1', 'wc1', 'po1', 'ps1', 1)

INSERT INTO [ProcessingData] ([ProductionOrderCode], [WorkCenterCode], [ProductionOrderStepCode],[ProductionStepCode],[Order])

     VALUES ('po1', 'wc1', 'po1', 'ps2', 2)

GO



-- -----------------------------------------------------------------------------------------------------

--      Step 6:     Test: delete all records in table  ProductionSteps

--

--          Executing the next SQL-Command causes an ERROR!!!   It seems, the trigger isn't executed

--

--          ERROR:

--              Msg 547, Level 16, State 0, Line 1

--              The DELETE statement conflicted with the REFERENCE constraint "FK_ProcessingData_ProductionSteps". 

--              The conflict occurred in database "TEST_DeleteSetNull", table "dbo.ProcessingData".

--

-- -----------------------------------------------------------------------------------------------------

DELETE FROM [ProductionSteps] 

GO