DML триггеры в Oracle 11g

Как известно, DML триггер в СУБД Oracle может срабатывать в следующие моменты времени при выполнении команд SQL INSERT, UPDATE, DELETE и MERGE:

• BEFORE STATEMENT - перед тем, как начнут делаться изменения,
• BEFORE EACH ROW - перед изменением каждой строки таблицы,
• AFTER EACH ROW - после изменения каждой строки таблицы,
• AFTER STATEMENT - после того, как сделаны все изменения.

Что если вам понадобится в триггере AFTER STATEMENT узнать количество сток, измененных текущей командой DML?

Курсор по умолчанию в таком триггере не помогает:

SQL> CREATE TABLE test_tab (amount number);
Table created

SQL> CREATE OR REPLACE TRIGGER test_tab_as_trg
  2  AFTER INSERT OR UPDATE OR DELETE ON test_tab
  3  BEGIN
  4      dbms_output.put_line('sql%rowcount : ' || sql%rowcount);
  5  END;
  6  /
Trigger created

SQL> set serveroutput on

SQL> INSERT INTO test_tab VALUES (1);
sql%rowcount : 
1 row inserted

SQL> INSERT INTO test_tab VALUES (2);
sql%rowcount : 
1 row inserted

Мы можем также захотеть узнать сумму значений столбца amount для строк, измененных текущей командой DML, или что-то еще в этом роде.

Решение этой задачки оказывается довольно громоздким:

SQL> CREATE OR REPLACE PACKAGE test_pkg AS
  2      g_sum NUMBER;
  3  END test_pkg;
  4  /
Package created

SQL> CREATE OR REPLACE TRIGGER test_tab_bs_trg
  2  BEFORE INSERT OR UPDATE OR DELETE ON test_tab
  3  BEGIN
  4      test_pkg.g_sum := 0;
  5      dbms_output.put_line($$PLSQL_UNIT || ' : ' || test_pkg.g_sum);
  6  END;
  7  /
Trigger created

SQL> CREATE OR REPLACE TRIGGER test_tab_ar_trg
  2  AFTER INSERT OR UPDATE OR DELETE ON test_tab
  3  FOR EACH ROW
  4  BEGIN
  5      test_pkg.g_sum := test_pkg.g_sum +
  6          CASE
  7          WHEN DELETING THEN
  8              :old.amount
  9          ELSE
 10              :new.amount
 11          END;
 12      dbms_output.put_line($$PLSQL_UNIT || ' : ' || test_pkg.g_sum);
 13  END;
 14  /
Trigger created

SQL> CREATE OR REPLACE TRIGGER test_tab_as_trg
  2  AFTER INSERT OR UPDATE OR DELETE ON test_tab
  3  BEGIN
  4      dbms_output.put_line($$PLSQL_UNIT || ' : ' || test_pkg.g_sum);
  5  END;
  6  /
Trigger created

Пакет test_pkg нам нужен для того, чтобы сохранять состояние переменной g_sum между срабатываниями разных триггеров.

Выполню команды DML:

SQL> INSERT INTO test_tab VALUES (3);
TEST_TAB_BS_TRG : 0
TEST_TAB_AR_TRG : 3
TEST_TAB_AS_TRG : 3
1 row inserted

SQL> UPDATE test_tab SET amount = amount + 1;
TEST_TAB_BS_TRG : 0
TEST_TAB_AR_TRG : 2
TEST_TAB_AR_TRG : 5
TEST_TAB_AR_TRG : 9
TEST_TAB_AS_TRG : 9
3 rows updated

SQL> DELETE test_tab;
TEST_TAB_BS_TRG : 0
TEST_TAB_AR_TRG : 2
TEST_TAB_AR_TRG : 5
TEST_TAB_AR_TRG : 9
TEST_TAB_AS_TRG : 9
3 rows deleted

SQL> ROLLBACK;
Rollback complete

В Oracle 11g появилась возможность решить эту проблему и ей подобные менее громоздко. Теперь разработчики могут воспользоваться составным (compound) триггером, в котором можно написать обработчики для нескольких, или всех четырех, моментов времени, и при этом переменные триггера сохраняют состояние в течение выполнения команды DML!

Заменю пакет test_pkg и три триггера на один составной триггер:

SQL> DROP TRIGGER test_tab_as_trg;
Trigger dropped

SQL> DROP TRIGGER test_tab_ar_trg;
Trigger dropped

