FORALL and STATEMENT level trigger

Just an addendum on one of the points in my previous post.

In that post, I pointed out, amongst other things, that for a FORALL .. INSERT statement, the statement level trigger fired only once (basic evidence in previous post).

This was something which surprised me.

Just from further observations:

• FORALL INSERT – Single statement – i.e. Statement trigger fires once.
• FORALL UPDATE – Multiple statements – i.e. Statement trigger fires multiple times.
• FORALL DELETE – Multiple statements – i.e. Statement trigger fires multiple times.

Which kind of backs up my original assertion in the forum thread, which was that when working with a collection and using statement level trigger work, a single INSERT/UPDATE/DELETE using TABLE (CAST ( )) should be preferable to FORALL.

The constant requirement to re-review the basics, previously known “facts”, knowledge gaps and also your assumptions is one of the things which makes working with Oracle so interesting.

SQL> SET serveroutput ON  
SQL> 
SQL> CREATE TABLE test_bulk  
  2  (test_id    NUMBER  
  3  , id_test_1  NUMBER  
  4  , id_test_2  NUMBER  
  5  ); 

Table created.

SQL> CREATE OR REPLACE TRIGGER test_asi  
  2  AFTER INSERT OR UPDATE OR DELETE
  3  ON TEST_BULK  
  4  REFERENCING NEW AS New OLD AS Old  
  5  DECLARE  
  6  begin  
  7     DBMS_OUTPUT.PUT_LINE('expensive action');  
  8  end;  
  9  /

Trigger created.

SQL> SELECT COUNT(*) FROM test_bulk;

  COUNT(*)
----------
         0

SQL> DECLARE  
  2    TYPE my_collection IS TABLE OF NUMBER;  
  3    my_collection_test  my_collection;  
  4  BEGIN  
  5     SELECT LEVEL BULK COLLECT  
  6     INTO   my_collection_test  
  7     FROM   dual  
  8     CONNECT BY LEVEL < 15;  
  9     FORALL i IN 1 .. my_collection_test.COUNT  
 10       INSERT INTO test_bulk(id_test_1,id_test_2) VALUES (1,my_collection_test(i));  
 11  END;  
 12  /  
expensive action

PL/SQL procedure successfully completed.

SQL> SELECT COUNT(*) FROM test_bulk; 

  COUNT(*)
----------
        14

SQL> DECLARE  
  2    TYPE my_collection IS TABLE OF NUMBER;  
  3    my_collection_test  my_collection;  
  4  BEGIN  
  5     SELECT LEVEL BULK COLLECT  
  6     INTO   my_collection_test  
  7     FROM   dual  
  8     CONNECT BY LEVEL < 15;  
  9     FORALL i IN 1 .. my_collection_test.COUNT  
 10       UPDATE test_bulk
 11       SET    id_test_1 = 1
 12       ,      id_test_2 = my_collection_test(i)
 13      WHERE id_test_2 = my_collection_test(i);  
 14  END;  
 14  / 
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action

PL/SQL procedure successfully completed.

SQL> DECLARE  
  2    TYPE my_collection IS TABLE OF NUMBER;  
  3    my_collection_test  my_collection;  
  4    my_collection_test2  my_collection;  
  5  BEGIN  
  6     SELECT LEVEL BULK COLLECT  
  7     INTO   my_collection_test  
  8     FROM   dual  
  9     CONNECT BY LEVEL < 15;  
 10     FORALL i IN 1 .. my_collection_test.COUNT  
 11       DELETE test_bulk
 12       WHERE  id_test_2 = my_collection_test(i)
 13       RETURNING id_test_2 BULK COLLECT INTO my_collection_test2;  
 14  END;  
 15  /  
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action

PL/SQL procedure successfully completed.

SQL> SELECT COUNT(*) FROM test_bulk;

  COUNT(*)
----------
         0

SQL>