2008.10_Wyzwalacze w aplikacjach biznesowych_[Aplikacje Biznesowe].pdf

Aplikacje biznesowe

Wyzwalacze w

aplikacjach biznesowych

SQL Server 2005/2008

W artykule zostaną omówione kwestie związane z wykorzystaniem wyzwalaczy

w aplikacjach biznesowych – triki pozwalające na nieuruchamianie wyzwalacza

dla określonych wierszy oraz niezwykle istotną z punktu widzenia aplikacji

biznesowych kwestię wykorzystywania wyzwalaczy na perspektywach.

Dowiesz się:

• Jak używać aplikacji biznesowych;

• Jak zapewnić bezpieczeństwo w aplikacjach

biznesowych używając wyzwalaczy;

• Jakie negatywne skutki w aplikacjach bizneso-

wych może powodować używanie dużej liczby.

Powinieneś wiedzieć:

• Podstawy SQL;

• Podstawowa znajomość SQL Server 2005/

2008.

Ale trzeba pamiętać, że na tabelach tych

nie są założone indeksy, dlatego każde zapy-

tanie SELECT uruchomione na takiej tabeli

oznacza skanowanie całej tabeli. Istnieją oczy-

wiście pewne wyjątki od tej zasady – np. jeśli

użyjemy predykatu TOP wraz z klauzulą ORDER

BY , to serwer nie będzie musiał skanować ca-

łej tabeli.

Tabele INSERTED i DELETED zostały w

odmienny sposób zdefiniowane w SQL Se-

rver 2000 oraz w kolejnych wersjach SQL

Server 2005 i 2008. W SQL Server 2005

i SQL Server 2008 tabele te są widokami

opartymi na sekcji dziennika zdarzeń, któ-

ry zawiera zapis rekordów modyfikowanych

przez instrukcję powodującą uruchomienie

wyzwalacza.

Tak więc każde zapytanie skierowane do

tabel INSERTED lub DELETED oznacza ko-

nieczność skanowania dziennika transakcji,

który zapisywany jest w sposób sekwencyj-

ny. To może okazać się wąskim gardłem dla

systemu biznesowego przetwarzającego du-

żą liczbę transakcji. W SQL Serwer 2005 i

SQL Server 2008 rozwiązano ten problem

poprzez zastosowanie bazy tempdb . Otóż w

SQL Server 2005 i SQL Server 2008 tabele

INSERTED i DELETED wskazują na ozna-

czane numerami wersji wiersze w bazie da-

nych tempdb . Samo oznaczanie numerami

wersji wierszy w tabelach bazy danych jest

nową techniką zastosowaną po raz pierwszy

w SQL Server 2005, która wykorzystywana

jest przy obsługiwaniu nowych poziomów

izolacji obrazów migawkowych, wyzwalaczy,

operacji na indeksach.

Jak już wspomniałem technologia ta po-

zwala na przechowywanie w bazie tempdb

wcześniejszych wersji danych. Na potrze-

by wyzwalaczy zapisywane są dane modyfi-

kowane przez instrukcję wyzwalającą przed

jej uruchomieniem i po jej uruchomieniu.

Z tego też względu tabele INSERTED i DE-

LETED używają bazy tempdb , a nie dzienni-

Poziom trudności

działania triggera, ale także skutki działania

innych instrukcji uruchomionych w danej

transakcji (czyli od momentu jawnego użycia

instrukcji BEGIN TRAN lub od początku wyko-

nywanego kodu w przypadku braku instruk-

cji BEGIN TRAN ).

SQL Server 2005 i SQL Server 2008 po-

zwalają na tworzenie triggerów dla instruk-

cji INSERT , UPDATE , DELETE (czyli dla instruk-

cji DML) oraz dla instrukcji CREATE , ALTER ,

DROP (czyli instrukcji DDL). Natomiast nie

jest możliwe utworzenie wyzwalacza dla in-

strukcji SELECT , a także wyzwalacza urucha-

mianego przed instrukcją (czyli wyzwalacza

typu BEFORE ). Najbardziej zbliżone działanie

do triggera typy BEFORE można uzyskać stosu-

jąc trigger typu INSTEAD OF.

zy danych, które są uruchamia-

ne automatycznie, jako następ-

stwo wystąpienia pewnych zdarzeń na serwe-

rze SQL Server. SQL Server 2005 i SQL Server

2008 obsługują triggery wyzwalane przez in-

strukcje Data Manipulation Language (DML)

