Oracle - проблема создания триггера, который обновляет другую таблицу

Я прочитал документы Oracle о создании триггеров и делаю то, что он показывает, но это просто не работает. Моя цель - обновить таблицу TPM_PROJECT с минимальным STARTDATE, появляющимся в таблице TPM_TRAININGPLAN. Таким образом, каждый раз, когда кто-то обновляет столбец STARTDATE в TPM_TRAININGPLAN, я хочу обновить таблицу TPM_PROJECT. Вот что я пытаюсь:

CREATE TRIGGER Trigger_UpdateTrainingDelivery
    AFTER DELETE OR INSERT OR UPDATE OF STARTDATE
    ON TPM_TRAININGPLAN
    FOR EACH ROW WHEN (new.TRAININGPLANTYPE='prescribed')
    BEGIN
       UPDATE TPM_PROJECT SET TRAININGDELIVERYSTART = (SELECT MIN(TP.STARTDATE) FROM TPM_TRAININGPLAN TP WHERE TP.PROJECTID = new.PROJECTID AND TP.TRAININGPLANTYPE='prescribed')
       WHERE PROJECTID = new.PROJECTID
    END;

Триггер создается без ошибок, но я получаю предупреждение:

 Warnings: ---> 
   W (1): Warning: execution completed with warning
          <---

Конечно, Oracle недостаточно хорош, чтобы на самом деле сказать мне, что такое предупреждение, мне просто показывают, что есть один.

Затем, если я обновляю таблицу плана тренировки с помощью:

UPDATE TPM_TRAININGPLAN
set STARTDATE = to_date('03/12/2009','mm/dd/yyyy')
where TRAININGPLANID=15916;

Я получаю сообщение об ошибке:

>[Error] Script lines: 20-22 ------------------------
 ORA-04098: trigger 'TPMDBO.TRIGGER_UPDATETRAININGDELIVERY' is invalid and failed re-validation
 Script line 20, statement line 1, column 7

Любые идеи, что я делаю неправильно? Спасибо!

Ответы

Ответ 1

Несколько вопросов в определенном порядке.

Во-первых, в теле триггера уровня вам нужно использовать :new и :old для ссылки на новые и старые записи. Требуется ведущая ободочная кишка. Таким образом, ваше предложение WHERE должно быть

WHERE PROJECTID = :new.PROJECTID

Во-вторых, если вы используете CREATE TRIGGER в SQL * Plus, вы можете получить список ошибок и предупреждений с помощью команды SHOW ERRORS, т.е.

SQL> show errors

Вы также можете запросить таблицу DBA_ERRORS (или ALL_ERRORS или USER_ERRORS в зависимости от вашего уровня привилегий), но это не то, что вам обычно нужно прибегать.

В-третьих, если исправить ошибки синтаксиса, вы получите мутирующую ошибку таблицы, если вы используете эту логику. Триггер уровня строки в таблице A (TPM_TRAININGPLAN в этом случае) не может запрашивать таблицу A, потому что таблица может находиться в несогласованном состоянии. Вы можете обойти это, как показывает Тим ​​в своей статье, создав пакет с коллекцией, инициализируя эту коллекцию в триггере перед запуском, заполняя данные в коллекции триггером уровня, а затем обрабатывая измененные строки в триггер после утверждения. Тем не менее, сложная сложность для добавления в систему, поскольку вам придется управлять несколькими различными объектами.

Как правило, вам лучше реализовать эту логику как часть любого API, который вы используете для управления таблицей TPM_TRAININGPLAN. Если это хранимая процедура, имеет смысл добавить логику для обновления TPM_PROJECT в этой хранимой процедуре, а не поместить ее в триггер. Как правило, больно пытаться отлаживать приложение, в котором много логики встроено в триггеры, потому что разработчикам очень сложно следить за тем, какие операции выполняются. В качестве альтернативы вы можете удалить столбец TRAININGDELIVERYSTART из таблицы TPM_PROJECT и просто вычислить минимальную дату начала во время выполнения.

В-четвертых, если ваш триггер срабатывает при вставках, обновлениях и удалениях, вы не можете просто ссылаться на значения :new. :new действителен для вставок и обновлений, но он будет NULL, если вы делаете удаление. :old действителен для удалений и обновлений, но будет иметь значение NULL, если вы делаете вставку. Это означает, что вы, вероятно, должны иметь логику вдоль линий (ссылаясь на решение пакета Tim)

BEGIN
  IF inserting 
  THEN
    trigger_api.tab1_row_change(p_id => :new.projectid, p_action => 'INSERT');
  ELSIF updating
  THEN
    trigger_api.tab1_row_change(p_id => :new.projectid, p_action => 'UPDATE');
  ELSIF deleting
  THEN
    trigger_api.tab1_row_change(p_id => :old.projectid, p_action => 'DELETE');
  END IF;
END;

Ответ 2

Как предложил Джастин Пещера, вы можете рассчитать минимальную дату начала, когда вам это нужно. Это может помочь, если вы создаете индекс на (projectid, startdate);

Если у вас действительно много проектов и планов обучения, другим решением может быть создание MATERIALIZED VIEW, в котором есть все необходимые данные:

CREATE MATERIALIZED VIEW my_view
... add refresh options here ...
AS
SELECT t.projectid,  MIN(t.start_date) AS min_start_date
FROM TPM_TRAININGPLAN t
GROUP BY t.projectid;

(извините, у вас не работает Oracle, приведенный выше код для справки)