Триггер уровня инструкции для принудительного применения ограничения

Я пытаюсь реализовать триггер на уровне заявления, чтобы обеспечить соблюдение следующего правила: «Кандидат не может подавать заявки на более чем две должности за один день».

Я могу обеспечить это с помощью триггера уровня строки (как показано ниже), но я не знаю, как это сделать с помощью триггера уровня оператора, когда я не могу использовать: NEW или: OLD.

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

CREATE TABLE APPLIES(
anumber     NUMBER(6)   NOT NULL,  /* applicant number */
pnumber     NUMBER(8)   NOT NULL, /* position number */
appDate     DATE        NOT NULL, /* application date*/
CONSTRAINT APPLIES_pkey PRIMARY KEY(anumber, pnumber)
);

CREATE OR REPLACE TRIGGER app_trigger
BEFORE INSERT ON APPLIES
FOR EACH ROW
DECLARE 
  counter NUMBER;
BEGIN
  SELECT COUNT(*) INTO counter 
  FROM APPLIES 
  WHERE anumber = :NEW.anumber
  AND to_char(appDate, 'DD-MON-YYYY') = to_char(:NEW.appDate, 'DD-MON-YYYY');

  IF counter = 2 THEN
      RAISE_APPLICATION_ERROR(-20001, 'error msg');
  END IF;
END;

oracle plsql database-trigger
person nic guo    schedule 01.06.2019    source источник

Ответы (2)


arrow_upward
1
arrow_downward

Вы правы, что у вас нет значений: OLD и: NEW - поэтому вам нужно проверить всю таблицу, чтобы увидеть, выполняется ли условие (не будем называть его «ограничением», поскольку этот термин имеет конкретное значение в смысле реляционная база данных) была нарушена:

CREATE OR REPLACE TRIGGER APPLIES_AIU
  AFTER INSERT OR UPDATE ON APPLIES
BEGIN
  FOR aRow IN (SELECT ANUMBER,
                      TRUNC(APPDATE) AS APPDATE,
                      COUNT(*) AS APPLICATION_COUNT
                 FROM APPLIES
                 GROUP BY ANUMBER, TRUNC(APPDATE)
                 HAVING COUNT(*) > 2)
  LOOP
    -- If we get to here it means we have at least one user who has applied
    -- for more than two jobs in a single day.

    RAISE_APPLICATION_ERROR(-20002, 'Applicant ' || aRow.ANUMBER ||
                                    ' applied for ' || aRow.APPLICATION_COUNT ||
                                    ' jobs on ' ||
                                    TO_CHAR(aRow.APPDATE, 'DD-MON-YYYY'));
  END LOOP;
END APPLIES_AIU;

Рекомендуется добавить индекс для поддержки этого запроса, чтобы он работал эффективно:

CREATE INDEX APPLIES_BIU_INDEX
  ON APPLIES(ANUMBER, TRUNC(APPDATE));

dbfiddle здесь

Удачи.

person Bob Jarvis - Reinstate Monica    schedule 01.06.2019
comment
отличный пример и пояснение! спасибо и за ваши добрые пожелания! - person nic guo; 02.06.2019

arrow_upward
1
arrow_downward

Ваше правило включает более одной строки одновременно. Таким образом, вы не можете использовать триггер FOR ROW LEVEL: запрос к APPLIES, как вы предлагаете, вызовет ORA-04091: table is mutating exception.

Итак, ПОСЛЕ утверждения это так.

CREATE OR REPLACE TRIGGER app_trigger
AFTER INSERT OR UPDATE ON APPLIES
DECLARE 
  cursor c_cnt is
    SELECT 1 INTO counter 
    FROM APPLIES 
    group by anumber, trunc(appDate) having count(*) > 2;
  dummy number;
BEGIN
  open c_cnt;
  fetch c_cnt in dummy;
  if c_cnt%found then 
      close c_cnt;
      RAISE_APPLICATION_ERROR(-20001, 'error msg');
  end if;
  close c_cnt;
END;

Очевидно, что запрос ко всей таблице будет неэффективным при масштабировании. (Одна из причин, почему триггеры не рекомендуются для такого рода вещей). Итак, это ситуация, в которой мы можем захотеть использовать составной триггер (при условии, что мы используем 11g или новее).

person APC    schedule 01.06.2019
comment
Здесь есть одна критическая вещь ... это может не сработать. Потому что два сеанса могут вставлять одни и те же данные, и каждый будет видеть только 1, пока они не зафиксируются. Вуаля ... теперь у вас повреждение данных. Вам нужно заблокировать все строки, на которые здесь потенциально может повлиять .... это убийца масштабируемости. Взгляните сюда asktom.oracle.com/pls/apex/ - person Connor McDonald; 03.06.2019
comment
@ConnorMcDonald - это прискорбный факт, что почти каждое домашнее задание на основе триггера, которое мы видим здесь, попадает в эту ловушку. - person APC; 03.06.2019
comment
Бог с ним :-) - person Connor McDonald; 03.06.2019