Vježba 9

--Vježba 9 – VIEWS, STORED PROCEDURE, TRIGGERS

--NAPOMENA: Vježba se radi nad bazom podataka Pubs.
--1. Kreirati proceduru za upis podataka u tabelu authors. Izvršiti proceduru, tj. dodati novog autora sa testnim podacima.
USE pubs
CREATE PROCEDURE  dodaj_autora
AS
INSERT INTO authors
VALUES ('133-53-9929','Smith','Mike','283 555-3353','Neverstreet','Newcity','HC',99966,'True')
EXEC dodaj_autora

SELECT *
FROM authors

DROP PROCEDURE dodaj_autora

--2. Kreirati proceduru za izmjenu podataka u tabeli authors. Izvršiti proceduru, tj. izmijeniti određene podatke prethodno dodanog autora.
USE pubs
CREATE PROCEDURE izmjeni_autora
AS
UPDATE authors
SET au_fname='George', au_lname='Martin'
WHERE au_id='133-53-9929'
EXEC izmjeni_autora

SELECT *
FROM authors
WHERE au_id='133-53-9929'

DROP PROCEDURE izmjeni_autora

--3. Kreirati proceduru za brisanje zapisa iz tabele authors. Izvršiti proceduru, tj. obrisati prethodno dodanog autora.
USE pubs
CREATE PROCEDURE brisi_autora
AS
DELETE FROM authors
WHERE au_id='133-53-9929'

EXEC brisi_autora

SELECT *
FROM authors
WHERE au_id='133-53-9929'

DROP PROCEDURE brisi_autora

--4. Izmijeniti proceduru kreiranu u zadatku 1. U istoj proceduri, dodanog autora pridružiti kao koautora na dvije postojeće knjige.
--   U proceduri koristiti dvije INSERT komande (Tabele: authors, titleauthor). Izvršiti proceduru, tj. dodati novog autora sa njegovim knjigama.
USE pubs
CREATE PROCEDURE  dodaj_autora
AS
INSERT INTO authors
VALUES ('133-53-9929','Smith','Mike','283 555-3353','Neverstreet','Newcity','HC',99966,'True')
INSERT INTO titleauthor
VALUES('133-53-9929','PS3333',2,100)
EXEC dodaj_autora

SELECT *
FROM authors

DROP PROCEDURE dodaj_autora

--5. Kreirati pogled (view) sa sljedećim podacima: naziv izdavača, ime i prezime autora (odvojeno), naziv knjige, zarada od prodaje knjige/a.
USE pubs
CREATE VIEW autori
AS
SELECT p.pub_name AS 'Naziv izdavaca',a.au_fname'Ime autora',a.au_lname'Prezime autora',t.title'Naziv knjige',s.qty*t.price AS 'Zarada od prodaje knjige'
FROM publishers AS p JOIN titles AS t
ON p.pub_id=t.pub_id JOIN titleauthor AS ta
ON t.title_id=ta.title_id JOIN authors AS a
ON ta.au_id=a.au_id JOIN sales AS S
ON S.title_id=t.title_id

SELECT *
FROM autori

DROP VIEW autori

--6. Kreirati proceduru koja prima sljedeće parametre: ime i prezime autora, naziv izdavača, naziv knjige. U zavisnosti od proslijeđenog parametra procedura treba da vrati zaradu od prodaje knjige/a.
--   Koristiti view kreiran u zadatku 5.
CREATE PROCEDURE zarada
@imeautora nvarchar(20)='nema vrijednost',
@prezimeautora nvarchar(40)='nema vrijednost',
@nazivizdavaca nvarchar(40)='nema vrijednost',
@nazivknjige nvarchar(80)='nema vrijednost'
AS
SELECT p.pub_name AS 'Naziv izdavaca',a.au_fname'Ime autora',a.au_lname'Prezime autora',t.title'Naziv knjige',s.qty*t.price AS 'Zarada od prodaje knjige'
FROM publishers AS p JOIN titles AS t
ON p.pub_id=t.pub_id JOIN titleauthor AS ta
ON t.title_id=ta.title_id JOIN authors AS a
ON ta.au_id=a.au_id JOIN sales AS S
ON S.title_id=t.title_id
WHERE a.au_fname LIKE @imeautora OR a.au_lname LIKE @prezimeautora OR p.pub_name LIKE @nazivizdavaca OR t.title LIKE @nazivknjige

EXEC zarada @nazivknjige='The Gourmet Microwave'

DROP PROCEDURE zarada

--7. Kreirati trigger (INSTEAD OF) koji će spriječiti izvršavanje DELETE komande nad tabelom titleauthor. Ukoliko se pokuša obrisati zapis u tabeli titleauthor ispisati odgovarajuću poruku.
--   Koristiti komandu PRINT za ispis poruke.
CREATE TRIGGER nodelete
ON titleauthor
INSTEAD OF DELETE
AS
PRINT 'Zabranjeno brisanje podataka u tabeli author'
ROLLBACK

DELETE FROM titleauthor
WHERE au_id='213-46-8915'

DROP TRIGGER nodelete

DELETE FROM titleauthor
WHERE au_id='213-46-8915'

--8. Kreirati proceduru koja briše autora dodanog u zadatku 4, zajedno sa svim njegovim knjigama. Izvršiti proceduru, tj. pokušati obrisati autora.
--   Trigger kreiran u zadatku 7 treba da spriječi izvršavanje ove procedure.
CREATE PROCEDURE brisiautora
AS
DELETE titleauthor
FROM authors AS a JOIN titleauthor AS ta
ON a.au_id=ta.au_id
WHERE a.au_id='133-53-9929'
DELETE authors
FROM authors
WHERE au_id='133-53-9929'

EXEC brisiautora

SELECT *
FROM titles AS t JOIN titleauthor AS ta
ON t.title_id=ta.title_id JOIN authors AS a
ON a.au_id=ta.au_id
WHERE a.au_id='133-53-9929'