SQL> DROP TRIGGER test_tab_bs_trg;
Trigger dropped

SQL> DROP PACKAGE test_pkg;
Package dropped

SQL> CREATE OR REPLACE TRIGGER test_tab_trg
  2  FOR INSERT OR UPDATE OR DELETE ON test_tab
  3  COMPOUND TRIGGER
  4      l_sum PLS_INTEGER;
  5  
  6      BEFORE STATEMENT IS
  7      BEGIN
  8          l_sum := 0;
  9          dbms_output.put_line($$PLSQL_UNIT || ' BS : ' || l_sum);
 10      END BEFORE STATEMENT;
 11  
 12      BEFORE EACH ROW IS
 13      BEGIN
 14          dbms_output.put_line($$PLSQL_UNIT || ' BR : привет');
 15      END BEFORE EACH ROW;
 16  
 17      AFTER EACH ROW IS
 18      BEGIN
 19          l_sum := l_sum +
 20             CASE
 21             WHEN DELETING THEN
 22                 :old.amount
 23             ELSE
 24                 :new.amount
 25             END;
 26          dbms_output.put_line($$PLSQL_UNIT || ' AR : ' || l_sum);
 27      END AFTER EACH ROW;
 28  
 29      AFTER STATEMENT IS
 30      BEGIN
 31          dbms_output.put_line($$PLSQL_UNIT || ' AS : ' || l_sum);
 32      END AFTER STATEMENT;
 33  END;
 34  /
Trigger created

Здесь обработчик BEFORE EACH ROW добавлен только для демонстрации возможности обрабатывать все четыре доступных события в одном триггере.

Выполню команды DML:

SQL> INSERT INTO test_tab VALUES (3);
TEST_TAB_TRG BS : 0
TEST_TAB_TRG BR : привет
TEST_TAB_TRG AR : 3
TEST_TAB_TRG AS : 3
1 row inserted

SQL> UPDATE test_tab SET amount = amount + 1;
TEST_TAB_TRG BS : 0
TEST_TAB_TRG BR : привет
TEST_TAB_TRG AR : 2
TEST_TAB_TRG BR : привет
TEST_TAB_TRG AR : 5
TEST_TAB_TRG BR : привет
TEST_TAB_TRG AR : 9
TEST_TAB_TRG AS : 9
3 rows updated

SQL> DELETE test_tab;
TEST_TAB_TRG BS : 0
TEST_TAB_TRG BR : привет
TEST_TAB_TRG AR : 2
TEST_TAB_TRG BR : привет
TEST_TAB_TRG AR : 5
TEST_TAB_TRG BR : привет
TEST_TAB_TRG AR : 9
TEST_TAB_TRG AS : 9
3 rows deleted

Что ж, составной триггер справился с работой, для которой раньше требовались три триггера и пакет.

Теперь воспользуюсь составным триггером для еще одного эксперимента. Выясню возможность выполнения запроса на изменяемой таблице в разные моменты времени в ходе выполнения команды DML:

CREATE OR REPLACE TRIGGER test_tab_trg
FOR INSERT OR UPDATE OR DELETE ON test_tab
COMPOUND TRIGGER

    BEFORE STATEMENT IS
        l_count PLS_INTEGER;
    BEGIN
        SELECT count(*) INTO l_count FROM test_tab;
        dbms_output.put_line($$PLSQL_UNIT || ' BS : ' || l_count);
    EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line($$PLSQL_UNIT || ' BS : ' || sqlerrm);
    END BEFORE STATEMENT;

    BEFORE EACH ROW IS
        l_count PLS_INTEGER;
    BEGIN
        SELECT count(*) INTO l_count FROM test_tab;
        dbms_output.put_line($$PLSQL_UNIT || ' BR : ' || l_count);
    EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line($$PLSQL_UNIT || ' BR : ' || sqlerrm);
    END BEFORE EACH ROW;

    AFTER EACH ROW IS
        l_count PLS_INTEGER;
    BEGIN
        SELECT count(*) INTO l_count FROM test_tab;
        dbms_output.put_line($$PLSQL_UNIT || ' AR : ' || l_count);
    EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line($$PLSQL_UNIT || ' AR : ' || sqlerrm);
    END AFTER EACH ROW;

    AFTER STATEMENT IS
        l_count PLS_INTEGER;
    BEGIN
        SELECT count(*) INTO l_count FROM test_tab;
        dbms_output.put_line($$PLSQL_UNIT || ' AS : ' || l_count);
    EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line($$PLSQL_UNIT || ' AS : ' || sqlerrm);
    END AFTER STATEMENT;

END;
/

Выполню команды DML:

SQL> INSERT INTO test_tab VALUES (1);
TEST_TAB_TRG BS : 0
TEST_TAB_TRG BR : 0
TEST_TAB_TRG AR : ORA-04091: table AY.TEST_TAB is mutating, trigger/function may not see it
TEST_TAB_TRG AS : 1
1 row inserted

SQL> UPDATE test_tab SET amount = amount + 1 WHERE rownum = 1;
TEST_TAB_TRG BS : 1
TEST_TAB_TRG BR : ORA-04091: table AY.TEST_TAB is mutating, trigger/function may not see it
TEST_TAB_TRG AR : ORA-04091: table AY.TEST_TAB is mutating, trigger/function may not see it
TEST_TAB_TRG AS : 1
1 row updated

SQL> DELETE test_tab WHERE rownum = 1;
TEST_TAB_TRG BS : 1
TEST_TAB_TRG BR : ORA-04091: table AY.TEST_TAB is mutating, trigger/function may not see it
TEST_TAB_TRG AR : ORA-04091: table AY.TEST_TAB is mutating, trigger/function may not see it
TEST_TAB_TRG AS : 0
1 row deleted

Итак, экспериментально установлено, что в моменты времени BEFORE STATEMENT и AFTER STATEMENT можно выполнять команду SELECT на изменяемой таблице, а в моменты времени BEFORE EACH ROW и AFTER EACH ROW - нельзя (в общем случае).

SQL> DROP TRIGGER test_tab_trg;
Trigger dropped

Тема DML триггеров в Oracle 11g останется нераскрытой, если не сказать о преодолении в этой версии СУБД одного принципиального ограничения, связанного с триггерами. Чтобы проиллюстрировать это ограничение, создам два триггера, срабатывающие для одних и тех же команд DML в одни и те же моменты времени, и выполню команду DML, приводящую к их срабатыванию:

SQL> CREATE OR REPLACE TRIGGER test_tab_br1_trg
  2  BEFORE INSERT ON test_tab
  3  FOR EACH ROW
  4  BEGIN
  5      dbms_output.put_line($$PLSQL_UNIT);
  6  END;
  7  /
Trigger created

SQL> CREATE OR REPLACE TRIGGER test_tab_br2_trg
  2  BEFORE INSERT ON test_tab
  3  FOR EACH ROW
  4  BEGIN
  5      dbms_output.put_line($$PLSQL_UNIT);
  6  END;
  7  /
Trigger created

SQL> INSERT INTO test_tab VALUES (1);
TEST_TAB_BR2_TRG
TEST_TAB_BR1_TRG
1 row inserted

Как видим, вначале выполнился триггер test_tab_br2_trg, а затем триггер test_tab_br1_trg. Но почему именно в таком порядке? И что делать, если требуется другой порядок?

В версиях СУБД более ранних, чем Oracle 11g, нет возможности гарантировать порядок выполнения таких триггеров. И если нам важно, чтобы код триггера test_tab_br1_trg выполнялся прежде кода триггера test_tab_br2_trg, то добиться этого можно, выделив коды триггеров в отдельные процедуры и создав новый триггер, который бы вызывал эти процедуры в нужной последовательности.

Начиная с версии Oracle 11g для DML триггера можно указать, чтобы он срабатывал после срабатывания другого указанного триггера:

SQL> CREATE OR REPLACE TRIGGER test_tab_br2_trg
  2  BEFORE INSERT ON test_tab
  3  FOR EACH ROW
  4  FOLLOWS test_tab_br1_trg
  5  BEGIN
  6      dbms_output.put_line($$PLSQL_UNIT);
  7  END;
  8  /
Trigger created

SQL> INSERT INTO test_tab VALUES (1);
TEST_TAB_BR1_TRG
TEST_TAB_BR2_TRG
1 row inserted

И еще раз:

SQL> INSERT INTO test_tab VALUES (1);
TEST_TAB_BR1_TRG
TEST_TAB_BR2_TRG
1 row inserted

В заключение, удаляю следы экспериментов:

SQL> DROP TRIGGER test_tab_br2_trg;
Trigger dropped

SQL> DROP TRIGGER test_tab_br1_trg;
Trigger dropped

SQL> DROP TABLE test_tab;
Table dropped