Bazy Danych - poniedziałek, 15.00 - 18.00 - zadania zestaw 08.

TSQL - Triggery.

Składnia dla triggerów DML (istnieją jeszcze triggery LOGON i DDL):

	CREATE TRIGGER nazwa_wyzwalacza ON nazwa_tabeli
	{
		{ {FOR | AFTER | INSTEAD OF} { [INSERT] [,UPDATE] [,DELETE] }
			AS instrukcja_T-sql
		}				
	}
	

• FOR oznacza, że trigger będzie działał przed wykonaniem danej instrukcji - tabela nie będzie jeszcze zmodyfikowanaFOR jest synonimem AFTER, nie ma triggerów BEFORE
• AFTER oznacza, że trigger będzie działał po wykonaniem danej instrukcji - tabela będzie już zmodyfikowana
• INSTEAD OF oznacza że tabela nie będzie zmodyfikowana nawet po wykonaniu triggera przez orginalne zapytanie. Zamiast tego trigger przejmuje całą pracę.

• W przypadku triggerów {FOR | AFTER | INSTEAD OF} INSERT tabela INSERTED zawiera wstawione dane,
• w przypadku {FOR | AFTER | INSTEAD OF} DELETE tabela DELETED zawiera usuniete wiersze,
• w przypadku {FOR | AFTER | INSTEAD OF} UPDATE tabela DELETED zawiera dane sprzed zmiany a INSERTED po zmianie.

	DISABLE TRIGGER nazwa_triggera ON nazwa_tabeli GO
	DISABLE TRIGGER ALL ON nazwa_tabeli GO
	ENABLE TRIGGER nazwa_triggera ON nazwa_tabeli GO
	ENABLE TRIGGER ALL ON nazwa_tabeli GO	
	

Przykład (nie pozwól na zmianę danych):

	CREATE TRIGGER bezupdate ON tabela
	INSTEAD OF UPDATE
		AS 
	GO
	

	CREATE TRIGGER sprawdzmiejsca ON bilety
	INSTEAD OF INSERT
		AS 
			if (SELECT COUNT(*) FROM inserted) <= (SELECT MAX(miejsca) FROM sale as s JOIN inserted as i ON i.salaId = s.id)
			begin
				INSERT INTO bilety SELECT * FROM inserted
			end
	GO
	

Do raportowania błędów uzywamy następującej składni:

	RAISERROR('Nastąpił błąd',16,34)
	

	@@ERROR
	

	DECLARE @my_error INT	
	SELECT 1/0
	SELECT @my_error = (@@ERROR) % 255
	IF @my_error != 0
		RAISERROR('Nastąpił błąd!',16,@my_error)
	

Przykład (nie pozwól na zmianę danych ale wyrzuć błąd):

	CREATE TRIGGER bezupdate ON tabela
	INSTEAD OF UPDATE
		AS 
			RAISERROR('Nie wolno!', 16, 2)
	GO
	

Blok TRY ... CATCH służy głównie w procedurach składowanych, funkcjach i triggerach do obsługi błędów systemowych, oraz użytkownika o severity w zakresie 0-10.

	BEGIN TRY
	{ sql_statement | statement_block }
	END TRY
	BEGIN CATCH
	{ sql_statement | statement_block }
	END CATCH
	[ ; ]
	

• ERROR_NUMBER() zwraca numer błędu.
• ERROR_SEVERITY() zwraca poziom istotności błędu (severity level).
• ERROR_STATE() zwraca stan błędu.
• ERROR_PROCEDURE() zwraca nazwę procedury lub wyzwalacza, w którym wystąpił błąd.
• ERROR_LINE() zwraca numer tej linii kodu w procedurze, w której nastąpił błąd.
• ERROR_MESSAGE() zwraca pełny tekst komunikatu o błędzie.

	CREATE PROCEDURE usp_GetErrorInfo
	AS
		SELECT
			ERROR_NUMBER() AS ErrorNumber,
			ERROR_SEVERITY() AS ErrorSeverity,
			ERROR_STATE() AS ErrorState,
			ERROR_PROCEDURE() AS ErrorProcedure,
			ERROR_LINE() AS ErrorLine,
			ERROR_MESSAGE() AS ErrorMessage;
	GO
	BEGIN TRY
		-- Zróbmy coś niedozwolonego
		SELECT 1/0;
	END TRY
	BEGIN CATCH
		EXECUTE usp_GetErrorInfo;
	END CATCH;
	

Zadania

Mamy dany następujący schemat bazy danych:

