Trigger INSTEAD OF INSERT. Problem ze wstawieniem rekordu do widoku wykorzystującego JOIN

28-Cze-2011

Problem: administrator musi wypełnić tabele opisującą komputery w jego sieci. Aplikacja została tak zaprojektowana, że w bazie danych istnieją 2 tabele.  Na dodatek do obu tabel trzeba wpisać ten sam ID (relacja 1 do 1). Łatwiej byłoby zrobić to poprzez widok, ale z tym widokiem nie jest tak łatwo. SQL Serwer nie potrafi wstawiać rekordów poprzez widok zbudowany jako join z dwóch tabel… Na całe szczęście mamy przecież triggery instead of insert.

Dla uproszczenia, wyciągam tutaj tylko fragment z tych tabel:

CREATE DATABASE Test;
GO
 
 USE Test;
GO
 
 CREATE TABLE ComputerIdentity
(
      id INT IDENTITY primary key,
      description VARCHAR(100),
      macaddress VARCHAR(100)
);
GO
 
 CREATE TABLE Settings
(
      id INT FOREIGN KEY REFERENCES ComputerIdentity(id),
      Type VARCHAR(100),
      OSInstall VARCHAR(100),
      OSDComputerName VARCHAR(100)
);
GO

 Wpisując rekord do ComputerIdentity, należy także wpisać rekord do Settings. Kolumną wiążącą obie tabele ze sobą za pomocą klucza obcego jest kolumna id.

Aby wygodniej przeglądać rekordy został utworzony widok, łączący kolumny z obu tabel:

CREATE VIEW NamesView
AS
SELECT
      ci.id AS CI_ID
      ,ci.description
      ,ci.macaddress
      ,s.Type
      ,s.OSInstall
      ,s.OSDComputerName
FROM dbo.ComputerIdentity AS ci
      LEFT JOIN dbo.Settings AS s
            ON s.Id = ci.ID;
GO

 Teraz można już pisać proste zapytanie do tego widoku i przeglądać dane połączone, jakby pochodziły z jednej tabeli.

SELECT * FROM NamesView;
GO

 Jeżeli jednak zechcesz wpisać nowy rekord poprzez INSERT do tego widoku

INSERT INTO NamesView
VALUES(‚description’,’mac’,’type’,’os’,’osd name’)
GO

 To otrzymasz błąd:

Msg 4405, Level 16, State 1, Line 1
View or function ‚NamesView’ is not updatable because the modification affects multiple base tables.

 Rzeczywiście, widok składa się z dwóch tabel I serwer nie wie, jak ma wstawić ten rekord. Z pomocą przychodzi trigger, który ucruchomi się zamiast polecenia insert. Jest to tzw INSTEAD OF INSERT trigger:

CREATE TRIGGER TR_NamesView_Insert ON NamesView INSTEAD OF INSERT
AS
BEGIN
      DECLARE @NumRows INT;
      SELECT @NumRows = COUNT(*) FROM inserted
     
      IF @NumRows >1
            BEGIN
                  RAISERROR(‚Cannot insert more than 1 record at time’,16,1)
                  ROLLBACK
            END
      ELSE
            BEGIN
                  INSERT INTO ComputerIdentity(description,macaddress)
                        SELECT description, macaddress FROM inserted
                 
                  DECLARE @id INT = SCOPE_IDENTITY()
 
                  INSERT INTO Settings(id, Type, OSInstall, OSDComputerName)
                        SELECT @id, Type, OSInstall, OSDComputerName
                        FROM inserted
            END
END  
GO

 Ale po kolei!

Najpierw deklarujesz trigger jako uruchamiający się zamiast polecenia INSERT na widoku:

CREATE TRIGGER TR_NamesView_Insert ON NamesView INSTEAD OF INSERT

 Ponieważ użytkownik będzie wstawiał rekordy ręcznie, a nie poprzez import z innych źródeł i ponieważ dzięki temu uprości się sam trigger, to domagamy się aby na raz był wstawiany tylko jeden rekord. Najpierw więc zapamiętujemy ilość wstawionych rekordów:

      DECLARE @NumRows INT;
      SELECT @NumRows = COUNT(*) FROM inserted

 Jeżeli ta ilość jest większa niż jeden, to kończymy wstawianie rekordu błędem:

      IF @NumRows >1
            BEGIN
                  RAISERROR(‚Cannot insert more than 1 record at time’,16,1)
                  ROLLBACK
            END

 W przeciwnym zaś razie, przepisujemy dane do tabel. Najpierw do pierwszej tabeli. Jest to polecenie insert przepisujące wybrane z tabeli inserted rekordy do tabeli ComputerIdentity:

INSERT INTO ComputerIdentity(description,macaddress)
                        SELECT description, macaddress FROM inserted

 Aby w tabeli Settings poprawnie wypełnić kolumnę id, potrzebny jest wygenerowany przez serwer za sprawą IDENTITY numer id w tabeli ComputerIdentity:

                  DECLARE @id INT = SCOPE_IDENTITY()

 Teraz można już wypełnić drugą tabelę. Korzystamy tutaj ze znanego już @id oraz kolumn z tabeli inserted.

                  INSERT INTO Settings(id, Type, OSInstall, OSDComputerName)
                        SELECT @id, Type, OSInstall, OSDComputerName
                        FROM inserted

 Teraz można już spróbować polecenia INSERT na widoku:

INSERT INTO NamesView(description,macaddress,Type,OSInstall,OSDComputerName)
VALUES(‚description’,’mac’,’type’,’os’,’osd name’)
GO

Okazuje się, że działa i wypełnia tabele, na których oparty jest widok.

SELECT * FROM NamesView
SELECT * FROM ComputerIdentity
SELECT * FROM Settings

Więcej na temat programowania triggerów opowiada szkolenie 6232

Dodaj komentarz:

Autor: Rafał Kraik