oraz instrukcje Data Dafinition Language

(DDL). Ogólna postać triggera została przed-

stawiona w ramce

Triggery, podobnie jak procedury i funk-

cje składowane podlegają następującym fazom

przetwarzania – analiza składniowa, tłumacze-

nie nazw i optymalizacja. Jednak w przeciwień-

stwie do procedur i funkcji składowanych trig-

gery nie mają swojego interfejsu, czyli nie posia-

dają parametrów wejściowych i wyjściowych i

nie można wywoływać ich w sposób jawny. Są

one uruchamiane w sposób automatyczny, ja-

ko reakcja na zdarzenie, które zaszło na serwe-

rze baz danych.

Triggery są również częścią transakcji, która

spowodowała ich uruchomienie. Oznacza to,

że transakcja nie zostanie zakończona, dopóki

nie zakończy się działanie wyzwalacza.

Natomiast, jeśli wewnątrz wyzwalacza

umieszczona zostanie instrukcja ROLLBACK

TRAN , to wycofane zostaną nie tylko skutki

Tabele INSERTED i DELETED

Wewnątrz triggera mamy dostęp do sta-

rej i nowej wersji modyfikowanych wier-

szy – dzieje się tak dzięki tabelom specjal-

nym INSERTED i DELETED . Tabela IN-

SERTED zawiera nowy obraz modyfikowa-

nych wierszy, a tabela DELETED stary ob-

raz. Oczywiście tabela INSERTED będzie

zawierać dane tylko i wyłącznie w przypad-

ku triggerów uruchamianych przez instruk-

cje INSERT i UPDATE . Natomiast dla wyzwala-

czy uruchamianych przez instrukcję DELETE

będzie pusta.

Tabele INSERTED i DELETED mają taką sa-

mą strukturę jak tabele, dla których zdefinio-

wano wyzwalacz.

10/2008

W yzwalacze (triggery) to obiekty ba-

SQL Server 2005/2008

ka transakcji. Dzięki temu wyzwalacze wy-

konywane są efektywniej (na bazie tempdb

możliwe jest wykonywanie wielu działań,

natomiast na dzienniku transakcji możli-

we jest wykonywanie działań w sposób se-

kwencyjny).

Jednak wykonywanie zbyt wielu wyzwa-

laczy w aplikacjach biznesowych może być

przyczyną słabej wydajności takich aplika-

cji. Tabele INSERTED i DELETED nie są in-

deksowane – z tego też względu przy dużej

ilości danych operacje SELECT wykonywa-

ne na tych tabelach mogą trwać długo. Jed-

nak jeśli w aplikacji biznesowej potrzebu-

jemy przeskanować całą tabelę INSERTED

lub DELETED i potrzebne dane można uzy-

skać w pojedynczym zapytaniu, to niewiele

da się zrobić w celu poprawienia wydajno-

ści. Natomiast jeśli musimy korzystać z ta-

beli INSERTED lub DELETED w wielu ite-

racjach, to lepszym rozwiązaniem jest sko-

piowanie danych do innej tabeli (lub tabeli

tymczasowej) za pomocą nielogowanej ope-

racji SELECT INTO i założenie na nowej tabe-

li indeksu.

cyjnie (jeden za drugim). SQL Server 2005

i SQL Server 2008 umożliwiają za pomocą

procedury składowanej sp_settriggerorder

wskazanie pierwszego i ostatniego wyzwala-

cza – kolejność pozostałych wyzwalaczy nie

jest określona.

Głównym zadaniem triggerów typu AFTER

jest reagowanie na zmiany wykonywane na

serwerze baz danych. Można ich także użyć

w odpowiedzi na wykonanie instrukcji wy-

zwalającej np. do zapewnienia reguł integral-

ności, których nie udało się uzyskać za pomo-

cą ograniczeń.

instrukcję SQL. Gdy żaden rekord nie został

zmieniony, to następuje koniec działania wy-

zwalacza. W przeciwnym wypadku spraw-

dzane jest, w której z tabel INSERTED lub

DELETED są wiersze i wypisywany jest odpo-

wiedni komunikat.

W SQL Server 2000 zamiast instrukcji IF

((SELECT COUNT(*) FROM INSERTED)>0) le-

piej jest ze względów optymalizacyjnych

użyć fragmentu IF EXISTS(SELECT * FROM

INSERTED) , gdyż sprawdzanie ilości wierszy

poprzez COUNT(*) jest zdecydowanie bar-

dziej kosztowne niż wykorzystanie EXISTS .

