środa, 27 lipca 2011

DELETE z klauzulą OUTPUT a integralność danych

Dzisiaj natrafiłem na ciekawą właściwość klauzuli OUTPUT. Miałem za zadanie napisać procedurę, która usuwa rekord z jednej tabeli i zapisuje go to innej. Prosty audyt, bez wykorzystywania triggerów.

Problem trywialny. Stworzyłem sobie tabelę do której zapisywane są usuwane recordy. Do tego dodałem kilka kluczy obcych. W zapytaniu, które usuwa recordy dodałem klauzulę OUTPUT, tak by zapisać skasowane dane do mojej nowej tabeli. Proste, nieprawdaż? Otóż nie. W momencie uruchomienia mojej procedure od razu sie ona wysypała. Niestety w klauzuli OUTPUT ... INSERT nie może występować tabela, która jest powiązana z innymi tabelami kluczami obcymi. I co tu wybrać teraz integralnośćdanych czy wygodę?

Wole integralność mimo wszystko kosztem bardziej złożonego zapytania. Tyle teorii, teraz praktyka. Poniższy przykład w bardzo prosty sposób pokazuje istotę problemu.

Najpierw tabele:

USE TEMPDB
GO

CREATE TABLE PRODUCTS (
ID INT IDENTITY NOT NULL PRIMARY KEY,
[DESCRIPTION] NVARCHAR(100)
)
GO

CREATE TABLE PROFILES (
PROFILE_ID INT IDENTITY NOT NULL PRIMARY KEY,
FULLNAME NVARCHAR(100)
)
GO

CREATE TABLE PRODUCTS_ARCHIVE (
ID INT IDENTITY NOT NULL PRIMARY KEY,
[DESCRIPTION] VARCHAR(100),
DELETEDBY INT,
ORIGINAL_ID INT
)
GO

Teraz trochę danych testowych.

INSERT INTO PRODUCTS([DESCRIPTION])
SELECT 'Brick' UNION
SELECT 'Knife' UNION
SELECT 'Spanner' UNION
SELECT 'Nails'
GO

INSERT INTO PROFILES (FULLNAME)
SELECT 'John Peacock' UNION
SELECT 'Steven Smith' UNION
SELECT 'Jason Brown'
GO

Teraz dwie procedure, które pomogą zobrazować problem.
CREATE PROCEDURE SP_DELETE_PRODUCT
@ID INT,
@DELETEDBY INT
AS
DELETE FROM PRODUCTS
OUTPUT DELETED.[DESCRIPTION], @DELETEDBY, DELETED.ID INTO PRODUCTS_ARCHIVE([DESCRIPTION],DELETEDBY,ORIGINAL_ID)
WHERE ID = @ID
GO

CREATE PROCEDURE SP_DELETE_PRODUCT_ALT
@ID INT,
@DELETEDBY INT
AS
DECLARE @TAB TABLE (
[DESCRIPTION] VARCHAR(100),
ORIGINAL_ID INT
)

DELETE FROM PRODUCTS
OUTPUT DELETED.[DESCRIPTION], DELETED.ID INTO @TAB([DESCRIPTION],ORIGINAL_ID)
WHERE ID = @ID
INSERT INTO PRODUCTS_ARCHIVE([DESCRIPTION],DELETEDBY,ORIGINAL_ID)
SELECT [DESCRIPTION], @DELETEDBY, ORIGINAL_ID FROM @TAB
GO

Żadna z tabel nie jest powiązana kluczami obcymi, przeważnie niezbyt dobre to rozwiązanie.

EXEC SP_DELETE_PRODUCT 1,1

Wykonując pierwszą procedurę, która wykonuje zadanie z wykorzystaniem zaledwie 3 lini kodu otrzymujemy pożądany rezultat. Rekord został usunięty z tabeli PRODUCTS i dodany do PRODUCTS_ARCHIVE. Wszystko pięknie, ładnie i przyjemnie ale przydałoby się zapewnienie trochę integralności danch.

W tym celu połączymy tabele PROFILES and PRODUCTS_ARCHIVE kluczem obcym.

ALTER TABLE PRODUCTS_ARCHIVE
ADD CONSTRAINT FK_P_PA FOREIGN KEY(DELETEDBY) REFERENCES PROFILES(PROFILE_ID)
GO

Teraz poniższe zapytanie zwróci błąd.

EXEC SP_DELETE_PRODUCT 2,1

Msg 332, Level 16, State 1, Procedure SP_DELETE_PRODUCT, Line 6
The target table 'PRODUCTS_ARCHIVE' of the OUTPUT INTO clause cannot be on either side of a (primary key, foreign key) relationship. Found reference constraint 'FK_P_PA'.

Niestety DELETE ... INTO nie lubi relacji między tabelami.

Rozwiązaniem problemu w tym przypadku jest utworzenie tabeli tymczasowej lub zmiennej w celu przechowania usuwanych danych.

Problem można rozwiązać na dwa sposoby. Albo wykonać zapytanie SELECT, które skopiuje usuwane dane do tabeli tymczasowej, przed wykonaniem polecenia DELETE, albo też skorzystać z klauzuli OUTPUT ... INTO.

Mając już usunięte dane z pierwszej tabeli, należy teraz skopiować zawartość tabeli tymczasowej do tabeli docelowej.

Te rozwiązanie wykorzystuje druga procedura, poniższe zapytanie wykona sie bez błędów.

EXEC SP_DELETE_PRODUCT_ALT 2,1

Na koniec jeszcze dla formalności troche kodu czyszczącego utworzone obiekty.

DROP PROCEDURE SP_DELETE_PRODUCT
GO
DROP PROCEDURE SP_DELETE_PRODUCT_ALT
GO
DROP TABLE PRODUCTS_ARCHIVE
GO
DROP TABLE PRODUCTS
GO
DROP TABLE PROFILES
GO

One Response so far

  • Anonimowy says:

    Fajny artykuł, podoba mi się :) Pozdrawiam

  • Prześlij komentarz