Ошибка запуска триггера PL / SQL: таблица мутирует, триггер может не видеть его [duplicate]
Версия stardard-SQL с использованием логической логики :
SELECT company_name
, COUNT(action = 'EMAIL' OR NULL) AS "Email"
, COUNT(action = 'PRINT' AND pagecount = 1 OR NULL) AS "Print 1 pages"
, COUNT(action = 'PRINT' AND pagecount = 2 OR NULL) AS "Print 2 pages"
, COUNT(action = 'PRINT' AND pagecount = 3 OR NULL) AS "Print 3 pages"
FROM tbl
GROUP BY company_name;
Как?
TRUE OR NULL дает TRUE. FALSE OR NULL дает NULL. NULL OR NULL дает NULL. И COUNT учитывает только ненулевые значения. Вуаля.
2 ответа
То, что вы натолкнулись, - это классическое исключение «mutating table». В ROW-триггере Oracle не позволяет вам запускать запрос к таблице, для которой задан триггер, поэтому это SELECT против TABLE1 в DELETING части триггера вызывает эту проблему.
Существует несколько способов обойти это. Возможно, лучшим в этой ситуации является использование составного триггера, который будет выглядеть примерно так:
CREATE OR REPLACE TRIGGER TABLE1_NUM_TRG
FOR INSERT OR DELETE ON TABLE1
COMPOUND TRIGGER
TYPE NUMBER_TABLE IS TABLE OF NUMBER;
tblTABLE2_IDS NUMBER_TABLE;
BEFORE STATEMENT IS
BEGIN
tblTABLE2_IDS := NUMBER_TABLE();
END BEFORE STATEMENT;
AFTER EACH ROW IS
BEGIN
IF INSERTING THEN
UPDATE TABLE2 t2
SET t2.TABLE2NUM = :new.NUM
WHERE t2.ID = :new.TABLE2_ID;
ELSIF DELETING THEN
tblTABLE2_IDS.EXTEND;
tblTABLE2_IDS(tblTABLE2_IDS.LAST) := :new.TABLE2_ID;
END IF;
END AFTER EACH ROW;
AFTER STATEMENT IS
BEGIN
IF tblTABLE2_IDS.COUNT > 0 THEN
FOR i IN tblTABLE2_IDS.FIRST..tblTABLE2_IDS.LAST LOOP
UPDATE TABLE2 t2
SET t2.TABLE2NUM = (SELECT NUM
FROM (SELECT t1.NUM
FROM TABLE1 t1
WHERE t1.TABLE2_ID = tblTABLE2_IDS(i)
ORDER BY modification_date DESC)
WHERE ROWNUM = 1)
WHERE t2.ID = tblTABLE2_IDS(i);
END LOOP;
END IF;
END AFTER STATEMENT;
END TABLE1_NUM_TRG;
Составной триггер позволяет каждой временной точке (BEFORE STATEMENT, BEFORE ROW, AFTER ROW и AFTER STATEMENT) для обработки. Обратите внимание, что точки синхронизации всегда вызывается в указанном порядке. Когда выполняется соответствующий оператор SQL (т.е. INSERT INTO TABLE1 или DELETE FROM TABLE1) и запускается этот триггер, первая выбранная точка синхронизации будет BEFORE STATEMENT, а код в обработчике BEFORE STATEMENT будет выделять PL / SQL чтобы держать кучу чисел. В этом случае номера, которые должны быть сохранены в таблице PL / SQL, будут значениями TABLE2_ID из таблицы 1. (Вместо таблицы, например, массива используется таблица PL / SQL, поскольку таблица может содержать различное количество значений, а если мы использовали массив, нам нужно заранее знать, сколько чисел нам нужно хранить. Мы не можем заранее знать, сколько строк будет зависеть от конкретного оператора, поэтому мы используем таблицу PL / SQL). Когда достигнута точка времени AFTER EACH ROW, и мы обнаруживаем, что обрабатываемый оператор является INSERT, триггер просто идет вперед и выполняет необходимое UPDATE в TABLE2, так как это не вызовет проблемы. Однако, если выполняется DELETE, триггер сохраняет TABLE1.TABLE2_ID в таблицу PL / SQL, выделенную ранее. Когда окончательная точка времени AFTER STATEMENT окончательно достигнута, таблица PL / SQL, распределенная ранее, повторяется, и для каждого найденного TABLE2_ID выполняется соответствующее обновление.
Поделитесь и наслаждайтесь.
Вы должны определить перед триггером для удаления. Попробуйте использовать два триггера
CREATE OR REPLACE TRIGGER INS_TABLE1_NUM_TRG
AFTER INSERT ON table1
FOR EACH ROW
BEGIN
UPDATE table2
SET table2num = :new.num
WHERE table2.id = :new.table2_id;
END INS_TABLE1_NUM_TRG;
CREATE OR REPLACE TRIGGER DEL_TABLE1_NUM_TRG
BEFORE DELETE ON table1
FOR EACH ROW
BEGIN
UPDATE table2
SET table2num = (SELECT num FROM
(SELECT num FROM table1 WHERE table2_id = :old.table2_id
ORDER BY modification_date DESC)
WHERE ROWNUM <= 1)
WHERE table2.id = :old.table2_id;
END DEL_TABLE1_NUM_TRG;