Optymalizatory SQL Server 2005 i SQL Se-

rver 2008 zdecydowanie lepiej radzą sobie z

instrukcjami w stylu IF ((SELECT COUNT(*)

FROM INSERTED)>0) .

Aby sprawdzić działanie kodu z Listingu

1 należy wykonać instrukcje INSERT , UPDATE ,

DELETE . Dla instrukcji INSERT

Rozpoznawanie rodzaju triggera

Czasem istnieje konieczność utworzenia jed-

nego triggera dla różnych instrukcji, ale w

dalszym ciągu istnieje konieczność posiada-

nia wiedzy, jaka instrukcja została urucho-

miona.

Np. do celów rejestracyjnych można użyć

jednego wyzwalacza dla instrukcji INSERT ,

UPDATE , DELETE , a przy okazji można zapi-

sać jaki to był rodzaj instrukcji. W celu iden-

tyfikacji instrukcji można sprawdzić zawar-

tość tabel INSERTED i DELETED . Jeśli użyt-

kownik wykona instrukcję UPDATE , to da-

ne będą znajdować się zarówno w tabeli IN-

SERTED i DELETED . W przypadku instruk-

cji INSERT , dane będą znajdować się tylko w

tabeli INSERTED , a w przypadku instrukcji

DELETE tylko w tabeli DELETED . Na Listin-

gu 1 zaprezentowaliśmy kod źródłowy, któ-

ry pozwala na rejestrowanie wykonywanych

instrukcji.

Zmienna @@rowcount przechowuje infor-

macje na temat liczby dodanych, usuniętych

lub zmodyfikowanych wierszy przez ostatnią

INSERT INTO dbo.Osoby VALUES (Jan,

'Kowalski', 5000, 1);

otrzymamy następujący wynik:

SELECT * INTO Tymczasowa FROM Inserted

CREATE UNIQUE CLUSTERED INDEX idx_nazwa_

indeksu

ON Tymczasowa(nazwa_kolumny);

Instrukcja INSERT

(1 row(s) affected)

W przypadku instrukcji UPDATE

Zrozumienie działania tabel INSERTED i

DELETED w SQL Server 2005 i SQL Server

2008 jest kluczem do tworzenia efektywnych

aplikacji biznesowych (tak naprawdę efektyw-

nie działających wyzwalaczy w tych aplika-

cjach). Natomiast złe używanie tabel INSER-

TED i DELETED może prowadzić do dużych

problemów wydajnościowych, co w aplika-

cjach biznesowych jest niedopuszczalne.

UPDATE dbo.Osoby SET pensja=5500

WHERE id=1

w wyniku otrzymamy:

Instrukcja UPDATE

(1 row(s) affected)

Listing 1. Identyikacja rodzaju wyzwalacza

Triggery typu AFTER

Triggery typu AFTER uruchamiane są po zakoń-

czeniu instrukcji powodującej ich wywołanie.

Najczęstszą odmianą wyzwalaczy typu

AFTER są wyzwalacze DML AFTER , czyli trigge-

ry tworzone dla konkretnych tabel i konkret-

nych instrukcji typu UPDATE , INSERT , DELETE .

Tego typu wyzwalaczy można używać tylko i

wyłącznie do tabel trwałych (nie można ich

używać w stosunku do widoków i tabel tym-

czasowych).

Jeśli instrukcja DML wywołująca wyzwalacz

spowoduje wystąpienie błędu, to wyzwalacz nie

zostanie uruchomiony.

Wyzwalacze typu AFTER są wywoływane na

poziomie instrukcji, a nie na poziomie wiersza,

tak więc ilość wywołań triggera nie zależy od

ilości modyfikowanych wierszy.

Dla każdego obiektu i dla każdego typu in-

strukcji DML można tworzyć wiele wyzwala-

czy typu AFTER . Jeśli dla jednej tabeli i jedne-

go typu instrukcji mamy kilka różnych wy-

zwalaczy , to będą one uruchamiane sekwen-

CREATE TRIGGER trg_Osoby

ON dbo.Osoby

FOR INSERT , UPDATE , DELETE –- wyzwalacz na instrukcje INSERT , UPDATE , DELETE

DECLARE @row_count int;

-- pobieramy ilość wierszy, które zostały dodane, zmienione, usunięte

SET @row_count = @@rowcount;

-- gdy nie zostanie zmieniony żaden wiersz ...

IF ( @row_count = 0 )

BEGIN

