USE Decanate;

/*При удалении некоторой дисциплины, триггер не дает удалить дисциплины, по
которым хотя бы один человек получил экзамен (зачет).*/
DROP TRIGGER IF EXISTS disciplineDelete;
GO

CREATE TRIGGER disciplineDelete
ON Discipline INSTEAD OF DELETE
AS
IF (
SELECT count(Score.FK_Discipline_id)
FROM Score
WHERE Score.FK_Discipline_id IN
(SELECT id
FROM deleted)
) <> 0
BEGIN
    ROLLBACK TRAN
    PRINT N'This discipline can not be removed'
END
GO

DELETE FROM Discipline
    WHERE id = 1;
GO

/*Назначить нового старосту в некоторую группу. Если назначаемый на должность
старосты студент не состоит в этой группе, следует отменить транзакцию.*/

DROP TRIGGER IF EXISTS captain_zach;
GO

CREATE TRIGGER captain_zach
    ON SGroup
        FOR UPDATE
            AS
    BEGIN
    IF (
    SELECT inserted.captain_zach
    FROM inserted
    )
    NOT IN
    (
    SELECT Student.num_zach
    FROM Student
    WHERE Student.FK_SGroup_id in
    (SELECT inserted.id
    FROM inserted)
    )
    BEGIN
        ROLLBACK TRAN
        PRINT N'Student is not in the target group'
    END
END

GO

UPDATE SGroup
SET SGroup.captain_zach = 180612
WHERE SGroup.id = 3
GO

/*Не позволить добавить информацию о сданном экзамене (или зачете), если
дисциплина не соответствует специальности студента.*/
DROP TRIGGER IF EXISTS insert_score_info;
GO

CREATE TRIGGER insert_score_info
    ON Score
    FOR INSERT
    AS
    BEGIN
    IF (
    SELECT sd.FK_Specialty_id
    FROM SpecialtyDiscipline sd
        JOIN Discipline d ON sd.FK_Discipline_id = d.id
        JOIN Inserted i ON i.FK_Discipline_id = d.id
    )
    <>
    (
    SELECT sg.FK_Specialty_id
    FROM SGroup sg
    WHERE sg.id =
    (
    SELECT s.FK_SGroup_id
    FROM Student s
        JOIN Inserted i ON s.num_zach = i.FK_Student_num_zach
    )
    )
        BEGIN
        ROLLBACK TRAN
        PRINT N'This score can not be added'
    END
END

GO

INSERT Score
VALUES
    (180611, 6, '2018-12-29', N'Хор');
GO