Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Удаление строки в связанных таблицах / 9 сообщений из 9, страница 1 из 1
20.04.2021, 07:50
    #40064062
Strength Class
Strength Class
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Удаление строки в связанных таблицах
Есть 2 таблицы связанные внешним ключом (FK_ValsPrices_DataVals).

В свойствах ключа прописано каскадное удаление. Фото в прилагаемой картинке.

Есть триггер на удаление:
USE [ztt]
GO
/****** Object: Trigger [dbo].[DeleteTrigger] Script Date: 20.04.2021 8:00:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[DeleteTrigger] ON [dbo].[DataVals]
INSTEAD OF DELETE
AS
-- проверяем нет ли ссылок на шифр вала в сертификатах
SET NOCOUNT ON;

DECLARE @nCntr Integer
DECLARE @idDataVal Integer;

SET @idDataVal = (SELECT TOP 1 idDataVals FROM deleted);

SET @nCntr = 0;
SET @nCntr = (SELECT TOP 1 idDataVals FROM CertOutData WHERE idDataVals=@idDataVal);

if ISNULL(@nCntr,0)=0
BEGIN
DELETE ValsPrices WHERE idDataVals=@idDataVal;
DELETE DataVals WHERE idDataVals=@idDataVal;
END

При попытке удалить запись:
DELETE
FROM [ztt].[dbo].[DataVals]
WHERE ([idDataVals]=2006)

Выдает ошибку:
Msg 547, Level 16, State 0, Procedure DeleteTrigger, Line 17 [Batch Start Line 0]
The DELETE statement conflicted with the REFERENCE constraint "FK_DataVals_ValsPrices". The conflict occurred in database "iomzcerttest", table "dbo.DataVals", column 'idValMainPrice'.
The statement has been terminated.

Запись не удаляется. Почему?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.04.2021, 08:54
    #40064069
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Удаление строки в связанных таблицах
Strength Class
Фото в прилагаемой картинке.
Мы не лечим по фотографиям. Приведите CREATE TABLE обеих таблиц. А ещё - код следует форматировать, а не давать нечитаемой "лапшой".

Кстати, а зачем триггер, если уже есть каскадное удаление?

И, если надо чистить "зависшие" записи, то это следует делать не вместо удаления, а после него.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.04.2021, 09:12
    #40064071
Strength Class
Strength Class
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Удаление строки в связанных таблицах
Akina,

Для первой таблицы
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
USE [ztt]
GO

/****** Object:  Table [dbo].[DataVals]    Script Date: 20.04.2021 10:08:53 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[DataVals](
	[idDataVals] [int] IDENTITY(1,1) NOT NULL,
	[strCipher] [nvarchar](50) NOT NULL,
	[dblDiameter] [decimal](6, 3) NOT NULL,
	[idTolerance] [int] NULL,
	[dblDiameterInner] [decimal](6, 3) NOT NULL,
	[dblLength] [decimal](7, 2) NOT NULL,
	[idStealMarka] [int] NOT NULL,
	[idValType] [int] NULL,
	[dblMass] [decimal](5, 2) NULL,
	[dblMassWorkPiece] [decimal](5, 2) NULL,
	[dblKIM]  AS ([dbo].[fnCalcKIM]([dblMass], [dblMassWorkPiece])),
	[strComment] [nvarchar](50) NULL,
	[idValMainPrice] [int] NULL,
	[bIsOtkEntered] [bit] NOT NULL,
	[strSortOrder1]  AS ([dbo].[AdjustStrCipher1]([strCipher])),
	[nSortOrder2]  AS ([dbo].[AdjustStrCipher2]([strCipher])),
	[idDataValAnalog] [int] NULL,
	[dtCreation] [datetime] NOT NULL,
	[strCipherConstrain]  AS (convert(nvarchar(50),rtrim(ltrim(replace(replace(replace([strCipher],char(9),''),char(10),''),char(13),''))))),
	[idShaftSpline] [int] NOT NULL,
	[strDataValAnalog] [nvarchar](50) NULL,
	[strTrebZakazchika] [nvarchar](50) NULL,
 CONSTRAINT [PK_DataVals] PRIMARY KEY CLUSTERED 
(
	[idDataVals] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [IX_DataVals_5] UNIQUE NONCLUSTERED 
(
	[strCipher] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[DataVals] ADD  CONSTRAINT [DF_DataVals_bIsOtkEntered]  DEFAULT (0) FOR [bIsOtkEntered]
GO

ALTER TABLE [dbo].[DataVals] ADD  CONSTRAINT [DF_DataVals_dtCreation]  DEFAULT (getdate()) FOR [dtCreation]
GO

ALTER TABLE [dbo].[DataVals]  WITH NOCHECK ADD  CONSTRAINT [FK_DataVals_ValsPrices] FOREIGN KEY([idValMainPrice])
REFERENCES [dbo].[ValsPrices] ([idValPrice])
GO

ALTER TABLE [dbo].[DataVals] CHECK CONSTRAINT [FK_DataVals_ValsPrices]
GO



Для второй таблицы:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
/****** Object:  Table [dbo].[ValsPrices]    Script Date: 20.04.2021 10:10:58 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ValsPrices](
	[idValPrice] [int] IDENTITY(1,1) NOT NULL,
	[idDataVals] [int] NOT NULL,
	[idZakazchik] [int] NOT NULL,
	[dtPrice] [datetime] NOT NULL,
	[curPrice] [money] NOT NULL,
	[idStealMarka] [int] NOT NULL,
 CONSTRAINT [PK_ValsPrices] PRIMARY KEY CLUSTERED 
(
	[idValPrice] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [IX_ValsPrices_3] UNIQUE NONCLUSTERED 
(
	[idDataVals] ASC,
	[idZakazchik] ASC,
	[dtPrice] ASC,
	[idStealMarka] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ValsPrices] ADD  CONSTRAINT [DF_ValsPrices_dtPrice]  DEFAULT (getdate()) FOR [dtPrice]
GO

ALTER TABLE [dbo].[ValsPrices]  WITH NOCHECK ADD  CONSTRAINT [FK_ValsPrices_DataVals] FOREIGN KEY([idDataVals])
REFERENCES [dbo].[DataVals] ([idDataVals])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[ValsPrices] CHECK CONSTRAINT [FK_ValsPrices_DataVals]
GO
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.04.2021, 10:12
    #40064089
PaulWist
PaulWist
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Удаление строки в связанных таблицах
Strength Class,

У Вас перекрестные ссылки, таблицы ссылаются друг на друга.

Таблица [dbo].[DataVals] ссылается на [dbo].[ValsPrices]

Код: sql
1.
2.
ALTER TABLE [dbo].[DataVals]  WITH NOCHECK ADD  CONSTRAINT [FK_DataVals_ValsPrices] FOREIGN KEY([idValMainPrice])
REFERENCES [dbo].[ValsPrices] ([idValPrice])



Талица [dbo].[ValsPrices] ссылается на [dbo].[DataVals]

Код: sql
1.
2.
3.
4.
5.
ALTER TABLE [dbo].[ValsPrices]  WITH NOCHECK ADD  CONSTRAINT [FK_ValsPrices_DataVals] FOREIGN KEY([idDataVals])
REFERENCES [dbo].[DataVals] ([idDataVals])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.04.2021, 08:57
    #40064345
Strength Class
Strength Class
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Удаление строки в связанных таблицах
PaulWist,

Перекрестные ссылки удалены, но ошибка осталась та же.

Первая таблица:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
/****** Object:  Table [dbo].[DataVals]    Script Date: 21.04.2021 9:52:14 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[DataVals](
	[idDataVals] [int] IDENTITY(1,1) NOT NULL,
	[strCipher] [nvarchar](50) NOT NULL,
	[dblDiameter] [decimal](6, 3) NOT NULL,
	[idTolerance] [int] NULL,
	[dblDiameterInner] [decimal](6, 3) NOT NULL,
	[dblLength] [decimal](7, 2) NOT NULL,
	[idStealMarka] [int] NOT NULL,
	[idValType] [int] NULL,
	[dblMass] [decimal](5, 2) NULL,
	[dblMassWorkPiece] [decimal](5, 2) NULL,
	[dblKIM]  AS ([dbo].[fnCalcKIM]([dblMass], [dblMassWorkPiece])),
	[strComment] [nvarchar](50) NULL,
	[idValMainPrice] [int] NULL,
	[bIsOtkEntered] [bit] NOT NULL,
	[strSortOrder1]  AS ([dbo].[AdjustStrCipher1]([strCipher])),
	[nSortOrder2]  AS ([dbo].[AdjustStrCipher2]([strCipher])),
	[idDataValAnalog] [int] NULL,
	[dtCreation] [datetime] NOT NULL,
	[strCipherConstrain]  AS (convert(nvarchar(50),rtrim(ltrim(replace(replace(replace([strCipher],char(9),''),char(10),''),char(13),''))))),
	[idShaftSpline] [int] NOT NULL,
	[strDataValAnalog] [nvarchar](50) NULL,
	[strTrebZakazchika] [nvarchar](50) NULL,
 CONSTRAINT [PK_DataVals] PRIMARY KEY CLUSTERED 
(
	[idDataVals] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [IX_DataVals_5] UNIQUE NONCLUSTERED 
(
	[strCipher] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[DataVals] ADD  CONSTRAINT [DF_DataVals_bIsOtkEntered]  DEFAULT (0) FOR [bIsOtkEntered]
GO

ALTER TABLE [dbo].[DataVals] ADD  CONSTRAINT [DF_DataVals_dtCreation]  DEFAULT (getdate()) FOR [dtCreation]
GO

ALTER TABLE [dbo].[DataVals]  WITH NOCHECK ADD  CONSTRAINT [FK_DataVal_ValPrices] FOREIGN KEY([idValMainPrice])
REFERENCES [dbo].[ValsPrices] ([idValPrice])
GO

ALTER TABLE [dbo].[DataVals] CHECK CONSTRAINT [FK_DataVal_ValPrices]
GO

ALTER TABLE [dbo].[DataVals]  WITH NOCHECK ADD  CONSTRAINT [FK_DataVals_ValsPrices] FOREIGN KEY([idValMainPrice])
REFERENCES [dbo].[ValsPrices] ([idValPrice])
GO

ALTER TABLE [dbo].[DataVals] CHECK CONSTRAINT [FK_DataVals_ValsPrices]
GO



Вторая таблица:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ValsPrices](
	[idValPrice] [int] IDENTITY(1,1) NOT NULL,
	[idDataVals] [int] NOT NULL,
	[idZakazchik] [int] NOT NULL,
	[dtPrice] [datetime] NOT NULL,
	[curPrice] [money] NOT NULL,
	[idStealMarka] [int] NOT NULL,
 CONSTRAINT [PK_ValsPrices] PRIMARY KEY CLUSTERED 
(
	[idValPrice] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [IX_ValsPrices_3] UNIQUE NONCLUSTERED 
(
	[idDataVals] ASC,
	[idZakazchik] ASC,
	[dtPrice] ASC,
	[idStealMarka] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ValsPrices] ADD  CONSTRAINT [DF_ValsPrices_dtPrice]  DEFAULT (getdate()) FOR [dtPrice]
GO
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.04.2021, 10:16
    #40064364
PaulWist
PaulWist
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Удаление строки в связанных таблицах
Strength Class,

Смотрите, что делаете:

1. Удаляете из [dbo].[DataVals]
Код: sql
1.
2.
3.
DELETE
FROM [ztt].[dbo].[DataVals]
WHERE ([idDataVals]=2006)



2. Срабатывает триггер [dbo].[DeleteTrigger] ON [dbo].[DataVals], в котором происходит удаление из [dbo].[DataVals]

Код: sql
1.
DELETE DataVals WHERE idDataVals=@idDataVal;



НО, у таблицы [dbo].[ValsPrices] есть FK на [dbo].[DataVals]

Код: sql
1.
2.
3.
ALTER TABLE [dbo].[DataVals]  WITH NOCHECK ADD  CONSTRAINT [FK_DataVal_ValPrices] FOREIGN KEY([idValMainPrice])
REFERENCES [dbo].[ValsPrices] ([idValPrice])
GO



Таким образом, при удалении из [dbo].[DataVals] в удаляемых записях есть значение поля [dbo].[DataVals].[idValPrice], на которое ссылается [dbo].[ValsPrices].[idValPrice], отсюда ошибка.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.04.2021, 10:56
    #40064388
Strength Class
Strength Class
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Удаление строки в связанных таблицах
PaulWist,

Т.е., я так понимаю, мне нужно удалить внешний ключ и оставить триггер, либо оставить внешний ключ, сделать каскадное удаление и удалить триггер?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.04.2021, 11:59
    #40064408
PaulWist
PaulWist
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Удаление строки в связанных таблицах
Strength Class
PaulWist,

Т.е., я так понимаю, мне нужно удалить внешний ключ и оставить триггер, либо оставить внешний ключ, сделать каскадное удаление и удалить триггер?


Ммм, всё зависит от бизнес логики, если удалить FK, то получим "висящие" ссылки.

Вы лучше разберитесь, почему при удалении происходит такая ситуация, обычно так бывает, когда разработчик думает, что данные лежат определенным образом, а на самом деле это не так.

PS В принципе, достаточно обNULLить ValsPrices.idValPrice на которые ссылается DataVals.idValMainPrice - это я рассказываю как не надо делать!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.04.2021, 07:38
    #40064608
Strength Class
Strength Class
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Удаление строки в связанных таблицах
PaulWist,
большое спасибо. Попробую доразобраться.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Удаление строки в связанных таблицах / 9 сообщений из 9, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 431ms
Закрыть
start [/forum/topic.php?fid=46&mobile=1&tid=1684790]:
 0ms
get settings:
 8ms
get forum list:
 12ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 30ms
get topic data:
 9ms
get forum data:
 2ms
get page messages:
 41ms
get tp. blocked users:
 1ms
others: 322ms
total:  431ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]