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