30 lines
921 B
PL/PgSQL
30 lines
921 B
PL/PgSQL
|
-- Триггер: запрет на запись числового результата, если есть качественный по тому же обследованию
|
||
|
|
CREATE OR REPLACE FUNCTION check_numeric_result_uniqueness ()
|
||
|
|
RETURNS TRIGGER
|
||
|
|
AS $$
|
||
|
|
DECLARE
|
||
|
|
qualitative_exists boolean;
|
||
|
|
BEGIN
|
||
|
|
SELECT
|
||
|
|
EXISTS (
|
||
|
|
SELECT
|
||
|
|
1
|
||
|
|
FROM
|
||
|
|
qualitative_result
|
||
|
|
WHERE
|
||
|
|
result_id = NEW.result_id) INTO qualitative_exists;
|
||
|
|
IF qualitative_exists THEN
|
||
|
|
RAISE EXCEPTION
|
||
|
|
USING ERRCODE = 'P0001', MESSAGE = '10005: QUALITATIVE_RESULT_ALREADY_EXISTS_FOR_THIS_EXAMINATION';
|
||
|
|
END IF;
|
||
|
|
RETURN NEW;
|
||
|
|
END;
|
||
|
|
$$
|
||
|
|
LANGUAGE plpgsql;
|
||
|
|
|
||
|
|
CREATE TRIGGER trg_check_numeric_result_uniqueness
|
||
|
|
BEFORE INSERT OR UPDATE ON numeric_result
|
||
|
|
FOR EACH ROW
|
||
|
|
EXECUTE FUNCTION check_numeric_result_uniqueness ();
|
||
|
|
|