56 lines
1.6 KiB
PL/PgSQL
56 lines
1.6 KiB
PL/PgSQL
|
-- Проверка: выписка должна быть позже поступления
|
||
|
|
CREATE OR REPLACE FUNCTION check_discharge_after_admission ()
|
||
|
|
RETURNS TRIGGER
|
||
|
|
AS $$
|
||
|
|
DECLARE
|
||
|
|
admission_time timestamp;
|
||
|
|
BEGIN
|
||
|
|
SELECT
|
||
|
|
admission_datetime INTO admission_time
|
||
|
|
FROM
|
||
|
|
hospital_admission
|
||
|
|
WHERE
|
||
|
|
hospital_admission_id = NEW.hospital_admission_id;
|
||
|
|
IF NEW.discharge_datetime <= admission_time THEN
|
||
|
|
RAISE EXCEPTION
|
||
|
|
USING ERRCODE = 'P0001', MESSAGE = '10003: DISCHARGE_BEFORE_ADMISSION';
|
||
|
|
END IF;
|
||
|
|
RETURN NEW;
|
||
|
|
END;
|
||
|
|
$$
|
||
|
|
LANGUAGE plpgsql;
|
||
|
|
|
||
|
|
CREATE TRIGGER trg_check_discharge_after_admission
|
||
|
|
BEFORE INSERT OR UPDATE ON hospital_discharge
|
||
|
|
FOR EACH ROW
|
||
|
|
EXECUTE FUNCTION check_discharge_after_admission ();
|
||
|
|
|
||
|
|
-- Триггер: нельзя создать выписку, если уже есть выписка по этой госпитализации
|
||
|
|
CREATE OR REPLACE FUNCTION check_unique_discharge ()
|
||
|
|
RETURNS TRIGGER
|
||
|
|
AS $$
|
||
|
|
DECLARE
|
||
|
|
discharge_count integer;
|
||
|
|
BEGIN
|
||
|
|
SELECT
|
||
|
|
COUNT(*) INTO discharge_count
|
||
|
|
FROM
|
||
|
|
hospital_discharge
|
||
|
|
WHERE
|
||
|
|
hospital_admission_id = NEW.hospital_admission_id
|
||
|
|
AND hospital_discharge_id <> COALESCE(NEW.hospital_discharge_id, -1);
|
||
|
|
IF discharge_count > 0 THEN
|
||
|
|
RAISE EXCEPTION
|
||
|
|
USING ERRCODE = 'P0001', MESSAGE = '10004: DISCHARGE_ALREADY_EXISTS';
|
||
|
|
END IF;
|
||
|
|
RETURN NEW;
|
||
|
|
END;
|
||
|
|
$$
|
||
|
|
LANGUAGE plpgsql;
|
||
|
|
|
||
|
|
CREATE TRIGGER trg_check_unique_discharge
|
||
|
|
BEFORE INSERT OR UPDATE ON hospital_discharge
|
||
|
|
FOR EACH ROW
|
||
|
|
EXECUTE FUNCTION check_unique_discharge ();
|
||
|
|
|