PRINT 'Nie zmieniono danych' -- ... wypisanie komunikatu ...

RETURN ; -- ... i koniec triggera

END ;

IF (( SELECT COUNT ( * ) FROM INSERTED ) >0 ) –- jeśli są wiersze w INSERTED ...

IF (( SELECT COUNT ( * ) FROM DELETED ) >0 ) -- ... i w DELETED ...

PRINT 'Instrukcja UPDATE' -- ... to wykonano instrukcję UPDATE

ELSE -- ... i nie ma wierszy w DELETED

PRINT 'Instrukcja INSERT' -- ... to wykonano instrukcję INSERT

ELSE -- w przypadku, gdy nie ma wierszy w INSERTED

PRINT 'Instrukcja DELETE' ; -- to wykonano instrukcję DELETE

www.sdjournal.org

Aplikacje biznesowe

Natomiast w przypadku uruchomienia in-

strukcji DELETE

• lokalne tabele tymczasowe o konkretnej

nazwie widoczne są dla tworzącej je se-

sji, na poziomie wywołującym, oraz na

wszystkich poziomach wewnętrznych;

• aby tabela tymczasowa zapobiegła tylko

jednej próbie uruchomienia danej instruk-

cji SQL, należy po powrocie do zadania

wywołującego usunąć tę tabelę;

• tabele tymczasowe tworzone są w ba-

zie tempdb – tak więc używając funk-

cji OBJECT _ ID do sprawdzenia istnienia

tabeli tymczasowej, należy używać na-

zwy tabeli kwalifikowanej przy pomo-

cy nazwy bazy danych. Wywołanie funk-

cji OBJECT _ ID bez przedrostka tempdb w

czasie połączenia z inną bazą danych za-

wsze zwróci wartość NULL .

Funkcja COLUMNS_UPDATED

i predykat UPDATE – selektywne

wywoływanie wyzwalaczy

Tworząc aplikacje biznesowe zdarza się, że

chcemy reagować tylko i wyłącznie na zmia-

nę wartości niektórych kolumn. SQL Server

2005 i SQL Server 2008 pozwalają na uru-

chamianie triggera w sytuacjach, gdy zmie-

nione zostały tylko niektóre kolumny (np.

gdy ktoś próbuje zmienić wartość klucza

głównego). W SQL Server 2005 i SQL Se-

rver 2008 mamy dwa mechanizmy zapew-

niające selektywne uruchamianie wyzwala-

czy. Są to funkcja COLUMNS_UPDATED i predy-

kat UPDATE .

W predykacie UPDATE jako parametr wej-

ściowy należy podać nazwę kolumny. Wyni-

kiem działania instrukcji jest wartość TRUE lub

FALSE w zależności od tego, czy podana w para-

metrze wejściowym kolumna została zmienio-

na w klauzuli SET polecenia UPDATE urucha-

miającego wyzwalacz. Predykat UPDATE zwróci

wartość TRUE dla każdej kolumny, jeśli polece-

niem uruchamiającym wyzwalacz jest instruk-

cja INSERT .

Funkcja COLUMNS_UPDATED zwraca łańcuch

bajtów, w którym każdej kolumnie odpowia-

da jeden bit. Bit przyjmuje wartość 1 w przy-

padku, gdy kolumna została zmieniona, lub

0 , gdy kolumna nie została zmieniona. Każ-

dy bajt w łańcuchu reprezentuje 8 kolejnych

kolumn (po jednym bicie na kolumnę) – w

ramach każdego bajtu, bity uporządkowa-

ne są od prawej do lewej. Oznacza to, że in-

formacja o zmianie pierwszej kolumny zapi-

sana jest w pierwszym bajcie po lewej stro-

nie łańcucha, w pierwszym bicie znajdują-

cym się po prawej stronie danego bajtu. Na-

tomiast drugą kolumnę reprezentuje dru-

gi bit od prawej strony w tym samym bajcie.

W celu sprawdzenia, czy określone kolumny

zostały zmienione, trzeba posłużyć się opera-

torem bitowego iloczynu logicznego (&) po-

równując mapę bitową zwróconą przez funk-

cję COLUMNS_UPDATED z własną maską bitową,

w której należy włączyć bity tylko interesują-

cych nas kolumn.

Problemem dla osób używających funk-

cji COLUMNS_UPDATED może być fakt, że bi-

towy operator iloczynowy w SQL Server

2005 i SQL Server 2008 wymaga poda-

