Good Day
I have a strange occurrence that I hope you can help me with. I have 3 related tables in SQL Server.
In SQL Delete Rule set as follows:
Inventory_PriceList --> Inventory_Prices = Cascade
Inventory_PriceList --> Client_Account = No Action
Both have "Enforce Foreign Key Constraint" set to yes. Also the cascade delete option in Radzen is disabled.
What i noticed is that if i have a page with just 1 datagrid for the PriceList and attempt to delete a record , it correctly tires to delete the record
exec sp_executesql N'SELECT TOP(1) [i].[id], [i].[name]
FROM [dbo].[Inventory_Pricelist] AS [i]
WHERE [i].[id] = @__id_0',N'@__id_0 int',@__id_0=24
When I add a linked datagrid to the page it attempts to delete the PriceList and the individual prices. Now i would not expect that because that (I know there is a cascade in SQL Index). Should in theory be the job of the SQL server. In production I could have as many as 100000 price records per pricelist.
exec sp_executesql N'SET NOCOUNT ON;
DELETE FROM [dbo].[Inventory_Prices]
OUTPUT 1
WHERE [id] = @p0;
DELETE FROM [dbo].[Inventory_Prices]
OUTPUT 1
WHERE [id] = @p1;
DELETE FROM [dbo].[Inventory_Prices]
OUTPUT 1
WHERE [id] = @p2;
DELETE FROM [dbo].[Inventory_Prices]
OUTPUT 1
WHERE [id] = @p3;
DELETE FROM [dbo].[Inventory_Prices]
OUTPUT 1
WHERE [id] = @p4;
DELETE FROM [dbo].[Inventory_Pricelist]
OUTPUT 1
WHERE [id] = @p5;
',N'@p0 int,@p1 int,@p2 int,@p3 int,@p4 int,@p5 int',@p0=47,@p1=48,@p2=49,@p3=51,@p4=52,@p5=24
Now where I have the main issue is that if I add a 3rd datagrid to the page to show what clients are using the PriceList and I attempt to delete the PriceList, it now posts to SQL a delete for the PriceList, each Individual price as well as the ClientAccount.
exec sp_executesql N'SET NOCOUNT ON;
DELETE FROM [dbo].[Client_Account]
OUTPUT 1
WHERE [id] = @p0;
DELETE FROM [dbo].[Inventory_Prices]
OUTPUT 1
WHERE [id] = @p1;
DELETE FROM [dbo].[Inventory_Prices]
OUTPUT 1
WHERE [id] = @p2;
DELETE FROM [dbo].[Inventory_Prices]
OUTPUT 1
WHERE [id] = @p3;
DELETE FROM [dbo].[Inventory_Prices]
OUTPUT 1
WHERE [id] = @p4;
DELETE FROM [dbo].[Inventory_Prices]
OUTPUT 1
WHERE [id] = @p5;
DELETE FROM [dbo].[Inventory_Pricelist]
OUTPUT 1
WHERE [id] = @p6;
',N'@p0 nvarchar(450),@p1 int,@p2 int,@p3 int,@p4 int,@p5 int,@p6 int',@p0=N'BA31E141-6DF9-4593-A5EC-AF49E25911B7',@p1=47,@p2=48,@p3=49,@p4=51,@p5=52,@p6=24
The delete fails on the page because of a foreign key constrain of another table on client. Why would my application attempt to delete the client table because there is a datagrid on the page and if I remove the datagrid it does not attempt to delete the Client table.
Your help will be greatly appreciated.