create table Samoloty(
	id int PRIMARY KEY,
	nazwa varchar(256) NOT NULL,
	iloscMiejsc int NOT NULL,
	pojemnoscBagazu int NOT NULL DEFAULT(100),
)
go
create table helikoptery(
	id int PRIMARY KEY,
	nazwa varchar(256) NOT NULL,
	iloscMiejsc int NOT NULL,	
)
go
create table Loty(
	id int PRIMARY KEY,
	aktualnaCena decimal(10,2) NOT NULL,
	cel varchar(256) NOT NULL,
	idSamolotu int NOT NULL,
	dataWylotu date NOT NULL,
)
go
create table Bilety(
	id int PRIMARY KEY,
	idLotu int NOT NULL REFERENCES Loty(id),
	cena decimal(10,2) NOT NULL,
	klient varchar(256) NOT NULL,
)
go

insert into Samoloty VALUES(1, 'Kosciuszko', 4);
insert into Samoloty VALUES(2, 'Wladyslaw', 5);
insert into Samoloty VALUES(3, 'Jagiello', 3);

insert into Loty VALUES(1, 2000, 'USA', 1, '2012-12-12');
insert into Loty VALUES(2, 1000, 'Lizbona', 2, '2012-12-11');
insert into Loty VALUES(3, 800, 'Lizbona', 3, '2012-12-15');
insert into Loty VALUES(4, 2500, 'USA', 2, '2012-12-18');
insert into Loty VALUES(5, 1500, 'USA', 3, '2012-12-08');
insert into Loty VALUES(6, 3500, 'USA', 2, '2012-12-24');
insert into Loty VALUES(7, 400, 'Amsterdam', 3, '2012-12-24');
insert into Loty VALUES(8, 350, 'Amsterdam', 1, '2012-12-04');

insert into Bilety VALUES(1, 1, 400, 'Kowalski');
insert into Bilety VALUES(2, 1, 400, 'Kowalski');
insert into Bilety VALUES(3, 1, 400, 'Kowalski');
insert into Bilety VALUES(4, 1, 400, 'Milosz');
insert into Bilety VALUES(5, 2, 400, 'Kowalski');
insert into Bilety VALUES(6, 2, 400, 'Milosz');
insert into Bilety VALUES(7, 3, 400, 'Kowalski');
insert into Bilety VALUES(9, 3, 400, 'Kowalski');
insert into Bilety VALUES(10, 3, 400, 'Nowak');
insert into Bilety VALUES(11, 4, 400, 'Kowalski');
insert into Bilety VALUES(12, 4, 400, 'Kowalski');
insert into Bilety VALUES(13, 4, 400, 'Kowalski');
insert into Bilety VALUES(14, 4, 400, 'Kowalski');
insert into Bilety VALUES(15, 4, 400, 'Nowak');

Wykonaj poniższe polecenia

1. Stwórz trigger, który po usunięciu wierszy z tabeli bilety zmieni aktualną cenę na niższą o 10% jeśli ilośc biletów na dany lot spadnie poniżej 3. Załóż, że zawsze usuwamy bilety tylko na jeden lot w jednym zapytaniu delete.

2. Stwórz trigger, który po usunięciu wierszy z tabeli bilety zmieni aktualną cenę na niższą o 10% jeśli ilośc biletów na dany lot spadnie poniżej 3 - bez powyższego założenia.

3. Sprawdź co się stanie, jeśli wykonamy operacje DELETE * FROM bilety. Czy trigger się wykonał? Przywróć dane o biletach i spróbój wykonać operację TRIM bilety. Czy trigger się wykonał?

4. Stwórz trigger, który po usunięciu wierszy z tabeli bilety zmieni aktualną cenę na niższą o 10% jeśli ilośc biletów na dany lot spadnie poniżej 3. Załóż, że zawsze usuwamy bilety tylko na jeden lot w jednym zapytaniu delete.

5. Stwórz trigger, który po wstawieniu wierszy do tabeli bilety zmieni ich ceną na aktualną.

6. Stwórz trigger, który podczas wstawienia biletu sprawdzi czy w samolocie jest jeszcze wolne miejsce. Załóż, że wszystkie bilety są na ten sam lot.

7. Stwórz trigger, który podczas wstawienia biletu sprawdzi czy w samolocie jest jeszcze wolne miejsce - bez powyższego założenia.

8. Stwórz trigger, który podczas wstawienia biletu sprawdzi czy w samolocie jest jeszcze wolne miejsce, jesli tak to doda bilet, powiększy cenę bazową lotu o 5%, oraz policzy obniżkę tym klientom, który kupili już kiedykolwiek więcej niż 10 biletów. Trigger powinien poradzić sobie z wieloma wstawianymi wierszami, jesli nie ma wystarczająco dużo miejsc wszystkie powinny zostać odrzucone.

9. Stwórz dwa triggery na tabelach Samoloty i Helikoptery, które sprawdzą czy dodawany pojazd ma poprawne id. Jedno id nie może powtarzać się w dwóch tabelach.

10. Stwórz trigger na tabeli loty, które umozliwi wstawianie tylko takich lotów, których id samolotu jest w tabeli Samoloty lub Helikoptery.