|
|
-- 当插入新的学生时，自动在personal_info表中插入一条记录
|
|
|
CREATE OR REPLACE FUNCTION InsertPersonalInfoOnStudentInsert()
|
|
|
RETURNS TRIGGER AS $$
|
|
|
BEGIN
|
|
|
INSERT INTO personal_info (phone, email) VALUES ('', '');
|
|
|
NEW.personal_info_id := currval('personal_info_info_id_seq');
|
|
|
RETURN NEW;
|
|
|
END;
|
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
|
|
CREATE TRIGGER trigger_insert_personal_info_on_student_insert
|
|
|
BEFORE INSERT ON student
|
|
|
FOR EACH ROW EXECUTE FUNCTION InsertPersonalInfoOnStudentInsert();
|
|
|
|
|
|
-- 当删除一个教练时，自动删除其关联的personal_info记录
|
|
|
CREATE OR REPLACE FUNCTION DeletePersonalInfoOnCoachDelete()
|
|
|
RETURNS TRIGGER AS $$
|
|
|
BEGIN
|
|
|
DELETE FROM personal_info WHERE info_id = OLD.personal_info_id;
|
|
|
RETURN OLD;
|
|
|
END;
|
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
|
|
CREATE TRIGGER trigger_delete_personal_info_on_coach_delete
|
|
|
AFTER DELETE ON coach
|
|
|
FOR EACH ROW EXECUTE FUNCTION DeletePersonalInfoOnCoachDelete();
|
|
|
|
|
|
-- 当插入新的预约时，检查是否已经有重叠的时间段
|
|
|
CREATE OR REPLACE FUNCTION CheckOverlapBeforeAppointmentInsert()
|
|
|
RETURNS TRIGGER AS $$
|
|
|
DECLARE
|
|
|
overlap_count INT;
|
|
|
BEGIN
|
|
|
SELECT COUNT(*) INTO overlap_count
|
|
|
FROM appointment a
|
|
|
WHERE a.venue_id = NEW.venue_id
|
|
|
AND ((a.period = NEW.period)
|
|
|
OR (a.period < NEW.period AND a.period + INTERVAL '1 hour' > NEW.period)
|
|
|
OR (a.period > NEW.period AND a.period < NEW.period + INTERVAL '1 hour'));
|
|
|
IF overlap_count > 0 THEN
|
|
|
RAISE EXCEPTION 'There is an overlapping appointment at the same time and place.';
|
|
|
END IF;
|
|
|
RETURN NEW;
|
|
|
END;
|
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
|
|
CREATE TRIGGER trigger_check_overlap_before_appointment_insert
|
|
|
BEFORE INSERT ON appointment
|
|
|
FOR EACH ROW EXECUTE FUNCTION CheckOverlapBeforeAppointmentInsert();
|
|
|
|
|
|
-- 当更新发票金额时，确保金额不小于零
|
|
|
CREATE OR REPLACE FUNCTION EnsureInvoiceAmountNonNegative()
|
|
|
RETURNS TRIGGER AS $$
|
|
|
BEGIN
|
|
|
IF NEW.amount < 0 THEN
|
|
|
RAISE EXCEPTION 'Invoice amount cannot be negative.';
|
|
|
END IF;
|
|
|
RETURN NEW;
|
|
|
END;
|
|
|
$$ LANGUAGE plpgsql;
|
