Can an Oracle database trigger fire more than once during the execution of a single atomic piece of DML?

Here is a bit of Oracle 101 code creating some test data that will be used in the remainder of this blog article, and a simple row level trigger for auditing purposes.  Easy stuff, yes?

SQL>
SQL> create table tblTest(n1 number);

Table created.

SQL> insert into tblTest(n1) values (1);

1 row created.

SQL> insert into tblTest(n1) values (1);

1 row created.

SQL> insert into tblTest(n1) values (1);

1 row created.

SQL> insert into tblTest(n1) values (1);

1 row created.

SQL> insert into tblTest(n1) values (10);

1 row created.

SQL> insert into tblTest(n1) values (100);

1 row created.

SQL> insert into tblTest(n1) values (1000);

1 row created.

SQL> alter table tblTest
2      add n2 number;

Table altered.

SQL> select *
2      from tblTest
3 /

        N1         N2
---------- ----------
         1
         1
         1
         1
        10
       100
      1000

7 rows selected.

SQL>
SQL>
SQL>
SQL>
SQL>
SQL> create or replace trigger trgTest
2     after insert or update or delete
3      on tblTest
4       for each row
5     declare
6      vState varchar2(100);
7      vOperation varchar2(50);
8     begin
9       vState := 'N1(old)=' || nvl(to_char(:old.n1), 'null');
10      vState := vState || ' N1(new)=' || nvl(to_char(:new.n1), 'null');
11      vState := vState || ' N2(old)=' || nvl(to_char(:old.n2), 'null');
12      vState := vState || ' N2(new)=' || nvl(to_char(:new.n2), 'null');
13
14      if inserting then
15         vOperation := 'inserting';
16       elsif updating then
17         vOperation := 'updating';
18       elsif deleting then
19         vOperation := 'deleting';
20       else vOperation := 'unknown';
21      end if;
22
23      dbms_output.put_line('row level trigger: ' || vOperation || ' ' || vState);
24    end trgTest;
25 /

Trigger created.

SQL>
SQL>
SQL>

So having built up some test data, and a simple row level trigger, and showing some code with Oracle SQL%ROWCOUNT too, is the output from execution of the following code what I expect?

SQL>
SQL>
SQL>
SQL> declare
2    begin
3      update tblTest
4        set n2 = n1
5         where n1 = 1;
6      dbms_output.put_line('There were ' || to_char(sql%rowcount) || ' rows updated');
7
8      delete from tblTest
9        where n1 in (10, 100, 1000);
10     if sql%rowcount = 3 then
11        dbms_output.put_line('3 rows as expected were deleted');
12     end if;
13   end;
14 /
row level trigger: updating N1(old)=1 N1(new)=1 N2(old)=null N2(new)=1
row level trigger: updating N1(old)=1 N1(new)=1 N2(old)=null N2(new)=1
row level trigger: updating N1(old)=1 N1(new)=1 N2(old)=null N2(new)=1
row level trigger: updating N1(old)=1 N1(new)=1 N2(old)=null N2(new)=1
There were 4 rows updated
row level trigger: deleting N1(old)=10 N1(new)=null N2(old)=null N2(new)=null
row level trigger: deleting N1(old)=100 N1(new)=null N2(old)=null N2(new)=null
row level trigger: deleting N1(old)=1000 N1(new)=null N2(old)=null N2(new)=null
3 rows as expected were deleted

PL/SQL procedure successfully completed.

SQL>
SQL>

Yes, absolutely what I expected. It is so lovely where the experiment and the observed results are the same, in IT and in chemistry.

To summarise however, the answer as output, 4 rows updated (there were 4 rows in tblTest where the value of N1 is 1) and 3 rows deleted (there are 3 words in tblTest that has values for N1 10, 100, and 1000). Furthermore the trigger is firing with expected values populated in :new and :old once per row. There is neither anything challenging nor exciting here, and all I have been doing is painting the picture on some basic SQL%ROWCOUNT and TRIGGER test case functionality/usage.

Now, moving on from Oracle 101, and let’s call this Oracle 101a, how about the Oracle MERGE statement, the other often forgotten DML family member (so the full DML set is SELECT, UPDATE, DELETE, INSERT, and MERGE, and even then SELECT is only an affiliate member). Here is a trace from the execution of the MERGE statement, from a freshly reinitialised tblTest.

SQL>
SQL>
SQL>
SQL> declare
2    begin
3      merge into tblTest t0
4        using (select rownum x0 from dual connect by rownum <=10) t1
5          on (t0.n1 = t1.x0)
6            when matched then
7               update set n2 = n1
8            when not matched then
9               insert (n1, n2) values (x0, x0);
10      dbms_output.put_line(to_char(sql%rowcount) || ' rows were touched in the merge DML');
11   end;
12 /
row level trigger: updating N1(old)=1 N1(new)=1 N2(old)=null N2(new)=1
row level trigger: updating N1(old)=1 N1(new)=1 N2(old)=null N2(new)=1
row level trigger: updating N1(old)=1 N1(new)=1 N2(old)=null N2(new)=1
row level trigger: updating N1(old)=1 N1(new)=1 N2(old)=null N2(new)=1
row level trigger: updating N1(old)=10 N1(new)=10 N2(old)=null N2(new)=10
row level trigger: inserting N1(old)=null N1(new)=5 N2(old)=null N2(new)=5
row level trigger: inserting N1(old)=null N1(new)=8 N2(old)=null N2(new)=8
row level trigger: inserting N1(old)=null N1(new)=3 N2(old)=null N2(new)=3
row level trigger: inserting N1(old)=null N1(new)=2 N2(old)=null N2(new)=2
row level trigger: inserting N1(old)=null N1(new)=6 N2(old)=null N2(new)=6
row level trigger: inserting N1(old)=null N1(new)=7 N2(old)=null N2(new)=7
row level trigger: inserting N1(old)=null N1(new)=4 N2(old)=null N2(new)=4
row level trigger: inserting N1(old)=null N1(new)=9 N2(old)=null N2(new)=9
13 rows were touched in the merge DML

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL>

Hmm, 13 rows. Let me look at the data now. I need to convince myself that is right.

SQL>
SQL> select *
2      from tblTest
3        order by n1 asc;

        N1         N2
---------- ----------
         1          1
         1          1
         1          1
         1          1
         2          2
         3          3
         4          4
         5          5
         6          6
         7          7
         8          8
         9          9
        10         10
       100
      1000

15 rows selected.

SQL>
SQL>
SQL>

Of course, 15 rows in total, 2 that haven’t been changed (100 and 1000), and the remaining 13 either inserted (N1 having values 2 to 9) or updated (where N1 has a value of 1 or 10). In this case, clearly, SQL%ROWCOUNT is the sum of the number of inserted and updated rows. Obviously there is no granularity in the SQL%ROWCOUNT sum – from SQL%ROWCOUNT I can only determine the total number of rows in tblTest ‘touched’ by the MERGE, but specifically I cannot tell how many rows were discretely inserted and how many were updated. A few conditionals, perhaps another query, a scope variable maybe, and/or some simple algebra and this could be worked out however. Furthermore I can see 13 lines output above prefixed by “row level trigger:”, all with the expected and unsurprising values from :new and :old.

In summary, I have demonstrated above that a single piece of DML results in SQL%ROWCOUNT having the value for the number of affected rows, that the Oracle MERGE statement behaves predictably too, and that the trigger fires once per row (and after all, it is a ‘row level’ trigger, but I still need to point this out).

Do you see where I am going with this blog article now? Do you feel a sense of entrapment looming?

The big remaining question – what about the DELETE clause of the MERGE statement? What magic value will SQL%ROWCOUNT be assigned after execution of a single MERGE statement. Recall too that the DELETE clause of the MERGE statement was retrofitted as an ‘enhancement’ to the existing MERGE statement in 10g so perhaps it behaves differently than you might expect.

Here is the same snippet of code to that executed above, but this time the DELETE clause is included into the MERGE statement syntax (line 11 or 8 in the code below).

SQL>
SQL>
SQL>
SQL> declare
2     begin
3       merge into tblTest t0
4         using (select rownum x0 from dual connect by rownum <=10) t1
5           on (t0.n1 = t1.x0)
6            when matched then
7               update set n2 = n1
8               delete where n1 <= 4
9            when not matched then
10              insert (n1, n2) values (x0, x0);
11
12       dbms_output.put_line(to_char(sql%rowcount) || ' rows were touched in the merge DML');
13    end;
14 /
row level trigger: updating N1(old)=1 N1(new)=1 N2(old)=null N2(new)=1
row level trigger: deleting N1(old)=1 N1(new)=null N2(old)=1 N2(new)=null
row level trigger: updating N1(old)=1 N1(new)=1 N2(old)=null N2(new)=1
row level trigger: deleting N1(old)=1 N1(new)=null N2(old)=1 N2(new)=null
row level trigger: updating N1(old)=1 N1(new)=1 N2(old)=null N2(new)=1
row level trigger: deleting N1(old)=1 N1(new)=null N2(old)=1 N2(new)=null
row level trigger: updating N1(old)=1 N1(new)=1 N2(old)=null N2(new)=1
row level trigger: deleting N1(old)=1 N1(new)=null N2(old)=1 N2(new)=null
row level trigger: updating N1(old)=10 N1(new)=10 N2(old)=null N2(new)=10
row level trigger: inserting N1(old)=null N1(new)=5 N2(old)=null N2(new)=5
row level trigger: inserting N1(old)=null N1(new)=8 N2(old)=null N2(new)=8
row level trigger: inserting N1(old)=null N1(new)=3 N2(old)=null N2(new)=3
row level trigger: inserting N1(old)=null N1(new)=2 N2(old)=null N2(new)=2
row level trigger: inserting N1(old)=null N1(new)=6 N2(old)=null N2(new)=6
row level trigger: inserting N1(old)=null N1(new)=7 N2(old)=null N2(new)=7
row level trigger: inserting N1(old)=null N1(new)=4 N2(old)=null N2(new)=4
row level trigger: inserting N1(old)=null N1(new)=9 N2(old)=null N2(new)=9
13 rows were touched in the merge DML

PL/SQL procedure successfully completed.

SQL>
SQL> select *
2      from tblTest
3        order by n1 asc;

        N1         N2
---------- ----------
         2          2
         3          3
         4          4
         5          5
         6          6
         7          7
         8          8
         9          9
        10         10
       100      
      1000

11 rows selected.
SQL>

Oh, this UPSERT is really quite neat, and everything appears so predictable – the latter point not to be undervalued. To summarise again however,  where N1 = 1, a join match occurs (after the UPDATE) and the 4 rows are deleted, whereas for N1 = 10, N1 and N2 merely updated. For all other rows with values of N1 between 2 and 9, the N1/N2 row values are inserted. This is a very concise multi-operation piece of SQL, although its execution may lead to performance issues as the WHEN NOT MATCHED clause possibly performs some sort of OUTER JOIN on tbltest to satisfy the query (that in this case, because tblTest only has 7 initial rows, is far from a problem, but I might wish to reconsider MERGE use in larger tables).

To summarise again “13 rows were touched in the merge DML” can, by hand, be rationalised as:

4 rows, that’s deleting everything that was initially in the table with N1 = 1
8 rows, that’s inserting N1/N2 = 2 through 9
1 row, that’s updating N1 = 10

and 4 + 8 + 1 = 13 = SQL%ROWCOUNT

Again, as above, SQL%ROWCOUNT alone cannot solely be used to ascertain the individual number of rows inserted, updated, or deleted.  However SQL%ROWCOUNT behaves as one would expect for DML in the Oracle MERGE.

Before continuing, can you spot the oddity? There is a big space below, purely for dramatic effect, but scroll down to discover more.

Q. What is wrong.
A. The TRIGGER fired 17 times (there are 17 outputted lines above beginning “row level trigger:”, not 13)

Q. Did I expect this
A. No

Q. Is this insane
A. Yes. Furthermore the for each row clause in the create trigger statement is for each row, not one or more times for each row.

Q. Can I rationalise why this is happening
A. Yes

Q. Are :new and :old being populated correctly
A. Yes and no.

The issue is with the rows that are being deleted. Just look at one of the 1’s pair (there are 4 of them, 4 1’s in tblTest, so 17 – 4 = 13, my comfort number, the value of SQL%ROWCOUNT after execution of this single MERGE statement and single piece of DML) that I have copy/pasted below:

row level trigger: updating N1(old)=1 N1(new)=1 N2(old)=null N2(new)=1
row level trigger: deleting N1(old)=1 N1(new)=null N2(old)=1 N2(new)=null

The trigger is firing for the UPDATE in the MERGE statement, then it is also firing for the DELETE. So the question – if the row is being DELETED by a single piece of DML, not two pieces of DML, do you care about some Oracle internal step where it is updated prior to it being immediately DELETEd in a single SQL statement. The answer is No. Secondly, are the :old and :new values for N1 and N2 what I expect and have observed previously? No is the answer here too. If this TRIGGER were being used to audit changes in a highly regulated environment such as a bank or pharmaceutical organisation, would it be possible to reconstitute the data change history from the audit trail for auditing purposes? The answer is “not without a bit of head scratching”.

Why has Oracle done this? I suspect it is for one of two reasons, and neither is related to ANSI or other standards (where this type of behaviour is not specified)?

The reasons are:

1. Crap programming – the developers likely just invoked the existing private API methods for UPDATE and DELETE, that resulted in the TRIGGER firing more than once due to existing encapsulated behaviour, when they retrofitted the MERGE delete ‘enhancement’.
2. Ease of implementation of UNDO.

All of this aside, even if the behaviour were to be explicitly and clearly outlined in the Oracle documentation, perhaps even retrospectively, would you expect a trigger to fire more than once during the execution of a single DML statement? Furthermore, if you managed to convince me that this behaviour is ‘by design’, what would that say about the software engineers or company as a whole, where they would consciously design this type of “gotcha” into their proprietary (this is an Oracle specific implementation) language.  Why would you do that? No matter what way you look at this, the observed behaviour is disappointing to say the least, and I suspect it has resulted in many lost developer hours and data corruption.

The code in this blog article was developed and tested against the following Oracle version.

SQL>
SQL> set linesize 132 wrap off tab off trunc off
SQL>
SQL> SELECT *
2       FROM v$version;
BANNER                                                                           CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production    0
PL/SQL Release 12.1.0.2.0 - Production                                           0
CORE 12.1.0.2.0 Production                                                       0
TNS for Linux: Version 12.1.0.2.0 - Production                                  0
NLSRTL Version 12.1.0.2.0 - Production                                           0
SQL>
SQL> 

— Published by Mike, 10:54 13 February 2017