BP Lab 7

CREATE OR REPLACE TRIGGER izbrisi_ulogi
BEFORE DELETE ON Vraboteni
FOR EACH ROW
BEGIN
	DELETE FROM Ulogi WHERE Ulogi.v =: old.v;
END;
----
DELETE FROM Vraboteni WHERE imev='Tana Mimeska'
----
SELECT COUNT(*) FROM (SELECT * FROM Vraboteni JOIN Ulogi ON Vraboteni.v = Ulogi.v) WHERE imev='Tana Mimeska';
---------------------------------------------------------------------------------------------------------------
ALTER TABLE Teatri
ADD VkupnoPretstavi int
-------------------------
UPDATE Teatri SET VkupnoPretstavi=(SELECT COUNT(DISTINCT Bileti.p) FROM Bileti WHERE Teatri.t = Bileti.t)
-------------------------
CREATE OR REPLACE TRIGGER update_vkupno_pretstavi
AFTER INSERT OR DELETE ON Bileti
FOR EACH ROW
BEGIN
	UPDATE Teatri SET VkupnoPretstavi=(SELECT COUNT(DISTINCT Bileti.p) FROM Bileti WHERE Teatri.t = Bileti.t)
END;
------------------------
INSERT INTO Bileti(t, p, status, scena, cena, prodadeni) VALUES('14', '43', 'gostuva', 'golema_scena', '150', '200');
------------------------
DELETE FROM Bileti WHERE t=27 AND p=27;
------------------------
ALTER TABLE Bileti DROP PRIMARY KEY;
ALTER TABLE Bileti ADD PRIMARY KEY(t, p);

SELECT COUNT(*) FROM Ulogi WHERE ima_uloga="glavna" GROUP BY p

Ne smee da ima pretstava so poveke od 3 glavni ulogi i poveke od 1 reziser

CREATE ASSERTION dopolnitelno_baranje
CHECK(NOT_EXISTS(SELECT COUNT(*) FROM Ulogi WHERE ima_uloga='rezira' AND COUNT(*) > 1 GROUP BY p) AND NOT_EXISTS(SELECT COUNT(*) FROM Ulogi WHERE ima_uloga='glavna' AND COUNT(*) > 3 GROUP BY p))