nia wartości całkowitoliczbowych (lub war-

tości, które można niejawnie przekształ-

cić na typ całkowitoliczbowy). Z kolei sa-

ma funkcja COLUMNS_UPDATED może zwró-

cić wartość dłuższą niż 8 bajtów, czyli war-

tość zwracana przez funkcję może nie zmie-

ścić się w zmiennej największego typu całko-

witoliczbowego – BIGINT . Wtedy koniecz-

ne jest wyodrębnienie fragmentu zwracane-

go łańcucha poprzez wykorzystanie funkcji

SUBSTRING . Np. aby wyciąć kolumnę o pozy-

cji @nr_kolumny wystarczy posłużyć się frag-

mentem.

DELETE FROM dbo.Osoby

WHERE id=1

SQL Server 2005 lub SQL Server 2008 wypi-

szą komunikat:

Instrukcja DELETE

(1 row(s) affected)

Gdy wykonana zostanie instrukcja, która nie

zmieni żadnych danych,

-- w bazie nie istnieje osoba o id = 300

DELETE FROM Osoby WHERE id = 300

to wyświetlony zostanie komunikat

Listing 2 prezentuje wyzwalacz uruchamiany

dla określonych instrukcji.

Podczas wykonywania instrukcji UPDATE na

tabeli dbo.Osoby

Nie zmieniono danych

(0 row(s) affected)

Nieuruchamianie triggerów

dla konkretnych instrukcji SQL

Nie istnieją formalne metody pozwalające

uniknąć wywołania wyzwalacza dla konkret-

nej instrukcji SQL.

Możliwe jest całkowite wyłączenie triggera

za pomocą instrukcji

UPDATE dbo.Osoby

SET nazwisko = 'Kowalski'

WHERE nazwisko = Nowak

trigger nie zostanie wywołany – wyświetlony

zostanie komunikat informujący o tym, że je-

den wiersz uległ zmianie.

ALTER TABLE DISABLE TRIGGER

(1 row(s) affected)

Co zrobić jednak w sytuacji, gdy trzeba za-

blokować wywoływanie wyzwalacza dla

konkretnej instrukcji – co w przypadku

aplikacji biznesowych (o dużym poziomie

skomplikowania) może być dość często wy-

magane?

Trzeba opracować własną metodę – musi-

my zasygnalizować, że nie chcemy wywoły-

wać wyzwalacza. Jednym z trików pozwala-

jących na realizację tego zadania jest użycie

tabel o konkretnych nazwach – mogą to być

tabele tymczasowe. Jeśli użyjesz tabel tym-

czasowych, to musisz pamiętać o trzech za-

sadach:

Po usunięciu tabeli dbo.test_update

DROP TABLE dbo.test_update;

i wykonaniu instrukcji UPDATE

UPDATE dbo.Osoby SET nazwisko = 'Abacki'

WHERE nazwisko = 'Kowalski'

zobaczymy komunikat informujący, że trigger

zadziałał poprawnie.

Trigger działa

(1 row(s) affected)

Ogólna postać triggera

CREATE TRIGGER [nazwa_schematu_bazy.]nazwa_wyzalacza

ON { tabela | perspektywa }

[ WITH <opcje_wyzwalacza> ]

{ FOR | AFTER | INSTEAD OF }

{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }

[ WITH APPEND ]

[ NOT FOR REPLICATION ]

AS { polecenia SQL }

Po słowach kluczowych CREATE TRIGGER należy umieścić nazwę wyzwalacza (można ją poprze-

dzić nazwą schematu). Następnie po słowie ON należy wskazać tabelę lub perspektywę, dla której

uruchamiany będzie trigger. Wyrażenie WITH <opcje_wyzwalacza> służy do zdeiniowania opcji

wyzwalacza. Kolejne dwie linie wskazują, kiedy trigger będzie uruchamiany. Po słowie kluczowym

AS znajduje się ciało wyzwalacza.

10/2008

SQL Server 2005/2008

SUBSTRING(COLUMNS_UPDATED(), (@nr_kolumny

– 1) / 8 + 1, 1)

Na Listingu 4 zaprezentowano wyzwalacz,

który zamiast funkcji COLUMNS _ UPDATED wy-

korzystuje predykat UPDATE .

Kod wyzwalacza z predykatem UPDATE

jest bardziej czytelny i łatwiejszy do napi-

sania. Jednak w sytuacjach, gdy chcemy

sprawdzić, czy większa liczba kolumn zo-

stała zmieniona, lepiej jest używać funkcji

