Использование IF EXISTS (SELECT) в триггере BEFORE INSERT (Oracle)

Код, который у меня есть, не работает, Oracle сообщает мне, что триггер был создан с ошибками сборки. По-видимому, я не могу получить более точную информацию о том, что такое ошибка сборки...

Я действительно раньше не делал много SQL, поэтому я не очень хорошо знаком с синтаксисом. У меня есть подозрение, что это мой оператор IF EXISTS (SELECT ...) THEN, который не нравится Oracle, я искал похожие примеры в Google, но не смог найти ничего, что сработало бы в моей ситуации.

Итак о коде:

  • "дебют" - это атрибут даты (означает начало)
  • "плавник" - еще один атрибут даты (означает конец)
  • Я хочу убедиться, что даты НОВОЙ строки не перекрываются с датами других строк в таблице, если эти две строки имеют одинаковый атрибут «numInfirmier».
  • Поэтому я ВЫБИРАЮ все строки, которые имеют тот же numInfirmier, что и НОВАЯ строка, и перекрываются даты.

  • И ЕСЛИ что-то СУЩЕСТВУЕТ в этом выборе, я вызываю ошибку.

    CREATE OR REPLACE TRIGGER chev_surv
BEFORE INSERT OR UPDATE ON surveillance
FOR EACH ROW
BEGIN
    IF EXISTS (
        SELECT * FROM surveillance
        WHERE surveillance.numInfirmier = :NEW.numInfirmier
        AND ((surveillance.debut > :NEW.debut AND surveillance.debut < :NEW.fin)
        OR (surveillance.fin > :NEW.debut AND surveillance.fin < :NEW.fin))
    ) THEN
        RAISE_APPLICATION_ERROR(-20001,
        'Il ne doit pas y avoir de chevauchement entre deux périodes surveillance pour un surveillant.');
    END IF;
END;
/

Есть идеи, что случилось?


sql oracle triggers select exists
person MademoiselleC    schedule 28.03.2014    source источник
comment
Вы можете получить более точную информацию о фактических ошибках с помощью show errors, если ваш клиент поддерживает это, или select * from user_errors where type = 'TRIGGER' and name = 'CHEV_SURV'. Это доступно для любого сохраненного PL/SQL. Вы правы, вы не можете использовать exists вне условия select. Но даже если вы скомпилируете его, вы получите ошибку мутирующей таблицы — вы не можете запросить таблицу, против которой работает триггер.   -  person Alex Poole    schedule 28.03.2014
comment
Святая корова, это полезно знать XD   -  person MademoiselleC    schedule 28.03.2014
comment
показать ошибку говорит, что я могу использовать EXISTS только в инструкции SQL .... wut?   -  person MademoiselleC    schedule 28.03.2014
comment
Вы пытаетесь использовать его в контексте PL/SQL; это другой (хотя часто пересекающийся) язык. Вы можете использовать его только в подзапросе, например select ... from ... where exists (select ... from ...). Ответ Джастина показывает вам, как вы должны проверить в этом случае и почему это все еще не сработает * 8-)   -  person Alex Poole    schedule 28.03.2014

Ответы (1)


arrow_upward
8
arrow_downward

Во-первых, если вы используете SQL*Plus, когда вы создаете объект и получаете сообщение об ошибках компиляции, команда show errors покажет вам ошибки.

Если вы запустите show errors, вам сообщат, что IF EXISTS недопустимый синтаксис. Вы можете сделать что-то вроде

SELECT COUNT(*)
  INTO l_cnt
  FROM <<rest of query>>

IF( l_cnt > 0 )
THEN
  RAISE_APPLICATION_ERROR ...
END IF;

Однако, как только вы исправите ошибку компиляции, вы получите ошибки времени выполнения. В триггере уровня строки для surveillance вы обычно не можете запрашивать surveillance (можно, если все, что вы делаете, это INSERT VALUES, который гарантированно вставит только одну строку). Если вы это сделаете, вы получите ошибку мутирующего триггера во время выполнения.

