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

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

Komentarze są wyłączone