COLUMNS_UPDATED .

Operacje wykonywane w trigge-

rze na wybranych wierszach

Triggery mogą również wykonywać opera-

cje na poszczególnych wierszach, albo odpo-

wiednio reagować w zależności od wartości

przechowywanych w poszczególnych wier-

szach. W wyzwalaczach można używać kur-

sorów, tworzyć tabele. W aplikacjach bizne-

sowych tego typu możliwości triggerów moż-

Maskę, którą trzeba przygotować, aby spraw-

dzić, czy dany bit ma wartość 1 lub 0 , generu-

je się poprzez podnoszenie liczby 2 do potęgi o

jeden mniejszej niż pozycja danego bitu w baj-

cie. Wyrażenie obliczające pozycje bitu o nu-

merze @nr _ bitu w bajcie mogłoby wyglądać

następująco:

(@nr _ bitu - 1) % 8 + 1

Listing 2. Przykład wyzwalacza uruchamianego dla określonej instrukcji

, a wyrażenie generujące maskę:

USE test;

POWER(2, (@nr _ bitu-1) % 8) .

-- jeśli istnieje tabela dbo.test_update ...

W celu sprawdzenia wartości bitu należy prze-

prowadzić operacje bitowego iloczynu pomię-

dzy przygotowaną maską, a wartością zwraca-

ną przez funkcję COLUMNS _ UPDATED . Wyraże-

nie takie może wyglądać następująco:

IF OBJECT_ID ( 'dbo.test_update' ) IS NOT NULL

DROP TABLE dbo.test_update; -- ... to ją usuwamy

-- tworzymy tabelę, dzięki której nie uruchomimy wyzwalacza

IF SUBTRING(COLUMNS_UPDATED(), (@nr_kolumny

– 1) / 8 + 1, 1) &

POWER(2, (@nr_bitu - 1) % 8) > 0 -- kolumna

została zmieniona

CREATE TABLE dbo.test_update

(

kolumna INT

)

Po zapoznaniu się z teoretycznymi wiadomo-

ściami, nadszedł czas, aby zaprezentować wy-

zwalacz, który korzysta z funkcji COLUMNS _

UPDATED (Listing 3). Naszym celem będzie wy-

pisanie komunikatu, jeśli w instrukcji UPDATE

zmieniona została kolumna pensja.

Kolumna pensja jest czwartą kolumną w ta-

beli Osoby , dlatego w wyzwalaczu użyliśmy

wyrażenia (4 - 1) , choć nic nie stoi na prze-

szkodzie, aby zamiast tego działania napisać po

prostu liczbę 3 . Jednak naszym zdaniem użycie

wyrażenia ( 4 - 1) jest bardziej czytelne dla ko-

goś czytającego ten kod.

Aby sprawdzić poprawność działania stwo-

rzonego wyzwalacza należy wykonać dowolną

instrukcję UPDATE.

-- jeśli istnieje wyzwalacz dbo.trg_UPDATE ...

IF OBJECT_ID ( 'dbo.trg_UPDATE' ) IS NOT NULL

DROP TRIGGER dbo.trg_UPDATE; -- ... to go usuwamy

-- tworzymy wyzwalacz ...

CREATE TRIGGER trg_UPDATE

ON dbo.Osoby -- ... na tabeli dbo.Osoby ...

FOR UPDATE -- ... dla instrukcji UPDATE

-- jeśli tabela dbo.test_update istnieje ...

IF OBJECT_ID ( 'dbo.test_update' ) IS NOT NULL

RETURN ; -- ... to kończymy działanie wyzwalacza ...

PRINT 'Trigger działa' ; -- ... w przeciwnym wypadku wyzwalacz normalnie działa

UPDATE dbo.Osoby SET pensja=3300

WHERE id=1

Listing 3. Przykład użycia funkcji COLUMNS_UPDATED wewnątrz wyzwalacza

-- jeśli w bazie jest wyzwalacz ...

IF OBJECT_ID ( 'dbo.trg_columns_updated' ) IS NOT NULL

DROP TRIGGER dbo.trg_columns_updated; -- ... to go usuwamy

W wyniku uruchomienia powyższego zapyta-

nia otrzymamy:

Zmieniono kolumnę pensja

(1 row(s) affected)

-- tworzymy wyzwalacz ...

CREATE TRIGGER dbo.trg_columns_updated

ON dbo.Osoby -- ... na tabeli Osoby ...

FOR UPDATE -- ... na instrukcję UPDATE

