Блог

Главная - Вопросы по программированию - Проблема с триггером для предотвращения вставки (Oracle SQL)

Проблема с триггером для предотвращения вставки (Oracle SQL)

#sql #oracle #oracle11g #triggers #sql-insert

#sql #Oracle #oracle11g #триггеры #sql-вставка

Вопрос:

Я пытаюсь создать триггер, чтобы избежать вставки данных в таблицу по этой причине: если пациент мертв, он не может нанести визит. Я создал функцию, которая возвращает 1, если пациент мертв, и я вызываю ее на триггере. Это код:

 CREATE OR REPLACE FUNCTION check_d (pd INTEGER)
RETURN INTEGER
IS
d Patient.d_date%TYPE;
BEGIN
SELECT P.d_Date INTO d
FROM Patient P
WHERE P.idP=pd;
IF d <> NULL THEN
    RETURN 1;
ELSE
    RETURN 0;   
END IF;
END;

CREATE OR REPLACE TRIGGER bad_insert
BEFORE INSERT ON Visit
FOR EACH ROW
DECLARE
idd INTEGER;
dp  Visit.id_PV%TYPE;
BEGIN
SELECT V.id_PV INTO dp
FROM Visit V
WHERE id_PV=:NEW.id_PV;

idd := check_d(dp);
IF idd = 1 THEN
    RAISE_APPLICATION_ERROR(-20014,'Error. Patient Deceased.');
END IF;
END;
/

Функция должна быть в порядке, проблема в триггере.
Visit.id_PV в любом случае является внешним ключом для Patient.idP (ПЕРВИЧНЫЙ КЛЮЧ). Как я должен изменить код? Спасибо.

Комментарии:

1. Почему вы SELECT V.id_PV INTO dp включили триггер, поскольку у вас уже есть :NEW.id_PV значение?

2. Я не очень хорошо понимаю шаги, но я думаю, что выбор в триггере не сработает, :NEW.id_PV еще не существует для сравнения, когда вы выполняете вставку. Я бы попробовал, как говорит @SuperPoney, просто взять значение оттуда.

3. В вашей модели данных вы удаляете строку для пациента, когда этот пациент умирает? Возможно, было бы лучше обновить статус пациента. Но как в любом случае определяется внешний ключ, если patient он является родительским visit ?

Ответ №1:

Ваша функция неверна

 d <> NULL

Всегда будет возвращать UNKNOWN, вместо этого вам нужно проверять наличие нулей, таких как:

 d is not null

После того, как вы это исправили, вам все равно придется работать с ошибкой изменения таблицы, которую вы получаете при попытке выполнения инструкции insert (вы не получили это при попытке?). Вы ссылаетесь на ту же таблицу, в которую вы вставляете в свой триггер. Вы не можете этого сделать. Вместо этого вы должны ссылаться на нужный столбец, используя:new.id_PV .

Ответ №2:

Я думаю, что проблема находится здесь :

IF d <> NULL THEN

Измените его на :

IF d IS NOT NULL THEN

Ответ №3:

В текущем случае вы получите no_data_found исключение из-за запроса в триггере. Более того, вам не нужно возвращать значение для id_PV столбца, которое у вас уже есть как :NEW.id_PV , и такие попытки в основном заканчиваются mutating trigger ошибкой. Итак, измените свой триггер как

 CREATE OR REPLACE TRIGGER bad_insert
BEFORE INSERT ON Visit
FOR EACH ROW
BEGIN
  IF check_d(:NEW.id_PV) = 1 THEN
    RAISE_APPLICATION_ERROR(-20014, 'Error. Patient Deceased.');
  END IF;
END;
/

при избавлении от избыточных локальных переменных.

Функция также может быть краткой, например

 CREATE OR REPLACE FUNCTION check_d (pd Visit.id_PV%type) RETURN INT IS
 val INT;
BEGIN
 SELECT NVL(SUM(NVL2(P.d_Date,1,0)),0) 
   INTO val
   FROM Patient P
  WHERE P.idP=pd;
  
 RETURN val; 
END;
/

Demo