New Rule: never use multiple UPDATE OF in trigger event clause

[silviomarghitola]

Language Usage / Stored Objects / Triggers
Never use multiple UPDATE OF statements in the event clause of a trigger

bad:
CREATE OR REPLACE TRIGGER xyz BEFORE UPDATE OF column1 OR UPDATE OF column2 ...

good:
CREATE OR REPLACE TRIGGER xyz BEFORE UPDATE OF column1, column2 ...

[PhilippSalvisberg]

from SonarSource:

The DML events clause of a trigger is not meant to be used with multiple OF conditions. When it is, only the last one will actually be taken into account, without any error message being produced. This can lead to counter-intuitive code.

Only the UPDATE event should have an OF condition, and there should be at most one occurence of it.

Noncompliant Code Example

CREATE OR REPLACE TRIGGER myTrigger
  BEFORE UPDATE OF firstName OR UPDATE OF lastName -- Noncompliant - will *only* be triggered on updates of lastName!
  ON myTable
  FOR EACH ROW
BEGIN
  NULL;
END;
/

Compliant Solution

CREATE OR REPLACE TRIGGER myTrigger
  BEFORE UPDATE OF firstName, lastName             -- Compliant - triggered on updates of firstName or/and lastName
  ON myTable
  FOR EACH ROW
BEGIN
  NULL;
END;
/

[PhilippSalvisberg]

I ran the following test in a 19c Database instance:

REATE OR REPLACE TRIGGER emp_trg
  BEFORE UPDATE OF ename OR UPDATE OF job 
  ON emp
  FOR EACH ROW
BEGIN
  :new.sal := :old.sal*2;
END;
/

set echo on
select * from emp where empno = 7788;
update emp set ename = 'hallo' where empno = 7788;
select * from emp where empno = 7788;
rollback;
select * from emp where empno = 7788;
update emp set job = 'hallo' where empno = 7788;
select * from emp where empno = 7788;
rollback;
select * from emp where empno = 7788;

the result (console output) was:

SQL> set echo on
SQL> select * from emp where empno = 7788;

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7788 SCOTT      ANALYST         7566 19.04.1987 00:00:00       3000                    20

SQL> update emp set ename = 'hallo' where empno = 7788;

1 row updated.

SQL> select * from emp where empno = 7788;

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7788 hallo      ANALYST         7566 19.04.1987 00:00:00       3000                    20

SQL> rollback;

Rollback complete.

SQL> select * from emp where empno = 7788;

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7788 SCOTT      ANALYST         7566 19.04.1987 00:00:00       3000                    20

SQL> update emp set job = 'hallo' where empno = 7788;

1 row updated.

SQL> select * from emp where empno = 7788;

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7788 SCOTT      hallo           7566 19.04.1987 00:00:00       6000                    20

SQL> rollback;

Rollback complete.

SQL> select * from emp where empno = 7788;

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7788 SCOTT      ANALYST         7566 19.04.1987 00:00:00       3000                    20

Hence, this rule makes really sense.