-- jeśli bitowy iloczyn wartości zwracanej przez funkcję COLUMNS_UPDATED ...

IF SUBSTRING ( COLUMNS_UPDATED () , ( 4 - 1 ) / 8 + 1, 1 ) &

POWER ( 2, ( 4 - 1 ) % 8 ) > 0 -- ... i maski przygotowanej przez nas ...

-- ... jest większy od 0 to wyświetlany jest komunikat ...

PRINT 'Zmieniono kolumnę pensja'

ELSE -- ... w przeciwnym wypadku

RETURN ; -- ... działane wyzwalacza jest kończone

Natomiast w przypadku zapytania UPDATE mo-

dyfikującego inną kolumnę niż pensja

UPDATE dbo.Osoby SET nazwisko = 'Kowalski'

WHERE id=1

Wyzwalacz nie zostanie uruchomiony i w wy-

niku otrzymamy tylko następującą informację:

(1 row(s) affected)

www.sdjournal.org

Aplikacje biznesowe

na wykorzystać do audytu (śledzenia zmian)

niektórych tabel. Na Listingu 5 zaprezento-

wano wyzwalacz, który będzie tworzył in-

strukcje UPDATE dla poszczególnych wierszy,

które zostały zmodyfikowane w tabeli Oso-

by dowolną instrukcją UPDATE . Dzięki dzia-

łaniu wyzwalacza będziemy w stanie stwier-

dzić, w jaki sposób zmienione zostały po-

szczególne wiersze w tabeli Osoby i w razie

potrzeby przywrócić starą wersję danych w

danym wierszu.

Na początku stworzono tabelę, która bę-

dzie przechowywać informacje dotyczące

daty oraz treść instrukcji zmieniającą po-

szczególne wiersze. Po stworzeniu tabe-

li, która będzie przechowywała nasze logi,

należy stworzyć trigger (Listing 5). W wy-

zwalaczu tym zastosowane zostaną różne-

go rodzaju triki, które mogą zwiększyć je-

go efektywność (poszczególne instrukcje zo-

stały opisane w postaci komentarzy na Li-

stingu 5).

W wyzwalaczu wykorzystano kilka trików,

które mogą przyspieszyć wykonywane dzia-

łania:

-- wszystkim osobom zarabiający 3000 zł

i więcej zmieniono pensje na 3500 zł

UPDATE dbo.Osoby SET pensja = 3500

WHERE pensja >= 3000

Triggery typu INSTEAD OF mają wiele zalet,

które są niezwykle przydatne przy tworzeniu

aplikacji biznesowych:

Powyższa instrukcja zmieniła cztery wier-

sze. Dla każdego z wierszy zapisana została

w tabeli logów ( Osoby _ log _ update ) odpo-

wiednia instrukcja UPDATE . Oczywiście nie

jest to najbardziej optymalny sposób logo-

wania operacji (zamiast budować instrukcję

UPDATE można zapisać poszczególne wartości

pól), ale wskazuje sposób postępowania w ta-

kich sytuacjach.

• mogą być stosowane do omijania ograni-

czeń modyfikacji przeprowadzanych na

perspektywach – np. w perspektywach

nie można aktualizować kolumn będą-

cych wynikiem kalkulacji, lub agregacji.

Stosując wyzwalacze typu INSTEAD OF

można przeprowadzić analizę instrukcji

SQL i wykonać odpowiedni kod na uży-

wanych w perspektywie kolumnach tabel

źródłowych;

• są uruchamiane przed sprawdzeniem zde-

finiowanych ograniczeń – dzięki temu

możliwa jest identyfikacja działań, które

normalnie nie powiodłyby się i zastąpie-

nie tych działań poprawnym kodem, któ-

ry nie będzie naruszał ograniczeń.

Triggery typu INSTEAD OF

Triggery typu INSTEAD OF uruchamiane są

zamiast oryginalnej instrukcji SQL przepro-

wadzanej na określonym obiekcie. Nie są to

triggery działające na zasadzie BEFORE , któ-

re są uruchamiane przed oryginalną instruk-

cją, ale takie, które są uruchamiane zamiast tej

instrukcji. Oryginalna instrukcja nigdy nie zo-

stanie wykonana. Wyzwalacze typu INSTEAD

OF mają duże znaczenie w przypadku tworze-

nia aplikacji biznesowych. Otóż tworząc apli-

kacje biznesowe musimy zapewnić bezpie-

czeństwo danych. Z tego też względu użyt-

