[PL] SQL Server 2012 SP4 - jak wykryć fakt zmiany danych w tabeli i zapisać informacje na ten temat w bazie danych?

Dzisiaj, korzystając z SQL Servera, spróbujemy zarejestrować w bazie danych informacje o tym, że dane w wybranej kolumnie w określonej tabeli zostały zmodyfikowane. 

Zacznijmy od utworzenia tabeli testowej korzystając z poniższego kodu:

USE BazaTestowa

GO

CREATE TABLE dbo.Osoby (

Id BIGINT IDENTITY(1,1)

               NOT NULL

               PRIMARY KEY,

Imie nvarchar(200) NOT NULL,

Nazwisko nvarchar(200) NOT NULL,

DataUrodzenia date NOT NULL,

Plec char(1) NOT NULL,

Miasto varchar(100) NOT NULL,

Kraj varchar(20) NOT NULL,

Przeprowadzka bit,

PrzeprowadzkaData date

);

Jak widzimy powyżej, nasza testowa tabela:

• została utworzona w schemacie dbo
• nosi nazwę Osoby
• została stworzona w celu ewidencjonowania danych o osobach, w tym: imienia (kolumna Imie), nazwiska (kolumna Nazwisko), daty urodzenia (kolumna DataUrodzenia), płci (kolumna Plec), miasta (kolumna Miasto), kraju (kolumna Kraj)
• w kolumnie Plec pozwala na zapisywanie danych na temat płci zgodnie ze standardem PN-ISO 5218:
• 0 – płeć nieokreślona
• 1 – mężczyzna
• 2 - kobieta
• 9 – nieznana
• zawiera kolumny Przeprowadza i PrzeprowadzkaData w których zapisywać będziemy informacje o fakcie zmiany adresu przez osobę
• zawiera kolumnę ID, która jest kluczem podstawowym i wartości - na potrzeby tej kolumny - są generowanie automatycznie dla każdego wiersza, zaczynając od 1 i wzrastając co 1

Załadujmy dane do naszej testowej tabeli:

INSERT INTO dbo.Osoby (Imie, Nazwisko, DataUrodzenia, Plec, Miasto, Kraj)

VALUES (N'Jan',N'Kowalski', '1990-01-01', '1', 'Warszawa', 'Polska');

INSERT INTO dbo.Osoby (Imie, Nazwisko, DataUrodzenia, Plec, Miasto, Kraj)

VALUES (N'Anna',N'Kowalska', '1980-01-01', '1', 'Szczecin', 'Polska');

INSERT INTO dbo.Osoby (Imie, Nazwisko, DataUrodzenia, Plec, Miasto, Kraj)

VALUES (N'Tadeusz',N'Wiśniewski', '1984-01-01', '1', 'Kraków', 'Polska');

Sprawdźmy, że tabela dbo.Osoby zawiera dodane przed chwilą dane – powinny znajdować się w niej trzy rekordy. Zwróćmy uwagę na kolumny Przeprowadzka i PrzeprowadzkaData – nie mają one żadnej wartości.

SELECT * FROM dbo.Osoby;

Kolejnym krokiem jest utworzenie triggera, czyli wyzwalacza, który:

• będzie włączał się po każdej operacji aktualizacji danych w tabeli dbo.Osoby
• będzie sprawdzać czy zmianie uległy dane w kolumnie Miasto lub Kraj
• jeżeli dane w kolumnie Miasto lub Kraj zostają zmienione, to kolumna Przeprowadzka zostanie uzupełniona wartością 1, a PrzeprowadzkaData – bieżącą datą

CREATE TRIGGER dbo.OsobyAfterUpdate01

ON dbo.Osoby

AFTER UPDATE

AS

IF ( UPDATE (MIASTO) OR UPDATE (KRAJ) ) 

BEGIN

SET NOCOUNT ON;

UPDATE dbo.Osoby SET

Przeprowadzka = 1,

PrzeprowadzkaData = GETDATE()

from deleted d, dbo.Osoby o

where d.Id = o.Id

END

GO

Czas na końcowe testy – sprawdźmy działanie triggera. Aby to zrobić, zaktualizujmy dane w kolumnie Miasto dla wiersza z identyfikatorem 2, a następnie wyświetlmy aktualną zawartość tabeli dbo.Osoby.

UPDATE dbo.Osoby

SET Miasto = 'Gdańsk'

WHERE Id = 2;

SELECT * FROM dbo.Osoby;

Wynik powinien być następujący:

Czyli pożądana zmiana została wprowadzona (wartość kolumny Miasto została zmieniona), a ponadto trigger zadziałał prawidłowo – dla rekordu o identyfikatorze 2 została zaktualizowana zawartość kolumny Przeprowadzka i PrzeprowadzkaData.

Żeby mieć pewność, że wszystko działa prawidłowo, zróbmy jeszcze jeden test, który upewni nas, że trigger nie zadziała w przypadku aktualizacji danych w innej kolumnie niż Miasto lub Kraj, np. Plec. Zaktualizujmy zatem dla rekordu o identyfikatorze 3 wartość kolumny Plec z 1 (mężczyzna) na 9 (nieznana) i sprawdźmy aktualną zawartość tabeli dbo.Osoby.

UPDATE dbo.Osoby

SET Plec = '9'

WHERE Id = 3;

SELECT * FROM dbo.Osoby;

W tym przypadku również widzimy, że wszystko zadziałało prawidłowo, ponieważ dla rekordu o identyfikatorze 3 kolumny Przeprowadzka i PrzeprowadzkaData pozostały bez wartości, czyli trigger nie zaktualizował niepotrzebnie kolumn Przeprowadzka i PrzeprowadzkaData - stało się tak za sprawą warunku IF ( UPDATE (MIASTO) OR UPDATE (KRAJ) )  zawartego w triggerze, gdyż funkcja UPDATE służy do wykrywania zmian na określonej kolumnie.