G-7720

🆓

Error

Never use multiple UPDATE OF in trigger event clause.

Rule Details

Severity Level: Blocker
Quality Area: Maintainability
Fixing times: 5 mins
Quick Fix | Local-medium
Database Support: OracleDB (v7.3.4 +)

Reason

A DML trigger can have multiple triggering events separated by or like before insert or delete or update of some_column. If you have multiple update of separated by or, only one of them (the last one) is actually used and you get no error message, so you have a bug waiting to happen. Instead you always should use a single update of with all columns comma-separated, or an update without of if you wish all columns.

Example

Non-Compliant Example

create or replace trigger dept_br_u
before update of department_id or update of department_name
on departments for each row
begin
   -- will only fire on updates of department_name
   insert into departments_log (
      department_id
     ,department_name
     ,modification_date)
   values (
      :old.department_id
     ,:old.department_name
     ,sysdate);
end;
/

Issues

Line	Column	Message
2	8	Multiple UPDATE OF in trigger dept_br_u.

Compliant Solution - ★★★★★

create or replace trigger dept_br_u
before update of department_id,department_name
on departments for each row
begin
   insert into departments_log (
      department_id
     ,department_name
     ,modification_date)
   values (
      :old.department_id
     ,:old.department_name
     ,sysdate);
end;
/

Parameters

Use parameters to customize the rule to your needs.

Parameter	Description	Default Value
DisableAllQuickFix	Comma-separated list of rules for which a quick fix should not be applied to all the problems in a file.	Core G-3130

References

same as plsql:TriggerOfConditionsCheck
same as Trivadis G-7720