ITA-101 Bazy Danych

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 9

Wersja 1.0

Procedury składowane i wyzwalacze

Spis treści

Procedury składowane i wyzwalacze .................................................................................................. 1

Informacje o module ........................................................................................................................... 2

Przygotowanie teoretyczne ................................................................................................................. 3

Przykładowy problem ................................................................................................................. 3

Podstawy teoretyczne ................................................................................................................. 3

Przykładowe rozwiązanie ............................................................................................................ 8

Porady praktyczne .................................................................................................................... 13

Uwagi dla studenta ................................................................................................................... 14

Dodatkowe źródła informacji .................................................................................................... 14

Laboratorium podstawowe ............................................................................................................... 15

Problem 1 (czas realizacji 10 min) ............................................................................................. 15

Problem 2 (czas realizacji 10 min) ............................................................................................. 16

Problem 3 (czas realizacji 15 min) ............................................................................................. 17

Problem 4 (czas realizacji 10 min) ............................................................................................. 18

Laboratorium rozszerzone ................................................................................................................ 21

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 9

ITA-101 Bazy danych

Procedury składowane i wyzwalacze

Strona 2/21

Informacje o module

Opis modułu

W module zostanie zaprezentowany sposób działania oraz podstawy
tworzenia procedur składowanych. Dowiesz na czym polega różnica
pomiędzy zwykłym zapytaniem T-SQL a procedurą składowaną oraz co to
jest kompilacja i rekompilacja procedury. Zostanie wprowadzony również
specjalny rodzaj procedury składowanej – wyzwalacz.

Cel modułu

Celem modułu jest zapoznanie czytelnika z procedurami składowanymi oraz
wyzwalaczami w środowisku bazodanowym Microsoft SQL Server 2008 oraz
zaprezentowanie jak wykorzystad je w przykładowym projekcie.

Uzyskane kompetencje

Po zrealizowaniu modułu będziesz:



wiedział, jak napisad własną procedurę składowaną



umiał rozbudowywad gotowe procedury składowane



umiał tworzyd proste wyzwalacze



umiał zarządzad wyzwalaczami



umiał wykorzystywad wyzwalacze do optymalizacji i automatyzacji
działania SQL Server 2008

Wymagania wstępne

Przed przystąpieniem do pracy z tym modułem powinieneś:



potrafid samodzielnie stworzyd bazę danych wraz z jej obiektami
(patrz moduł 4)



znad podstawową składnię języka T-SQL



umied napisad własne zapytania w języku T-SQL (patrz moduł 6 i 7)



znad zaawansowane mechanizmy języka T-SQL (patrz moduł 9)



wiedzied na czym polega bezpieczeostwo w bazach danych (patrz
moduł 11)

Mapa zależności modułu

Zgodnie z mapą zależności przedstawioną na Rys. 1, przed przystąpieniem
do realizacji tego modułu należy zapoznad się z materiałem zawartym
w module: 3, 5, 6, 8, 10.

Moduł 9

Dodatek

Moduł 1

Moduł 2

Moduł 3

Moduł 4

Moduł 5

Moduł 6

Moduł 7

Moduł 8

Moduł 10

Moduł 11

Moduł 12

Moduł 13

Rys. 1 Mapa zależności modułu

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 9

ITA-101 Bazy danych

Procedury składowane i wyzwalacze

Strona 3/21

Przygotowanie teoretyczne

Przykładowy problem

Podczas wytwarzania bazy danych wiąże ze sobą grupę osób do której należą projektant bazy
danych, administrator systemu zarządzania bazą danych, administrator bazy danych oraz
programista bazy danych. Bardzo ważne jest żeby wszyscy biorący udział w wytwarzaniu systemu
bazodanowego współdziałali ze sobą na różnych etapach. Najwięcej wspólnego z programowaniem
w T-SQL ma programista bazy danych. To od niego w dużej mierze zależy czy wytworzone przez
niego funkcje, procedury składowane, wyzwalacze i inne element programistyczne będą działały we
właściwy sposób. To on odpowiedzialny jest za przygotowanie odpowiednich elementów
programowych, które następnie będzie wykorzystywał projektant aplikacji i programista aplikacji
dostępowej. A zatem również od niego w pewnym stopniu zależy bezpieczeostwo danych
gromadzonych w bazie. W SQL Server 2008 dostępny jest szereg systemowych procedur
składowanych. Oczywiście programista bazy danych może korzystad z ich również pisząc swoje
własne tzw. procedury składowane użytkownika. Kolejnym zadaniem jakie stoi przed programista i
administratorem bazy danych jest stworzenie odpowiednich liczby wyzwalaczy, które są pewnym
rodzajem procedur składowanych, które są wywoływane niejawnie. Należy pamiętad iż, procedury
składowane, które mogą byd wykorzystane przez programistę aplikacji dostępowej do bazy danych
powodują kolejną warstwę powodującą separację użytkownika koocowego od fizycznych danych
oraz powodują maskowanie fizycznej struktury bazy. Zatem bardzo ważne jest przemyślane i
odpowiednie stworzenie zbioru procedur składowanych.

Podstawy teoretyczne

Procedury składowane
Procedura składowana (ang. stored procedure) jest nazwanym zbiorem poleceo w języku SQL, który
jest przechowywany na serwerze w SZBD (System Zarządzania Bazą Danych) i jest kompilowany
przy pierwszym wykonaniu. Procedury wnoszą do środowiska serwera baz danych przetwarzanie
warunkowe i możliwości programistyczne.

W SZBD wykonanie dowolnego fragmentu kodu języka SQL wiąże się z pewnym ciągiem procesów –
począwszy od sprawdzenia składni aż po kompilację i wykonanie.

Proces wykonywania zapytania przez SQL Server

Poniżej zostało przedstawione, w jaki sposób odbywa się proces wykonania pojedynczego
zapytania w języku T-SQL w Microsoft SQL Server 2008:

1. Sprawdzenie i rozdzielenie kodu na fragmenty (często nazywane symbolami interpretowane

przez SZBD. Proces ten nazywamy analizą leksykalną.

2. Sprawdzenie kodu pod względem poprawności składni (kontrola poprawności semantycznej

– czyli czy kod nie odwołuje się do nieistniejących obiektów lub używa nieistniejących
poleceo oraz kontrola poprawności syntaktycznej – czy użyta składnia jest poprawna).

3. Standaryzacja wyodrębnionej części kodu, tzn. SZBD zapisuje go w jednoznacznej postaci

(usuwając niepotrzebne znaczniki).

4. Optymalizacja – każde zapytanie może posiadad wiele przygotowanych tzw. planów

wykonania (ang. execution plan). MS SQL Server posiada wewnętrzny proces zwany
Optymalizatorem Zapytao, który wybiera optymalny sposób dostępu do danych, tzn. taki
plan wykonania zapytania, w którym serwer będzie skanował (przeszukiwał) najmniejszą
ilośd stron danych (o fizycznej strukturze danych w MS SQL Server możesz przeczytad w
module 6). Na optymalizację szczególny wpływ mają struktura indeksów oraz sposób łączenia
tabel.

5. Kompilacja zapytania wg optymalnego planu wykonania i wykonanie skompilowanego

zapytania.

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 9

ITA-101 Bazy danych

Procedury składowane i wyzwalacze

Strona 4/21

6. Zwrócenie wyniku działania zapytania do klienta.

Proces wykonywania procedury składowanej

Wykonywanie procedur składowanych odbywa się inaczej niż wykonywanie pojedynczych zapytao
SQL. Poniżej prezentujemy schemat utworzenia i pierwszego wykonania procedury na przykładzie
MS SQL Server:

1. Tworzenie definicji procedury składowanej – tzn. wykonanie polecenie

CREATE PROCEDURE

.

2. Sprawdzanie kodu procedury pod względem syntaktyki.
3. Zapisanie nazwy procedury i jej kodu (tzw. ciało) do odpowiednich widoków systemowych

bazy danych (

sysobjects

oraz

syscomments

).

4. Wywołanie procedury składowanej przez użytkownika z odpowiednimi parametrami

używając polecenia EXEC.

5. Właściwe wykonanie procedury – optymalizacja planu wykonania i kompilacja.
6. Skompilowany optymalny plan wykonania jest zapisywany w tzw. buforze (z ang. cache'u

procedur.

Na Rys. 2 pokazano schemat tworzenia i wykonania procedury składowanej w środowisku SQL
Server 2008.

Rys. 2 Tworzenie i wykonanie procedury składowanej w SQL Server 2008

Rekompilacja procedur składowanych

Czasami zachodzi potrzeba ponownej kompilacji procedury składowanej. Dzieje się tak, gdy
wydajnośd skompilowanej procedury gwałtownie spada (może tak byd z wielu powodów, np.
zmiany struktury indeksów lub zapisania dużej ilości rekordów), gdy istnieje potrzeba kompilacji
procedury przy każdym jej wykonaniu (powody mogą byd te same, co w pierwszej sytuacji) lub gdy
zmianie ulega kod samej procedury (gdy użyjemy polecenia ALTER PROCEDURE).

Rekompilacji, czyli ponownej kompilacji procedury, można dokonad na dwa sposoby:



W definicji procedury dodając klauzulę

WITH RECOMPILE

.



Używając specjalnej systemowej procedury rekompilującej (w MS SQL Server jest to
procedura

sp_recompile

).

Tworzenie procedur składowanych

Do tworzenia procedur składowanych używamy polecenia języka SQL CREATE PROCEDURE (lub
CREATE PROC):

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 9

ITA-101 Bazy danych

Procedury składowane i wyzwalacze

Strona 5/21

CREATE { PROC | PROCEDURE } [nazwa_schematu.] nazwa_procedury
[ { @parametr typ_danych }
[ WITH <opcje_procedury> [ ,...n ] ]
[ FOR REPLICATION ]
AS { <wyrażenie_sql> [;][ ...n ] }
[;]

<opcja_procedury> ::=
[ ENCRYPTION ]
[ RECOMPILE ]
[ EXECUTE_AS_Clause ]

<wyrażenie_sql > ::=
{ [ BEGIN ] wyrażenie [ END ] }

W definicji procedury składowanej określamy:



nazwę procedury;



nazwy,



typy danych,



kierunek działania parametrów procedury;



ciało procedury – czyli kod wykonywany przez procedurę;



opcjonalnie deklarujemy, czy procedura ma byd przy każdym wykonaniu rekompilowana.

Poniżej podano przykład utworzenia prostej procedury składowanej nie zawierającej żadnych
parametrów:

CREATE PROCEDURE p_pracownicy
AS
SELECT imie, nazwisko
FROM Osoby

Wywołanie procedury składowanej

Do wywołania procedury składowanej służy polecenie

EXECUTE

(lub

EXEC

):

[ { EXEC | EXECUTE } ]
{
[ @return_status = ]
{ module_name [ ;number ] | @module_name_var }
[ [ @parameter = ] { value
| @variable [ OUTPUT ]
| [ DEFAULT ]
}
]
[ ,...n ]
[ WITH RECOMPILE ]
}
[;]

Przykładowo dla utworzonej wcześniej procedury składowanej

p_pracownicy

wywołanie

procedury będzie wyglądało w następujący sposób.

EXEC p_pracownicy
GO

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 9

ITA-101 Bazy danych

Procedury składowane i wyzwalacze

Strona 6/21

Parametry procedur składowanych

Procedury składowane mogą przyjmowad parametry wywołania. Ilośd i typ danych, które należy
podad przy wywołaniu procedury składowanej, określamy w trakcie tworzenia procedury (używając
polecenia

CREATE PROCEDURE

). W zależności od tego, czy parametry będą potrzebne do wykonania

procedury, czy też mają byd one przez procedurę zwrócone, wyróżniamy dwa rodzaje parametrów:
wejściowe (

INPUT

) oraz wyjściowe (

OUTPUT

).

Możliwe jest też zdefiniowanie w procedurze parametru przejściowego (będącego jednocześnie
wejściowym i wyjściowym), czyli parametru, którego wartośd podajemy przy wywołaniu procedury,
a procedura podczas działania może zmienid wartośd parametru i zwrócid nową wartośd.

CREATE PROCEDURE p_pracownicy
@nazwisko varchar(40)='Smith'
AS
SELECT imie, nazwisko
FROM Osoby
WHERE nazwisko=@nazwisko
GO

Zalety i wady procedur składowanych

Procedury składowane dzięki temu, że są zapisane na serwerze oraz dzięki skompilowanemu
planowi wykonania przechowywanemu w buforze procedur posiadają dwie zasadnicze zalety:



zwiększają wydajnośd bazy danych



ograniczają ruch w sieci (przesyłane są tylko nazwy procedur i wartości parametrów)

Ponadto procedury składowane mają kilka zalet z punktu widzenia programistów aplikacji
bazodanowych:



Zapewniają jedną logikę biznesową dla wszystkich aplikacji klienckich.



Przesłaniają szczegóły tabel w bazie danych (przezroczystośd struktury dla zwykłego
użytkownika aplikacji).



Umożliwiają modyfikację danych bez bezpośredniego dostępu do tabel bazy danych.



Dostarczają mechanizmów bezpieczeostwa (można nadawad uprawnienia do wykonywania
procedur poszczególnym użytkownikom bazy danych).



Umożliwiają programowania modularne (procedura zostaje zapisana w bazie danych, skąd
można ją wielokrotnie wywoływad; procedurę może pisad osoba wyspecjalizowana w bazach
danych – programista aplikacji jedynie ją wywoła).



Zapewniają szybsze wykonanie (jeśli wymagane jest wykonanie dużej liczby zapytao
procedury składowane są szybsze, ponieważ wykonują się na serwerze, są optymalizowane i
umieszczane w pamięci przy pierwszym wykonaniu).



Zmniejszają ruch sieciowy (jeśli zachodzi potrzeba wykonania wielu zapytao w T-SQL na raz,
można je zastąpid wywołaniem jednej procedury składowanej).

Jeśli w ogóle można mówid o wadach procedur składowanych, to w zasadzie można wspomnied o
kilku aspektach:



Następstwem rekompilacji czasem jest zmniejszenie wydajności procedury (czyli
administrator baz danych musi wiedzied, kiedy przeprowadzid rekompilację).



W przypadku zagnieżdżania procedur składowanych należy pamiętad o tym, że zmienia się
kontekst wykonania (procedura zagnieżdżana wykonuje się z uprawnieniami innej
procedury).



Wreszcie, aby tworzyd dobre (tzn. poprawnie działające) procedury składowane niezbędne
jest poznanie zaawansowanych mechanizmów języka SZBD (np. T-SQL) takich jak:
operowanie zmiennymi, funkcje i procedury systemowe, obsługa błędów.

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 9

ITA-101 Bazy danych

Procedury składowane i wyzwalacze

Strona 7/21

Wyzwalacze
Wyzwalacz (ang. trigger) jest specjalnym rodzajem procedury składowanej. W przeciwieostwie do
zwykłej procedury składowanej wyzwalacz nie może zostad jawnie wywołany.

Wyzwalacz jest wywoływany w reakcji na określone akcje. Akcje te to wykonanie przez użytkownika
określonego polecenia SQL (I

NSERT

,

UPDATE

,

DELETE

) na danej tabeli, dla której został określony

wyzwalacz.

Tworzenie wyzwalaczy

Wyzwalacze tworzymy używając polecenia CREATE TRIGGER. W definicji wyzwalacza określamy:



nazwę wyzwalacza;



dla jakiej tabeli tworzymy wyzwalacz;



na jakie akcje wyzwalacz będzie reagował;



jakiego typu wyzwalacz tworzymy;



ciało wyzwalacza (odpowiednik ciała procedury składowanej) - czyli kod wykonywany przez
wyzwalacz.

W SQL Server 2008 istnieją trzy rodzaje wyzwalaczy:

1. Triggery obsługujące operacje DML (ang. Data Manipulation Language), czyli

INSERT,

UPDATE

oraz

DELETE

wykonywane na tabeli lub widoku.

CREATE TRIGGER [ nazwa_schematu . ]nazwa_wyzwalacza
ON { table | view }
[ WITH <dml_opcje_wyzwalacza> [ ,...n ] ]
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS { wyrazenie_sql [ ; ] [ ,...n ] }

<dml_opcje_wyzwalacza> ::=
[ ENCRYPTION ]
[ EXECUTE AS Clause ]

2. Wyzwalacze obsługujące operacje DDL (ang. Data Definition Language) czyli

CREATE

,

ALTER

,

DROP

oraz pewne procedury składowane, które wykonują operacje DDL.

CREATE TRIGGER nazwa_wyzwalacza
ON { ALL SERVER | DATABASE }
[ WITH <ddl_opcje_wyzwalacza> [ ,...n ] ]
{ FOR | AFTER } { event_type | event_group } [ ,...n ]
AS { wyrażenie_sql [ ; ] [ ,...n ] }

<ddl_opcje_wyzwalacza> ::=
[ ENCRYPTION ]
[ EXECUTE AS Clause ]

3. Wyzwalacze obsługujące zdarzenie logowania (

LOGON

), który jest wywoływany, kiedy

ustalana jest sesja logującego się użytkownika.

CREATE TRIGGER nazwa_wyzwalacza
ON ALL SERVER
[ WITH <opcje_wyzwalacza_logon > [ ,...n ] ]

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 9

ITA-101 Bazy danych

Procedury składowane i wyzwalacze

Strona 8/21

{ FOR | AFTER } LOGON
AS { wyrażenie_sql [ ; ] [ ,...n ] }

< opcje_wyzwalacza_logon > ::=
[ ENCRYPTION ]
[ EXECUTE AS Clause ]

Zastosowanie wyzwalaczy

Wyzwalacze znajdują wiele zastosowao w bazach danych. Przede wszystkim stosujemy wyzwalacze
wszędzie tam, gdzie w inny sposób nie da się weryfikowad integralności danych, a zwłaszcza ich
zgodności z regułami logiki biznesowej. Na przykład chcemy, by w pewnej kolumnie tabeli
wstawiane były tylko wartości unikalne, ale jednocześnie zezwalamy na wstawienie wartości

NULL

.

Jedynym rozwiązaniem jest użycie wyzwalacza, który sprawdzi, czy wstawiana właśnie wartośd już
w danym polu wystąpiła, a jeżeli użytkownik wstawia wartośd

NULL

, to wyzwalacz mu na to pozwoli

(tego efektu nie można osiągnąd innymi metodami, np. używając indeksów, ustawiając właściwośd
unikalności kolumny lub używając kryteriów sprawdzających dane wstawiane w kolumnę).

Drugie zastosowanie to wszelkiego typu automatyzacja zadao administracyjnych w bazie danych
(wszelkiego rodzaju "przypominacze", obsługa nietypowych działao czy chociażby wysyłanie
wiadomości przez email lub pager).

Wreszcie z uwagi na pewne cechy wyzwalacze pozwalają na określony typ przetwarzania
transakcyjnego.

Przykładowe rozwiązanie

Tworzenie i uruchomienie procedury składowanej
W cel utworzenia nowej procedury składowanej należy z paska narzędziowego wybrad

New Query

. i

wpisad kod procedury. Przykładowo:

CREATE PROCEDURE dbo.getAllCustomers
AS
BEGIN

SELECT [CustomerID], [CompanyName]
FROM [Northwind].[dbo].[Customers]

END

Następnie należy wykonad skrypt klikając F5 lub wybierając z paska narzędziowego Execute. Jeśli
procedura składowana została utworzona poprawnie powinieneś otrzymad komunikat:
"Command(s) completed successfully" oraz zauważyd, że została dodana nowa procedura
składowana

w

Object

Explorer,

w

gałęzi

Databases->Northwind->Programmability-

>StoredProcedures co pokazano na Rysunku 3.

Jeśli procedury nie widad w wyżej wymienionej gałęzi proszę ją odświeżyd. Jeśli procedura jest już
utworzona, a mimo to zażądano kolejnego jej utworzenia to otrzymamy następujący komunikat:
„There is already an object named

‘getAllCustomers’

in the databases”

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 9

ITA-101 Bazy danych

Procedury składowane i wyzwalacze

Strona 9/21

Rys. 3 Tworzenie nowej procedury

Aby uruchomid procedurę składowaną wystarczy użyd polecenia EXECUTE (lub krócej EXEC). Można
także kliknąd prawym klawiszem myszki na procedurze i wybrad "Execute Stored Procedure..." co
wywoła okienko, w którym można przekazad do procedury jakieś parametry. Na Rysunku 4 widad,
że rozwijane menu udostępnia także inne opcje takie jak modyfikacja lub usunięcie.

Rys. 4 Wywołanie procedury

Inne metody tworzenia procedur składowanych
Inną metoda tworzenia procedury składowanej jest skorzystanie z gotowego schematu procedury.
W tym celu kliknij prawym przyciskiem myszy na gałęzi "Stored Procedures" i wybierz "New
StoredProcedure..." – uzyskasz w ten sposób gotowy schemat procedury składowanej co pokazno
na Rysunku 5.

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 9

ITA-101 Bazy danych

Procedury składowane i wyzwalacze

Strona 10/21

Rys. 5 Tworzenie nowej procedury według wzorca

Procedury składowane a polisy
W SQL Server 2008 wprowadzono politykę opartą o Management Framework dla silnika SQL, dzięki
której zarządzanie serwerem będzie bardziej zautomatyzowane w oparciu o pewne reguły, a nie
skrypty.

Zadaniem DMF jest umożliwienie administratorowi zarządzania serwerami za pomocą
definiowanych przez administratora reguł. W SQL Server 2008 mamy gotowe szablony reguł oparte
o najlepsze praktyki zaimplementowane w narzędziu

Best Practices Analyzer

, używanym przez

administratorów z poprzednimi wersjami systemu SQL Server.

Korzystając z mechanizmu polis możemy stworzyd restrykcje dla procedur składowanych o postaci:
brak możliwości definiowania przez programistę bazy danych procedur składowanych z prefiksem
‘sp_’. Restrykcję tą wprowadzimy na bazie danych Biblioteka.

W ramach

Object Explore

rozwijamy zakładkę

Menagement

a następnie

Policy Menagement

. W

pierwszym kroku należy dodad warunki jakie będą dołączone do polisy.

Jako pierwszy stwórzmy warunek, który będzie pilnował żeby nazwa procedury składowanej była
inna niż

‘sp_’

. Konfigurację tego warunku pokazano na Rysunku 6.

Następnie należy stworzyd warunek, dotyczący bazy danych na którym polisa, którą za chwilę
założymy będzie działała. Konfigurację tego warunku pokazano na Rysunku 7.

Jak już mamy stworzone warunki to w kolejnym kroku należy wystawid polisę i dodad do niej
wcześniej stworzone warunki. Konfiguracje wystawiania polisy pokazano na Rysunku 8.

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 9

ITA-101 Bazy danych

Procedury składowane i wyzwalacze

Strona 11/21

Rys. 6 Stworzenie warunku na procedurę składowaną

Rys. 7 Stworzenie warunku na bazę danych

Rys. 8 Założenie polisy

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 9

ITA-101 Bazy danych

Procedury składowane i wyzwalacze

Strona 12/21

Teraz kiedy mamy wystawiona polisę chcąc ją przetestowad należy klikną prawym przyciskiem na
bazie danych

Biblioteka

i wybrad

New Query

. Następnie wpisujemy kod pustej systemowej

procedury składowanej

CREATE PROCEDURE sp_testowa
AS
GO

Zauwazmy, ze wystapił błąd. Procedura nie została utworzona gdyż zadziałała restrykcja DMF co
pokazano na Rysunku 9.

Rys. 9 Błąd procedury i zadziałanie polisy

Tworzenie wyzwalacza
Stwórzmy wyzwalacz, którego zadaniem będzie nie dopuścid do zalogowania wskazanego przez nas
użytkownika. W tym celu na początku należy dodad użytkownika

zbd_user

i kojarzymy go z bazą

danych

AdventureWorks

. Następnie tworzymy odpowiedni wyzwalacz, który nie pozwoli

użytkownikowi zalogowad się do Microsoft SQL Server Management Studio. W tym celu, klikamy
prawym przyciskiem myszy na bazę

AdventureWorks

i z menu kontekstowego wybieramy

New

Query

. W oknie edycji zapytania wpisujemy następujący kod:

USE AdventureWorks
GO

CREATE TRIGGER trgRestrictUser
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS

BEGIN

IF (ORIGINAL_LOGIN()= 'zbd_user' AND APP_NAME() = 'Microsoft SQL
Server Management Studio')

ROLLBACK;
END

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 9

ITA-101 Bazy danych

Procedury składowane i wyzwalacze

Strona 13/21

Aby przetestowad działanie utworzonego wyzwalacza uruchamiamy drugą instancję MS SQL Server
Management Studio. W oknie

Connect to Server Authentication

wybieramy

SQL Server

Authentication

, Login:

zbd_user

, Password:

user

. Zatwierdzamy klikając

Connect

. Wybierając

Connect pokazuje się błąd logowania co pokazano na Rysunku 10.

Rys. 10 Bład logowania

Porady praktyczne

Procedury składowane



W MS SQL Server przy kontroli poprawności kodu procedury w trakcie jej tworzenia serwer
nie sprawdza, czy istnieją obiekty (tabele, widoki), do których procedura się odwołuje.
Sprawdzenie to następuje dopiero przy wykonaniu procedury (w przypadku odwołania do
nieistniejącego obiektu procedura zgłosi błąd).



Rekompilacja procedury składowanej nie oznacza utworzenia procedury na nowo. Oznacza
utworzenie nowego planu wykonania i zapisanie go do bufora procedury na miejsce
poprzednio skompilowanego planu tej samej procedury.



Systemowe procedury składowane w systemie Microsoft SQL Server 2008 są
przechowywane w bazie

master

, zaś w ich nazwach pojawia się prefiks

‘sp_’



Zgodnie z dobra praktyka programowania baz danych procedury składowane użytkownika w
SQL Server 2008 nie powinny zaczynad się od prefiksu

‘sp_’

. Prefiksem tym obarczone są

systemowe procedury składowane. W sytuacji kiedy kompilator zobaczy procedurę
składowaną o takiej nazwie będzie jej szukał w procedurach systemowych jako tych, które
już są skompilowane a ich wynik zapamiętany w cachu procedury. Dopiero kiedy jej tam nie
znajdzie stwierdzi że jest to procedura użytkownika i zacznie ją kompilowad według
poznanego schematu. Spowoduje to wydłużenie czasu wykonania procedury składowanej.



Stworzenie polisy, która nie pozwoli programiście utworzyd procedury składowanej
zaczynającej się od prefiksu

‘sp_’

spowoduje, że pierwsza próba stworzenia procedury o

takiej nazwie pociągnie za sobą koniecznośd uruchomienia sprawdzenia polisy a zatem czas
nieznacznie się wydłuży. Kolejne wykonanie próby stworzenia procedury niezgodnej z polisa
spowoduje natychmiastowe wyświetlenie komunikatu o naruszeniu restrykcji.

Wyzwalacze



Wyzwalacze mogą byd tworzone bezpośrednio w MS SQL Server 2008 Database Engine za
pomocą zwykłych zapytao napisanych w języku T-SQL lub poprzez specjalne metody w
Microsoft .NET Framework Common Language Runtime (CLR), a następnie po utworzeniu
importowane do instancji serwera bazodanowego. MS SQL Server 2008 pozwala na
tworzenie wielu wyzwalaczy dla specyficznego zdarzenia. Do tworzenia wyzwalaczy
potrzebne są specjalne uprawnienia w bazie danych.



Następujące instrukcje języka T-SQL nie są dozwolone w wyzwalaczach DML:

ALTER

DATABASE

,

LOAD DATABASE

,

RESTORE DATABASE

,

CREATE DATABASE

,

LOAD LOG

,

RESTORE

LOG

,

DROP DATABASE

i

RECONFGURE

. Dodatkowo powyższe instrukcje nie mogą byd użyte

wewnątrz ciała wyzwalacza DML.

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 9

ITA-101 Bazy danych

Procedury składowane i wyzwalacze

Strona 14/21

Uwagi dla studenta

Jesteś przygotowany do realizacji laboratorium jeśli:



rozumiesz, co to jest procedura składowana oraz wyzwalacz



rozumiesz zasadę działania procedur składowanych i wyzwalaczy



znasz składnię zaawansowanego języka Transact-SQL



umiesz dodawad użytkowników do SQL Server



rozumiesz różnicę pomiędzy różnymi rodzajami wyzwalaczy

Pamiętaj o zapoznaniu się z uwagami i poradami zawartymi w tym module. Upewnij się, że
rozumiesz omawiane w nich zagadnienia. Jeśli masz trudności ze zrozumieniem tematu zawartego
w uwagach, przeczytaj ponownie informacje z tego rozdziału i zajrzyj do notatek z wykładów.

Dodatkowe źródła informacji

1. Deren Bieniek, Randy Dyess, Mike Hotek, Javier Loria, Adam Machanic, Antonio Soto, Adolfo

Wiernik, SQL Server 2005 Implemantacja i obsuga, APN Promise, Warsyawa 2006

W książce obszernie przedstawiono zagadnienia związane z programowaniem baz
danych. Szczegółowo omówiono zagadnienia dotyczące procedur składowanych i
wyzwalaczy. Omówiono w niej implementacje procedur składowanych oraz w jaki
sposób przyznawad do nich uprawnienia. Pokazano implementacje wyzwalaczy
(DML i DDL). Książka szczególnie polecana ze względu na dużą zawartośd dwiczeo
laboratoryjnych.

2. Kalen Delaney, Podstawy baz danych krok po kroku, APN Promise, 2006

Bardzo dobra książka dla osób początkujących. Pokazano w niej praktyczne
zastosowanie wyzwalaczy i procedur składowanych. Książka oprócz teorii
obdarzona dużą liczbą przykładów.

3. Dusan Petkovic, Microsoft SQL Server 2008: A Beginner's Guide, McGraw-Hill, 2008

Pozycja napisana w sposób prosty. Wprowadza w SQL Server 2008 w sposób szybki
i łatwy. Osoba początkująca w SQL Server 2008 znajdzie w niej podstawy z każdego
tematu dotyczącego serwera bazodanowego. W prosty sposób dowiesz się jak
należy tworzyd proste procedury składowane bez parametrów i z parametrami oraz
poznasz jak należy stworzyd wyzwalacz i w jakiej sytuacji go zastosowad. Pozycja
szczególnie polecana dla osób początkujących jak i zaawansowanych.

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 9

ITA-101 Bazy danych

Procedury składowane i wyzwalacze

Strona 15/21

Laboratorium podstawowe

Problem 1 (czas realizacji 10 min)

Jesteś programistą bazodanowym w firmie National Insurance. Właśnie dowiedziałeś się od
swojego szefa, że firma National Insurance zarządzająca systemem prac dyplomowych na Twoim
wydziale planuje rozszerzenie systemu na skalę uczelnianą. Pierwsze zadanie, jakie przed Tobą
analiza procedur składowanych już istniejących.

Zadanie

Tok postępowania

1. Nawiązywanie
połączenia z SQL
Server 2008



Uruchom maszynę wirtualną BD2008.

— Jako nazwę użytkownika podaj Administrator.
— Jako hasło podaj P@ssw0rd.

Jeśli nie masz zdefiniowanej maszyny wirtualnej w MS Virtual PC,
dodaj nową maszynę używając wirtualnego dysku twardego z pliku
D:\VirtualPC\Dydaktyka\BD2008.vhd.



Kliknij Start. Z grupy programów Microsoft SQL Server 2008 uruchom
SQL Server Management Studio.



W oknie logowania kliknij Connect.

2. Informacja o
bazie danych



Z menu głównego wybierz File -> Open -> File.



Odszukaj plik C:\Labs\Lab09\ProcedurySystemowe.sql i kliknij Open.



Zaznacz i uruchom (F5) poniższy fragment kodu, który wywołuje
procedurę systemową sp_helpdb:

-- (1) Ustawiamy sie na baze danych PraceDyplomowe

USE PraceDyplomowe
GO

-- (2) Wywolanie procedury systemowej sp_helpdb

EXEC sp_helpdb PraceDyplomowe

Procedura sp_helpdb zwraca informacje o wybranej bazie danych
(rozmiar, listę plików i informacje o nich).

3. Informacje o
obiektach bazy
danych



Zaznacz i uruchom (F5) poniższy fragment kodu, który wywołuje
procedurę systemową sp_help:

-- (3) Wywolanie procedury systemowej sp_help

EXEC sp_help dbo.Prace

Procedura sp_help zwraca informacje o wybranym obiekcie bazy
danych.

Czy w tabeli dbo.Prace jest jakakolwiek kolumna typu bit?

4. Informacje o
indeksach tabeli



Zaznacz kod, który wywołuje procedurę systemową sp_helpindex.

-- (4) Wywolanie procedury systemowej sp_helpindex

EXEC sp_helpindex dbo.Prace



Wciśnij F5, aby uruchomid zaznaczony fragment kodu.

Procedura sp_helpindex zwraca listę i opis indeksów założonych na
kolumnach w wybranej tabeli.

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 9

ITA-101 Bazy danych

Procedury składowane i wyzwalacze

Strona 16/21

Czy w tabeli dbo.Prace jest nałożony jakiś indeks?

Problem 2 (czas realizacji 10 min)

Po dokonaniu analizy procedur już istniejących, kolejne zadanie jakie przed Toba zostało
postawione to zdefiniowanie procedur składowanych dla nowych użytkowników oraz modyfikacja
już istniejących procedur składowanych. W pierwszej kolejności powinieneś zająd się procedurami
składowanymi bez parametrów.

Zadanie

Tok postępowania

1. Nawiązywanie
połączenia z SQL
Server 2008



Uruchom maszynę wirtualną BD2008.

— Jako nazwę użytkownika podaj Administrator.
— Jako hasło podaj P@ssw0rd.

Jeśli nie masz zdefiniowanej maszyny wirtualnej w MS Virtual PC,
dodaj nową maszynę używając wirtualnego dysku twardego z pliku
D:\VirtualPC\Dydaktyka\BD2008.vhd.



Kliknij Start. Z grupy programów Microsoft SQL Server 2008 uruchom
SQL Server Management Studio.



W oknie logowania kliknij Connect.

2. Utworzenie
procedury
składowanej



Kliknij w menu głównym programu Management Studio na File.



Kliknij Open -> File.



Odszukaj plik C:\Labs\Lab10\Procedura_bez_parametrow.sql i kliknij
Open.



Zaznacz kod, który wywołuje procedurę składowaną Promotorzy (patrz
kod poniżej).

-- (1) Ustawiamy sie na baze danych PraceDyplomowe

USE PraceDyplomowe
GO

-- (2) Tworzymy procedure skladowana uzytkownika

CREATE PROCEDURE Promotorzy
AS
BEGIN
SELECT [Nazwisko], [Imie]
FROM Promotor
END

Innym sposobem stworzenia procedury składowanej jest kliknięcie
prawym przyciskiem myszy na gałęzi "Stored Procedures" i wybranie
"New StoredProcedure..." – uzyskasz w ten sposób gotowy schemat
procedury składowanej.



Wciśnij F5, aby uruchomid zaznaczony fragment kodu.

Jeśli procedura składowana została utworzona poprawnie, powinieneś
otrzymad komunikat "Command(s) completed successfully" oraz
zauważyd, że została dodana nowa procedura składowana w Object
Explorer, w gałęzi Databases -> PraceDyplomowe -> Programmability
-> StoredProcedures. Jeśli procedury nie widad w w/w gałęzi, odśwież
ją. Jeśli procedura jest już utworzona, a mimo to zażądano kolejnego

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 9

ITA-101 Bazy danych

Procedury składowane i wyzwalacze

Strona 17/21

jej utworzenia, to otrzymamy następujący komunikat: „There is
already object name ‘Promotorzy’ in the database”.

3. Uruchomie-
nie procedury
składowanej



Zaznacz kod, który wywołuje procedurę Promotorzy.

-- (3) Wywolujemy procedure skladowana Promotorzy

EXEC Promotorzy
GO



Innym sposobem na wywołanie procedury składowanej jest kliknięcie
prawym przyciskiem myszy na procedurze Promotorzy i wybierz
Execute Stored Procedure..." co wywoła okienko, w którym można
przekazad do procedury jakieś parametry.



Wciśnij F5, aby uruchomid zaznaczony fragment kodu.

Problem 3 (czas realizacji 15 min)

Kolejne zadanie jakie powinieneś wykonad to zdefiniowanie procedur składowanych dla nowych
użytkowników oraz modyfikacja już istniejących procedur składowanych z parametrami

Zadanie

Tok postępowania

1. Nawiązywanie
połączenia z SQL
Server 2008



Uruchom maszynę wirtualną BD2008.

— Jako nazwę użytkownika podaj Administrator.
— Jako hasło podaj P@ssw0rd.

Jeśli nie masz zdefiniowanej maszyny wirtualnej w MS Virtual PC,
dodaj nową maszynę używając wirtualnego dysku twardego z pliku
D:\VirtualPC\Dydaktyka\BD2008.vhd.



Kliknij Start. Z grupy programów Microsoft SQL Server 2008 uruchom
SQL Server Management Studio.



W oknie logowania kliknij Connect.



.

2. Utworzenie
procedury
składowanej



Kliknij w menu głównym programu Management Studio na File.



Kliknij Open -> File.



Odszukaj plik C:\Labs\Lab10\Procedura_z_parametrami.sql i kliknij
Open.



Zaznacz kod, który wywołuje procedurę składowaną Promotorzy (patrz
kod poniżej).

-- (1) Ustawiamy sie na baze danych PraceDyplomowe

USE PraceDyplomowe
GO

-- (2) Tworzymy procedure skladowana użytkownika z parametrem

CREATE PROCEDURE dbo.Promotorzy
@Nazwisko VARCHAR(30)
AS
BEGIN
SELECT *
FROM [PraceDyplomowe].[dbo].[Osoba]
WHERE [PraceDyplomowe].[dbo].[Osoba].[nazwisko] = @Nazwisko
END



Wciśnij F5, aby uruchomid zaznaczony fragment kodu.

Jeśli procedura składowana została utworzona poprawnie powinieneś
otrzymad komunikat "Command(s) completed successfully" oraz

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 9

ITA-101 Bazy danych

Procedury składowane i wyzwalacze

Strona 18/21

zauważyd, że została dodana nowa procedura składowana w Object
Explorer, w gałęzi Databases->PraceDyplomowe->Programmability-
>StoredProcedures

3. Uruchomie-
nie procedury
składowanej



Zaznacz kod, który wywołuje procedurę Promotorzy. Wciśnij F5, aby
uruchomid zaznaczony fragment kodu

-- (3) Wywolujemy procedure skladowana z parametrem

EXEC Promotorzy @Nazwisko='Kowalski'

GO

Lub kilknij prawym przyciskiem myszy na procedurze Promotorzy i
wybierz Execute Stored Procedure..." co wywoła okienko w którym
można przekazad do procedury jakieś parametry. Co pokazano na
Rysunku 11.

Rys. 11 Wywołanie procedury składowanej z parametrem

Problem 4 (czas realizacji 10 min)

Po zdefiniowaniu procedur składowanych wynikających z zmian jakie zachodzą w firmie w której
pracujesz kolejnym etapem jest stworzenie odpowiedniej liczby wyzwalaczy. Jednym z wyzwalaczy
jest zaimplementowania warunku, że każdy opiekun może mied maksymalnie 10 dyplomantów.

Zadanie

Tok postępowania

1. Nawiązywanie
połączenia z SQL
Server 2008



Uruchom maszynę wirtualną BD2008.

— Jako nazwę użytkownika podaj Administrator.
— Jako hasło podaj P@ssw0rd.

Jeśli nie masz zdefiniowanej maszyny wirtualnej w MS Virtual PC,
dodaj nową maszynę używając wirtualnego dysku twardego z pliku
D:\VirtualPC\Dydaktyka\BD2008.vhd.



Kliknij Start. Z grupy programów Microsoft SQL Server 2008 uruchom
SQL Server Management Studio.



W oknie logowania kliknij Connect.

2. Utworzenie
procedury
składowanej



Kliknij w menu głównym programu Management Studio na File.



Kliknij Open -> File.



Odszukaj plik C:\Labs\Lab10\Wyzwalacz_1.sql i kliknij Open.



Zaznacz i uruchom (F5) poniższy fragment kodu:

-- (1) Ustawiamy się na baze danych PraceDyplomowe

USE PraceDyplomowe
GO

-- (2) przygotujmy tabele do testowania wyzwalaczy

SELECT ID_Osoby, Nazwisko, Imie, Nr_Indeksu, ID_Stopnian

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 9

ITA-101 Bazy danych

Procedury składowane i wyzwalacze

Strona 19/21

INTO Osoba_kopi
FROM Osoba
GO



Zaznacz i uruchom (F5) fragment kodu oznaczonego w komentarzu (3).
Wynik pokazano na Rys. :

-- (3) zobaczmy, co znajduje sie w tabeli
SELECT * FROM Osoba_kopi
GO

Rys. 12



Następnie stwórz

Wyzwalacz

, którego zadaniem będzie przechwycenie

wykonywanego zapytania i wyświetlenie zdefiniowanej przez nas
informacji.



W obrębie okna Object Explorer wybieramy Osoba -> Triggers.



Wciskamy prawy przycisk myszy i wybieramy New Trigger.



Wpisujemy poniższy kod:

-- (4) Tworzymy nowy wyzwalacz

CREATE TRIGGER Ocenay
ON Osoba
AFTER INSERT, UPDATE
AS
DECLARE @username as varchar(30)
SELECT @username = SYSTEM_USER
PRINT 'Uzytkownik '+ @username + ' zmienil zawartosc tabeli
Osoba!!!'
GO



W celu zobaczenia działania wyzwalacza wywołujemy następujące
zapytanie:

-- (5) Sprawdzamy dzialanie wyzwalacza

UPDATE Osoba
SET [Nazwisko] = 'Nowak'
WHERE ID_Osoby=1
GO



Jeśli wyzwalacz zadziałał poprawnie, w oknie Messages powinien
pojawid się komunikat o tresci przedstawionej na Rysunku 13.

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 9

ITA-101 Bazy danych

Procedury składowane i wyzwalacze

Strona 20/21

Rys.133

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 9

ITA-101 Bazy danych

Procedury składowane i wyzwalacze

Strona 21/21

Laboratorium rozszerzone

Jesteś administratorem w firmie National Insurance. Właśnie dowiedziałeś się od swojego szefa, że
firma zarządzająca bazą AdventureWorks planuje rozszerzenie i modernizacje systemu w celu
spełnienia pewnych standardów. W związku z modernizacją systemu bazodanowego oraz
spełnienia standardów bezpieczeostwa dostęp do obiektów bazy danych powinien odbywad się
poprzez procedury składowane. W wyniku tego częśd procedur składowanych powinna zostad
zmodyfikowana i przekompilowana, częśd powinna zostad napisana od początku, a częśd powinna
zostad usunięta. Kolejnym pojawiającym się problemem jest kwestia spójności zmodyfikowanej
bazy danych. W tym celu powinny zostad zmodyfikowane, utworzone lub usunięte wyzwalacze
służące do zaimplementowania pewnych warunków.

Zadanie, jakie przed Tobą stoi, to:

1. Podjęcie decyzji, jakie nowe procedury składowane i wyzwalacze powinny zostad utworzone

w celu poprawienia bezpieczeostwa bazy danych.

2. Podjęcie decyzji, które procedury składowane w bazie danych pozostaną bez zmian, a które

zostaną zmodyfikowane lub usunięte.

3. Podjęcie decyzji, które wyzwalacze w bazie danych pozostaną bez zmian, a które zostaną

zmodyfikowane lub usunięte.