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.
