DROP TRIGGER IF EXISTS studentski_dom.TG_Insert_Soba_Kapacitet;

INSERT INTO studentski_dom.Stanar(StudentID, AkademskaGodinaID, DomID, SobaID, SluzbenikID)
VALUES(2, 2, 2, 3, 2);

DELIMITER $$

CREATE TRIGGER studentski_dom.TG_Insert_Soba_Stanari_Kapacitet
BEFORE INSERT
ON studentski_dom.Stanar
FOR EACH ROW
	BEGIN
		SET @idSobe = (SELECT NEW.SobaID from NEW);
        SET @idGodine = (SELECT NEW.AkademskaGodinaID from NEW);
        SET @idStudenta = (SELECT NEW.StudentID from NEW);

		SET @kapacitetSobe = (SELECT DISTINCT tip_sobe.BrojKreveta
								FROM studentski_dom.Stanar stanar INNER JOIN studentski_dom.Soba soba ON (stanar.SobaID = soba.SobaID)
								INNER JOIN studentski_dom.Tip_sobe tip_sobe ON (soba.TipSobeID = tip_sobe.TipSobeID)
									WHERE stanar.SobaID = @idSobe);
		SET @trenutanBrojStanaraUSobi = (SELECT stanar.AkademskaGodinaID, COUNT(stanar.StudentID)
											FROM studentski_dom.Stanar stanar
											WHERE stanar.AkademskaGodinaID = @idGodine AND stanar.StudentID = @idStudenta
											GROUP BY stanar.AkademskaGodinaID);
		SET @slobodnoMesta = @kapacitetSobe - @trenutanBrojStanaraUSobi;
        IF @slobodnoMesta = 0 THEN
			SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Kapacitet sobe je popunjen!';
		END IF;
    END;
$$