101 lines
2.4 KiB
PL/PgSQL
101 lines
2.4 KiB
PL/PgSQL
-- Trigger 1
|
||
|
||
CREATE OR REPLACE FUNCTION trg_prevent_group_delete()
|
||
RETURNS TRIGGER LANGUAGE plpgsql AS $$
|
||
DECLARE cnt INTEGER;
|
||
BEGIN
|
||
SELECT COUNT(*) INTO cnt FROM Students WHERE group_id = OLD.group_id AND status = 'УЧИТСЯ';
|
||
IF cnt > 0 THEN
|
||
RAISE EXCEPTION 'Cannot delete group %: % active students exist', OLD.group_id, cnt;
|
||
END IF;
|
||
RETURN OLD;
|
||
END;
|
||
$$;
|
||
|
||
CREATE TRIGGER prevent_group_delete
|
||
BEFORE DELETE ON Groups
|
||
FOR EACH ROW
|
||
EXECUTE PROCEDURE trg_prevent_group_delete();
|
||
|
||
-- Trigger 2
|
||
CREATE OR REPLACE FUNCTION trg_lowercase_mail()
|
||
RETURNS TRIGGER LANGUAGE plpgsql AS $$
|
||
BEGIN
|
||
NEW.mail := lower(NEW.mail);
|
||
RETURN NEW;
|
||
END;
|
||
$$;
|
||
|
||
CREATE TRIGGER lowercase_mail
|
||
BEFORE INSERT OR UPDATE ON Physicals
|
||
FOR EACH ROW
|
||
EXECUTE PROCEDURE trg_lowercase_mail();
|
||
|
||
-- Trigger 3
|
||
CREATE OR REPLACE FUNCTION trg_sync_student_status()
|
||
RETURNS TRIGGER LANGUAGE plpgsql AS $$
|
||
BEGIN
|
||
UPDATE Students
|
||
SET status = NEW.new_status
|
||
WHERE id = NEW.student_id;
|
||
RETURN NEW;
|
||
END;
|
||
$$;
|
||
|
||
CREATE TRIGGER sync_student_status
|
||
AFTER INSERT ON Movement
|
||
FOR EACH ROW
|
||
EXECUTE PROCEDURE trg_sync_student_status();
|
||
|
||
-- Trigger SCD 2
|
||
CREATE OR REPLACE FUNCTION trg_supervisors_history()
|
||
RETURNS trigger AS $$
|
||
BEGIN
|
||
NEW.valid_from = NOW();
|
||
INSERT INTO Supervisors (
|
||
person,
|
||
experience,
|
||
defended_ratio,
|
||
qualification,
|
||
valid_from,
|
||
valid_to
|
||
) VALUES (
|
||
OLD.person,
|
||
OLD.experience,
|
||
OLD.defended_ratio,
|
||
OLD.qualification,
|
||
OLD.valid_from,
|
||
NOW()
|
||
);
|
||
RETURN NEW;
|
||
END;
|
||
$$ LANGUAGE plpgsql;
|
||
|
||
CREATE OR REPLACE TRIGGER trg_supervisors_scd
|
||
BEFORE UPDATE ON Supervisors
|
||
FOR EACH ROW
|
||
EXECUTE PROCEDURE trg_supervisors_history();
|
||
|
||
CREATE OR REPLACE FUNCTION check_unique_supervisor()
|
||
RETURNS TRIGGER AS $$
|
||
BEGIN
|
||
IF EXISTS (
|
||
SELECT 1 FROM Supervisors
|
||
WHERE person = NEW.person
|
||
AND experience = NEW.experience
|
||
AND defended_ratio = NEW.defended_ratio
|
||
AND qualification = NEW.qualification
|
||
AND valid_from = NEW.valid_from
|
||
AND (valid_to = NEW.valid_to OR (valid_to IS NULL AND NEW.valid_to IS NULL))
|
||
) THEN
|
||
RAISE EXCEPTION 'Дублирующая запись в таблице Supervisors. Все поля (кроме id) должны быть уникальными.';
|
||
END IF;
|
||
|
||
RETURN NEW;
|
||
END;
|
||
$$ LANGUAGE plpgsql;
|
||
|
||
CREATE TRIGGER unique_supervisor_trigger
|
||
BEFORE INSERT ON Supervisors
|
||
FOR EACH ROW
|
||
EXECUTE FUNCTION check_unique_supervisor(); |