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)