Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 INSTEAD OF DELETE  [new]
Dmitry V. Liseev
Member [заблокирован]

Откуда: Санкт-Петербург
Сообщений: 5489
Хотелось, чтобы при удалении строки в таблице "A" каскадом удалялись все строки из таблицы "B", которые на нее ссылаются.
Сделал триггер.
CREATE TABLE [A]
(
	[Id]			[int]			IDENTITY(1,1) NOT NULL,

	CONSTRAINT [PK_A]				PRIMARY KEY CLUSTERED
	(
		[Id] ASC
	)
	ON [PRIMARY]

) ON [PRIMARY];
GO

CREATE TRIGGER [IOD_A] ON [A]
INSTEAD OF DELETE AS
BEGIN
	SET NOCOUNT ON;
	DELETE [B] WHERE [B].[Id] IN
	(
		SELECT [B].[Id] FROM [B]
		INNER JOIN [deleted] ON [B].[ARef1] = [deleted].[Id]
	UNION
		SELECT [B].[Id] FROM [B]
		INNER JOIN [deleted] ON [B].[ARef2] = [deleted].[Id]
	);
	DELETE [A] FROM [deleted];
END;
GO

CREATE TABLE [B]
(
	[Id]			[int]			IDENTITY(1,1) NOT NULL,
	[ARef1]			[int]			NOT NULL,
	[ARef2]			[int]			NOT NULL,

	CONSTRAINT [FK_B_ARef1]				FOREIGN KEY
	(
		[ARef1]
	)
	REFERENCES [A]
	(
		[Id]
	)
	/* ON DELETE CASCADE */,

	CONSTRAINT [FK_B_ARef2]				FOREIGN KEY
	(
		[ARef2]
	)
	REFERENCES [A]
	(
		[Id]
	)
	/* ON DELETE CASCADE */

) ON [PRIMARY];
GO

INSERT INTO [A] DEFAULT VALUES;
INSERT INTO [B] ( [ARef1], [ARef2] ) VALUES ( @@IDENTITY, @@IDENTITY );
GO

INSERT INTO [A] DEFAULT VALUES;
INSERT INTO [B] ( [ARef1], [ARef2] ) VALUES ( @@IDENTITY, @@IDENTITY );
GO

SELECT * FROM [A];
SELECT * FROM [B];
GO

DELETE [A] WHERE [Id] = 1;
GO

SELECT * FROM [A];
SELECT * FROM [B];
GO


Пишет:

The DELETE statement conflicted with the REFERENCE constraint "FK_B_ARef1". The conflict occurred in database "test", table "dbo.B", column 'ARef1'.
The statement has been terminated.

Причем при удалении всех записей (DELETE [A]) все работает и удаляется из обеих таблиц.

В чем косяк?
16 дек 11, 20:18    [11780365]     Ответить | Цитировать Сообщить модератору
 Re: INSTEAD OF DELETE  [new]
Glory
Member

Откуда:
Сообщений: 104751
Dmitry V. Liseev
В чем косяк?

в том, что у вас неправильно сделаны FK.
Два разных поля таблицы В ссылаются на одно поле таблицы А.
16 дек 11, 20:34    [11780409]     Ответить | Цитировать Сообщить модератору
 Re: INSTEAD OF DELETE  [new]
Dmitry V. Liseev
Member [заблокирован]

Откуда: Санкт-Петербург
Сообщений: 5489
Glory
Dmitry V. Liseev
В чем косяк?

в том, что у вас неправильно сделаны FK.
Два разных поля таблицы В ссылаются на одно поле таблицы А.
А разве так нельзя делать? Они могут ссылаться на разные записи в таблице A, а могут и на одну. Например, домработница и любовница. Могут быть разными женщинами, а могут быть одной и той-же.
16 дек 11, 20:39    [11780428]     Ответить | Цитировать Сообщить модератору
 Re: INSTEAD OF DELETE  [new]
Dmitry V. Liseev
Member [заблокирован]

Откуда: Санкт-Петербург
Сообщений: 5489
Делаю черех триггер, ибо ON DELETE CASCADE серверу не нравится. Он пишет:

Introducing FOREIGN KEY constraint 'FK_B_ARef2' on table 'B' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

Чего не так в триггерном коде? Я "руками" пытаюсь удалять записи из "B", а потом записи из "A".
16 дек 11, 20:45    [11780455]     Ответить | Цитировать Сообщить модератору
 Re: INSTEAD OF DELETE  [new]
Dmitry V. Liseev
Member [заблокирован]

Откуда: Санкт-Петербург
Сообщений: 5489
Если закомментировать DELETE [A] FROM [deleted], то ошибок нет, и из таблицы "B" удаляется, что требуется.
16 дек 11, 20:49    [11780472]     Ответить | Цитировать Сообщить модератору
 Re: INSTEAD OF DELETE  [new]
Glory
Member

Откуда:
Сообщений: 104751
Dmitry V. Liseev
Чего не так в триггерном коде? Я "руками" пытаюсь удалять записи из "B", а потом записи из "A".

Что-то не так с вашей структурой.
Поэтому не получаются ни каскады, ни триггера
16 дек 11, 20:58    [11780506]     Ответить | Цитировать Сообщить модератору
 Re: INSTEAD OF DELETE  [new]