kownikom bardzo często udostępniane są wi-

doki (zamiast tabel) zawierające tylko wyma-

gane do pracy danego użytkownika informa-

cje. Czasem jednak zachodzi konieczność, aby

ten użytkownik zmodyfikował lub dodał dane

do oryginalnej tabeli.

Oczywiście użytkownik nie wie, że dane,

które ogląda pochodzą z widoku, a nie tabeli.

A więc, jeśli otrzyma polecenie dodania wpi-

su, spróbuje tę czynność wykonać na dobrze

znanym mu widoku. W takiej sytuacji moż-

na użyć wyzwalacza typu INSTEAD OF , któ-

ry sprawdza poprawność wprowadzanych

danych i wstawia dane do oryginalnej tabe-

li (triggery typu INSTEAD OF można tworzyć

na widokach w przeciwieństwie do trigge-

rów typu AFTER ).

Listing 6 prezentuje działanie wyzwalacza ty-

pu INSTEAD OF . Instrukcję INSERT wstawiają-

ca dane do tabeli Osoby zastąpiono wyzwala-

czem typu INSTEAD OF , który sprawdza, czy

pensja dodawanej osoby jest większa od 0. Je-

śli tak nie jest, to instrukcja wstawienia nie

jest wykonana.

Próba wykonania instrukcji INSERT

• skopiowano zawartość tabel INSERTED i

DELETED do tabel tymczasowych za po-

mocą nielogowanej, a co za tym idzie bar-

dzo szybkiej operacji SELECT INTO . Oczy-

wiście, aby możliwe było skopiowanie da-

nych w ten sposób nie może istnieć tabela

docelowa;

• na utworzonych tabelach tymczasowych

założone zostały indeksy, które przyspie-

szają działania związane z pobieraniem

danych;

• kursor wewnątrz wyzwalacza utworzono

z opcją FAST _ FORWARD .

-- próba dodania osoby, z pensją 0 zł

zakończy się niepowodzeniem

INSERT INTO dbo.Osoby

VALUES('Albert', 'Bielecki', 1000, 1);

nie spowoduje dodania wiersza do tabeli Osoby .

Zwracamy szczególną uwagę na fakt, że trig-

ger typu INSTED OF działa ZAMIAST wywołu-

jącej go instrukcji. Tak więc, jeśli w kodzie wy-

zwalacza, zostanie pominięta instrukcja wywo-

łująca wyzwalacz (w ciele triggera nie będzie in-

strukcji INSERT wstawiającej dane do tabeli), to

żadne zmiany nie zostaną wykonane.

Teraz można napisać instrukcje UPDATE na ta-

beli Osoby , która zmieni kilka wierszy. Wy-

zwalacz powinien zapisać odpowiednie za-

pytanie SQL dla każdego z modyfikowanych

wierszy.

Wyzwalacze i perspektywy

W aplikacjach biznesowych ze względów bez-

pieczeństwa użytkownikom końcowym bar-

dzo często udostępniane są perspektywy za-

miast oryginalnych tabel. Aktualizacja, do-

danie lub usunięcie danych z perspektywy

bardzo często nie jest możliwe. I tu z pomo-

cą przychodzą wyzwalacze typu INSTEAD OF .

Dzięki wyzwalaczom można także sprawdzić

poprawność instrukcji próbującej dodać, mo-

dyfikować lub usunąć dane z perspektywy.

Można także zablokować wpisywanie niepo-

prawnych wartości.

Możliwe jest udostępnienie użytkownikowi

dodawania danych do tabel, mimo że tabele te

mogą nie być widoczne (użytkownikowi udo-

stępniona może być tylko perspektywa). Dzię-

ki takiemu podejściu funkcjonalność aplikacji

nie jest ograniczana, a bezpieczeństwo (co w

przypadku aplikacji biznesowych jest bardzo

Listing 4. Przykład użycia predykatu UPDATE wewnątrz wyzwalacza

IF OBJECT_ID ( 'dbo.trg_update' ) IS NOT NULL

DROP TRIGGER dbo.trg_update;

CREATE TRIGGER dbo.trg_update

ON dbo.Osoby

FOR UPDATE

IF UPDATE ( pensja ) -- jeśli kolumna pensja została zmieniona ...

PRINT 'Zmieniono kolumnę pensja' -- ... wypisywany jest komunikat

ELSE -- ... w przeciwnym wypadku ...

RETURN ; -- ... wyzwalacz jest kończony

10/2008

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika: