BAZY DANYCH Cz III

Transakcje, Triggery

Transakcje

• Definicja:

– Zbiór operacji (modyfikacja danych, usuwanie, wstawianie, tworzenie obiektów bazodanowych), które albo wszystkie kończone są sukcesem, albo w całości są odwoływane (czyli powracamy do stanu sprzed rozpoczęcia transakcji)

– Jeżeli w ramach pewnej transakcji, która jeszcze trwa zostay zmienione pewne dane, to inne procesy nie mogą zmienić tych danych dopóki wcześniejsza transakcja nie zakończy się, lub nie zostanie odwołana.

Transakcje - korzysci

• Idealne zabezpieczenie do manipulacji

całymi dokumentami (albo dokument jest

w całości albo go nie ma)

– Przykładem takiego dokumentu jest „faktura”

– Dane dotyczące jednej faktury są

przechowywane zazwyczaj w wielu tabelach

– Tworzenie faktury musi się udać „w całości”

lub w przypadku błędów faktura nie powinna być utworzona

Transakcje - korzyści

• Doskonałe rozwiązania zadań

„współdzielonych”. Np.. do nadawania

kolejnych numerów fakturom (mimo

równoczesnej pracy wielu osób nie

powstanie „dziura” w numeracji)

• Baza danych jest odporna nawet na

przerwę w pracy serwera baz danych (po

restarcie, niedokończone transakcje są

odwoływane)

Transakcje – składnia w MS SQL-u

• Początek transakcji (rozpoczyna

transakcję w ramach danej sesji):

BEGIN TRAN [nazwa]

• Odwołanie transakcji (odwołuje wszystkie rozpocząte w ramach danej sesji

transakcje):

ROLLBACK TRAN [nazwa]

• Zatwierdzenie i zakończenie transakcji COMMIT TRAN [nazwa]

Transakcja – Ćw. 1

• Otwieramy dwie sesje (niebieska i czerwona) BEGIN TRAN tr_blue

UPDATE Osoby

SET Nazwisko=RTRIM(Nazwisko)+’1’

WHERE id_osoby = 1

/* Zobaczymy, że jedna z osób ma inne

nazwisko */

SELECT * FROM Osoby

Transakcja – Ćw. 1

/* to zapytanie będzie wstrzymane do końca niebieskiej transakcji – musimy je

przerwać (np.. Query STOP/CANCEL) */

SELECT * FROM Osoby WHERE id_osoby = 1

/*ten parametr pozwala „podejrzec”

nieskonczone transakcje–nierekomendowany*/

SELECT FROM Osoby (nolock)

/*Ten pokazuje tylko zakończone transakcje*/

SELECT * FROM Osoby (readpast)

Transakcje – Ćw. 1

/* Odwołujemy transakcję */

ROLLBACK TRAN TR_blue

/* Teraz widzimy dane sprzed rozpoczęcia transakcji */

SELECT * FROM Osoby

SELECT * FROM Osoby

Transakcje - zagnieżdżanie

• Transakcje można zagnieżdzać:

BEGIN TRAN TR1

UPDATE Osoby SET Nazwisko=RTRIM(Nazwisko)+’1’

WHERE id_osoby = 1

SELECT * FROM osoby

BEGIN TRAN TR2

SELECT @@TRANCOUNT AS poziom_transakcji UPDATE Osoby SET Nazwisko=RTRIM(Nazwisko)+’2’

WHERE id_osoby = 1

SELECT * FROM Osoby

ROLLBACK TRAN

SELECT @@TRANCOUNT AS poziom_transakcji SELECT * FROM Osoby /*wszystkie tr. zostały odwołane*/

Transakcje - zagnieżdżanie

• Poprzedni przykład pokazuje, że:

– Rozpoczęcie kolejnej transakcji dodaje jeden poziom transakcji, który możemy sprawdzić testująć zmienną globalną @@TRANCOUNT

– Rollback Tran odwołuje wszystkie rozpoczęte do tej pory transakcje. Po ROLLBAK TRAN

zmienna @@TRANCOUNT wynosi ZERO

Transakcje – zapamiętanie stanu i

odwoływanie tr. do tego punktu

• Do zapamiętania stanu transakcji

używamy SAVEPOINT-a. Do jego

utworzenia służy komenda SAVE TRAN

nazwa

• SAVEPION-a nie zatwierdza się, ale

można odwołać wszystkie zmiany

dokonane po jego utworzeniu

• Przed jego utworzeniem musi być

rozpoczęta „normalna” transakcja.

SAVEPOINT Ćw 1

BEGIN TRAN TR_zew

UPDATE Osoby SET Nazwisko=RTRIM(nazwisko)+’1’

WHERE id_osoby = 1

SELECT @@TRANCOUNT AS Ile_Tr, o.* FROM Osoby o SAVE TRAN TR_savepoint

SELECT @@TRANCOUNT AS Ile_Tr, o.* FROM Osoby o UPDATE Osoby SET Nazwisko=RTRIM(Nazwisko)+’2’

WHERE id_osoby = 1

SELECT @@TRANCOUNT AS Ile_Tr, o.* FROM Osoby o ROLLBACK TRAN TR_savepoint

SELECT @@TRANCOUNT AS Ile_Tr, o.* FROM Osoby o ROLLBACK TRAN TR_zew

SELECT @@TRANCOUNT AS Ile_Tr, o.* FROM Osoby o

SAVEPOINT - wnioski

• Utworzenie SAVEPOINT-a nie zwiększa poziomu transakcji (nie tworzy on nowej transakcji)

• Można odwołać wszystkie zmiany do

SAVEPOINT-a zwykłym poleceniem

ROLLBACK TRAN nazwa_savepointa

• Ponieważ SAVEPOINT nie tworzy nowej transakcji nie wymaga polecenia COMMIT

TRAN

Zagnieżdzone transakcje - Wnioski

• W MS-SQL-u, SYBASE, nie ma sensu

zagnieżdzać transakcji, gdyż w przypadku takiej potrzeby nie można odwołać zmian

dokonanych tylko przez transakcję

wewnętrzną

• Zamiast zagnieżdzać, lepiej korzystać z SAVEPOINT-ów

Transakcje a procedury

• W MS-SQL-u badany jest poziom

transakcji przed wywołaniem procedury

bazodanowej i po jej zakończeniu. Jak jest różny to traktowane to jest jako poważny błąd

• Procedury, które tworzą własne transakcje powinny działać prawidłowo w przypadku,

gdy nie ma jeszcze żadnej transakcji jak i w przypadku gdy jest już jakaś rozpoczęta

Transakcje w procedurach

• Procedura musi mieć parametr – typu „output”, którym będzie informować, czy wystąpiły

wewnątrz niej błędy. Zazwyczaj przyjmuje się, że wartość 0 oznacza brak błędu, a każda inna wartość jego wystąpienie

• Wewnątrz procedury należy sprawdzić czy istnieje już rozpoczęta transakcja

– TAK -> używamy SAVEPOINT-a

– NIE -> tworzymy nową transakcję

Transakcje w procedurach

CREATE PROCEDURE TestTr (@trErr int output = 0, …) AS

DECLARE @trCnt int /* licznik tr na wejściu */

SET @trCnt = @@TRANCOUNT

IF @trCnt = 0

/* nie ma jeszcze żadnej */

BEGIN TRAN TRTestTr

ELSE

/* już jakaś jest */

SAVE TRAN TRtestTr

. . . /*operacje po których ustawiana jest @trErr*/

IF @trErr = 0

/* nie było błędów */

BEGIN

IF @trCnt = 0 /*była utworzona nowa transakcja*/

COMMIT TRAN TRTestTr

RETURN 0

END

IF @@TRANCOUNT > 0 /* na wszelki wypadek */

ROLLBACK TRAN TRTestTr /*odw.tr.lub do savepoint-a*/

RETURN -1

Procedury bazodanowe

• Są to procedury przechowywane po stronie serwera SQL

• Uznaje się, że najlepsza aplikacja BD to taka, która od strony klienta nie wysyła składni zapytań SQL, tylko wywołuje procedury

bazodanowe

– Bezpieczniejsze (ze składni wynika struktura bazy)

– Efektywniejsze, składnia jest kompilowana i optymalizowana każdorazowo a procedury

jednokrotnie (kompilowane podczas tworzenia a optymalizowane podczas pierwszego wywołania)

Składnia procedury

IF EXISTS

( SELECT 1 FROM sysobjects o

WHERE (o.[name] = ‘ProcTest’) AND

(OBJECTPROPERTY(o.[ID], ‘IsProcedure’)=1)

)

DROP PROCEDURE ProcTest

GO

CREATE PROCEDURE ProcTest ([lista param]) AS

. . . /* kod procedury */

GO

Procedury czy Funkcje ?

• Jeżeli procedura ma zwrócić jakąś wartośc poprzez parametr i nie wykonuje wewnątrz żadnych operacji SQL-owych to lepiej użyć funkcji

CREATE FUCTION FrokMies(@data datetime)

RETURNS char(6)

AS

RETURN convert(char(6), data, 112)

GO

SELECT nazwa_bazy.nazwa_urz_bazy.FrokMies(GETDATE())

Parametry procedur

• Parametry wejściowe mogą być każdego

typu akceptowanego przez dany SQL

CREATE Procedure P(@d datetime = NULL, @logi bit = 0, @napis nvarchar(200))

AS

…

GO

EXEC P @napis = N’ala ma kota’

Parametry we-wy

• Są to parametry poprzez które procedura może zwrócić wartości do wywołującego ją programu (procedury)

CREATE Procedure T2 (@m varchar(10) output) AS

SET @m = ‘Hello’

GO

DECLARE @napis varchar(10), @m varchar(10) EXEC T2 @m = @napis

SELECT @napis AS wynik1

EXEC T2 @m = @napis output

SELECT @napis AS wynik2

EXEC T2 @m=@m output

SELECT @m

Parametry we-wy -> Wnioski

• Parametr pod który procedura może coś

zwrócić musi być zadeklarowany jako

output

• Podczas wywołania pod parametr

podstawiamy nazwę aktualnej (istniejącej) zmiennej też z atrybutem output.

• Bez tego atrybutu wynik nie będzie

przekazany do programu wywołującego

Triggery

• Są to procedury bazodanowe które:

– Nie posiadają żadnych parametrów

– Są związane z jedną, konkretną tabelą w bazie

– Są związane z operacjami wstawiana, usuwania bądź

modyfikacji rekordów w tabeli. Mogą być zwiazane z dowolną ilością tych operacji.

– Wewnątrz triggera możemy zabronić kontynuowania danej operacji (odwołać transakcję). Ponieważ każda pojedyńcza operacja SQL-owa (insert,update,delete) jest pojedyńczą transakcją to wystarczy ją odwołać wewnątrz TRIGGER-a pisząc ROLLBACK TRAN

Triggery - przykład

create trigger trO ON Osoby FOR Insert AS

Raiserror('Nie da się :p', 16, 1)

ROLLBACK TRAN

GO

select * from osoby

insert into osoby (imię, nazwisko,id_miasta) values ('Test', 'Testowy',1)

select * from osoby

Triggery

• Wewnątrz triggera dostępne są dwie

tabele:

– Inserted rekordy wstawione

– Deleted rekordy skasowane

• Obydwie tabele mają identyczną strukturę (identyczne kolumny) jak tabela z którą

trigger jest związany

Triggery

• Operacja INSERT (wstawianie rekordów)

– Tabela inserted zawiera wstawione rekordy

– Tabela deleted jest pusta

• Operacja DELETE (usuwanie rekordów)

– Tabela deleted zawiera usunięte rekordy

– Tabela inserted jest pusta

• Operacja UPDATE (modyfikacja rekordów)

– Tabela inserted zawiera zmodyfikowane rekordy

– Tabela deleted zawiera rekordy sprzed zmiany

Trigger Przykład 2

• Dodajemy nową kolumnę do tabeli firmy.

Będzie w niej przechowywany NIP. Druga

kolumna NIP_bk będzie przechowywać

zawartość kolumny NIP z pominięciem

„myślników”, po tym polu będziemy szukać w aplikacji

Modyfikacja tabeli – dodanie

kolumny

ALTER TABLE tabela ADD kolumna typ

NULL | NOT NULL DEFAULT wartość

• Nowo dodana kolumna albo musi mieć

atrybut NULL albo NOT NULL z

warunkiem DEFAULT

– Wynika to z prostego faktu, ze po dodaniu kolumny SQL musi ją wypełnić wartościami

– Dla kolumny NULL będą to puste wartości (NULL), dla kolumn z wartością DEFAULT

będzie to wartość domyślna

Dodanie kolumn

ALTER TABLE Firmy ADD NIP char(20) NULL

ALTER TABLE Firmy ADD NIPBK char(20) NULL

CREATE TRIGGER TRFNIP ON Firmy FOR Insert AS

UPDATE baza.user.Firmy

SET NIPBK = REPLACE(NIP,’-’,’’)

WHERE nazwa_skr IN

(SELECT i.nazwa_skr FROM inserted i)

GO

Trigger Przykład

CREATE TRIGGER TRFNIPU ON Firmy FOR Update AS

/*Ponizsza konstrukcja jest dostępna tylko wewnątrz triggerów i mówi, czy w poleceniu UPDATE, w sekcji SET była wymieniona kolumna NIP */

IF UPDATE(NIP)

BEGIN

UPDATE baza.user.Firmy

SET NIPBK = REPLACE(NIP,’-’,’’)

WHERE nazwa_skr IN

(SELECT i.nazwa_skr FROM inserted i)

END

GO

update firmy set NIP = 'XX-YYY-ZZ-TTT'

where nazwa_skr = 'PW’ /* SELECT * FROM Firmy */

Trigery - mozliwości

• Dodatkowa weryfikacja dokonywanych zmian

• Przy wstawianiu rekordów (np. faktury) można sprawdzić dodatkowe warunki

– Np. czy klient nie zablokowany

– Czy towar jest na magazynie (podczas wstawiania pozycji faktury można generować pozycje „wydania z magazynu”

• Doskonała metoda (nieinwazyjna) na

rozszerzanie funkcjonalności systemu, bez dostępu do jego kodów źródłowych

Trigger – Przykład 2

• Rozszerzymy funkcjonalność naszego

systemu o monitorowanie zmian w tabeli

osoby. Zakładamy, że sysem jest

zamknięty i nie mamy kodów źródłowych

ale mamy pełne prawa administracyjne do

bazy danych (tak jest zazwyczaj)

Trigery – Przykład 2

CREATE TABLE osoby_tmp

( id_osoby

int

NOT NULL

, imię

varchar(40)

NOT NULL

, nazwisko

varchar(40)

NOT NULL

, data_zm

datetime NOT NULL

DEFAULT (GETDATE())

, kto_zm

varchar(40)

NOT NULL

DEFAULT (LEFT(USER_NAME(),40))

, stacja

varchar(40)

NOT NULL

DEFAULT (LEFT(HOST_NAME(),40))

)

Trigger – Przykład 2

CREATE TRIGGER TROTMP ON Osoby FOR Update AS

IF (UPDATE(imię) OR UPDATE(nazwisko))

AND EXISTS

( SELECT 1 FROM inserted i join deleted d ON (i.id_osoby = d.id_osoby)

WHERE NOT ((i.imię = d.imię)

AND (i.nazwisko = d.nazwisko))

)

Trigger – Przykład, cd.

INSERT INTO baza.user.osoby_tmp

( id_osoby, imię, nazwisko )

SELECT d.id_osoby, d.imię, d.nazwisko

FROM inserted i join deleted d

ON (i.id_osoby = d.id_osoby)

WHERE NOT ((i.imię = d.imię)

AND (i.nazwisko = d.nazwisko))

GO

Trigger – Przykład, test

update osoby

set nazwisko = rtrim(nazwisko) + '1'

Select * from Osoby

select * from Osoby_tmp

/*Po chwili odtwarzamy poprawne nazwiska*/

Update Osoby SET nazwisko = t.nazwisko FROM Osoby join Osoby_tmp t ON

(Osoby.id_osoby = t.id_osoby)