С точки зрения модели данных, когда вы проектируете таблицу, в которой действительные данные для конкретной строки зависят от данных, хранящихся в других строках той же таблицы, вы, как правило, нарушаете принципы нормализации, и вам, как правило, лучше исправить ошибку. базовая модель данных.

Если вы действительно полны решимости сохранить модель данных, я бы предпочел создать материализованное представление, которое обновляется при фиксации и имеет данные только для строк, которые нарушают ваши критерии. Затем вы можете наложить ограничения на это материализованное представление, которые выдают ошибки во время фиксации, когда ваши критерии нарушаются. Для этого потребуются материализованные журналы просмотра на вашем столе.

Если вы действительно хотите сохранить модель данных и применить логику с помощью триггеров, вам понадобится классическое решение с тремя триггерами (или составной триггер с тремя частями, если вы используете 11.2 или более позднюю версию). Вы бы создали пакет с набором значений первичного ключа. Триггер оператора before инициализирует коллекцию. Триггер уровня строки будет вставлять первичные ключи строк, которые были вставлены и/или обновлены в эту коллекцию. И затем триггер оператора after будет перебирать эту коллекцию и выполнять любые проверки, которые вы хотите. Однако это много движущихся частей, поэтому я обычно не советую этого делать.

К тому же, даже если вы заставите все эти части работать, ваша логика не защитит вас в многопользовательской среде. Когда к системе одновременно обращаются несколько пользователей, вполне возможно, что один пользователь вставит строку, второй пользователь вставит другую строку с перекрывающимся диапазоном, а затем каждый сеанс будет зафиксирован. В этом случае оба набора триггеров разрешат изменение, но вы все равно останетесь с данными в таблице, которые нарушают ваши требования. Поскольку материализованное представление применяется во время фиксации, а не во время вставки, оно будет правильно работать в многопользовательской среде. Если вы хотите, чтобы триггеры работали в многопользовательской среде, вам придется еще больше усложнить их, добавив дополнительную логику, обеспечивающую сериализацию, которая блокирует запуск второго сеанса insert до тех пор, пока первый сеанс не будет зафиксирован или откатан. Это добавляет сложности, снижает масштабируемость и, в зависимости от того, как это реализовано, может превратить поддержку в кошмар.

person Justin Cave    schedule 28.03.2014
comment
С точки зрения модели данных, когда вы проектируете таблицу, в которой действительные данные для конкретной строки зависят от данных, хранящихся в других строках той же таблицы, вы, как правило, нарушаете принципы нормализации, и вам, как правило, лучше исправить ошибку. базовая модель данных. Я не мог не согласиться. Однако, к сожалению, это для домашней работы колледжа, в которой модель данных навязана, и решение проблемы с помощью триггера также навязано... Я делаю бакалавра в области разработки программного обеспечения, и наши собственные преподаватели не могут сделать приличный модель данных. Расскажите о хорошем примере :P - person MademoiselleC; 28.03.2014
comment
Неужели нет способа сделать это с помощью одного триггера? По какой-то причине я очень сомневаюсь, что учителя ожидают, что мы придумаем решение с тремя триггерами... - person MademoiselleC; 28.03.2014
comment
@MademoiselleC - Не с одним триггером, нет. Вы можете создать триггер, который срабатывает только на insert, а не на update, и ограничить свое приложение выполнением только insert ... values операций (insert ... select все равно будет вызывать изменяющиеся ошибки триггера), а затем потребуется только 1 триггер. Это не то ограничение, которое вы когда-либо рассматривали для наложения на реальную систему, но, возможно, это то, что ищут ваши инструкторы. - person Justin Cave; 28.03.2014
comment
Ситуация, которую нам дали, является только академической (т.е. определенно не должна соответствовать цели реальной системы), поэтому я думаю, что это было бы приемлемым решением. И самое главное, это то, что я могу сделать очень быстро. Спасибо вам за помощь! - person MademoiselleC; 28.03.2014