Dmitry V. Liseev
Member [заблокирован]

Откуда: Санкт-Петербург
Сообщений: 5489
Glory
Что-то не так с вашей структурой.
Поэтому не получаются ни каскады, ни триггера
Вы хотите сказать, что любовница не может быть одновременно и домработницей?

Выяснил, что если
DELETE [A] FROM [deleted];
заменить на
DELETE [A] WHERE [Id] IN (SELECT [Id] FROM [deleted]);
все работает, как надо. Но ведь по смыслу это одно и то-же?

Использую:

Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64)
Sep 16 2010 19:43:16
Copyright (c) 1988-2008 Microsoft Corporation
Express Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (VM)
16 дек 11, 21:05    [11780527]     Ответить | Цитировать Сообщить модератору
 Re: INSTEAD OF DELETE  [new]
Glory
Member

Откуда:
Сообщений: 104751
Dmitry V. Liseev
Выяснил, что если
DELETE [A] FROM [deleted];
заменить на
DELETE [A] WHERE [Id] IN (SELECT [Id] FROM [deleted]);
все работает, как надо. Но ведь по смыслу это одно и то-же?

И где же в первом примере связь между таблицами А и deleted ?
16 дек 11, 21:10    [11780544]     Ответить | Цитировать Сообщить модератору
 Re: INSTEAD OF DELETE  [new]
Dmitry V. Liseev
Member [заблокирован]

Откуда: Санкт-Петербург
Сообщений: 5489
Glory
И где же в первом примере связь между таблицами А и deleted ?
Триггер срабатывает на удаление из таблицы A. Следовательно, у А и deleted одинаковая структура.

http://msdn.microsoft.com/ru-ru/library/ms189835.aspx

FROM <table_source>
Задает дополнительное предложение FROM. Это расширение языка Transact-SQL для инструкции DELETE позволяет задавать данные из <table_source> и удалять соответствующие строки из таблицы в первом предложении FROM.

Это расширение, в котором задается соединение, может быть использовано вместо вложенного запроса в предложении WHERE для указания удаляемых строк.
16 дек 11, 21:15    [11780568]     Ответить | Цитировать Сообщить модератору
 Re: INSTEAD OF DELETE  [new]
Glory
Member

Откуда:
Сообщений: 104751
Dmitry V. Liseev
Триггер срабатывает на удаление из таблицы A. Следовательно, у А и deleted одинаковая структура.

Причем тут структуру ?
Как _соединяются_ две таблицы в запросе ?
16 дек 11, 21:17    [11780572]     Ответить | Цитировать Сообщить модератору
 Re: INSTEAD OF DELETE  [new]
Dmitry V. Liseev
Member [заблокирован]

Откуда: Санкт-Петербург
Сообщений: 5489
Glory
Как _соединяются_ две таблицы в запросе ?
CREATE TABLE C (Id int);
CREATE TABLE D (Id int);
GO

INSERT INTO C (Id) VALUES(5);
INSERT INTO C (Id) VALUES(7);
INSERT INTO C (Id) VALUES(9);

INSERT INTO D (Id) VALUES(4);
INSERT INTO D (Id) VALUES(7);
INSERT INTO D (Id) VALUES(12);

DELETE C FROM D;

SELECT * FROM C;
GO

Действительно, удаляет все строки из таблицы "C". Тогда в чем глубинный смысл этого предложения FROM?
16 дек 11, 21:29    [11780607]     Ответить | Цитировать Сообщить модератору
 Re: INSTEAD OF DELETE  [new]
Glory
Member

Откуда:
Сообщений: 104751
Dmitry V. Liseev
Действительно, удаляет все строки из таблицы "C". Тогда в чем глубинный смысл этого предложения FROM?

А наличие FROM освобождает от написания JOIN ?
16 дек 11, 21:32    [11780612]     Ответить | Цитировать Сообщить модератору
 Re: INSTEAD OF DELETE  [new]
Dmitry V. Liseev
Member [заблокирован]

Откуда: Санкт-Петербург
Сообщений: 5489
Dmitry V. Liseev
Действительно, удаляет все строки из таблицы "C". Тогда в чем глубинный смысл этого предложения FROM?
DELETE C FROM D INNER JOIN C ON D.Id=C.Id;
Вон оно чего!
16 дек 11, 21:33    [11780616]     Ответить | Цитировать Сообщить модератору
 Re: INSTEAD OF DELETE  [new]
Dmitry V. Liseev
Member [заблокирован]

Откуда: Санкт-Петербург
Сообщений: 5489
Glory
А наличие FROM освобождает от написания JOIN ?
Я думал "Это расширение языка Transact-SQL" само догадается.
16 дек 11, 21:34    [11780622]     Ответить | Цитировать Сообщить модератору
 Re: INSTEAD OF DELETE  [new]
Glory
Member

Откуда:
Сообщений: 104751
Dmitry V. Liseev
Я думал "Это расширение языка Transact-SQL" само догадается.

Особенно если написать FROM B, C, D, E
Сервер сразу должен составить правильные соединения
16 дек 11, 21:43    [11780642]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить