G-9213

🆓

Warning

Always follow naming conventions for triggers.

Rule Details

Severity Level: Minor
Quality Area: Maintainability
Fixing times: 1 mins
Local-easy
Database Support: OracleDB (v7.3.4 +)

Reason

SQL identifiers share the same namespace as PL/SQL identifiers. Follow naming conventions to prevent naming conflicts, improve readability, and clearly indicate the scope without forcing the use of qualified names. A common practice is to use a prefix and/or suffix to distinguish the identifier types.

Recommendations for Triggers on Tables and Views

Choose a naming convention that includes:

either

the name of the object the trigger is added to,
any of the triggering events:
- _br_iud for Before Row on Insert, Update and Delete
- _io_id for Instead of Insert and Delete

the name of the object the trigger is added to,
the activity done by the trigger,
the suffix _trg

Recommendations for System Triggers

Name of the event the trigger is based on.

Activity done by the trigger
Suffix _trg

Examples

ddl_audit_trg
logon_trg

Example

Non-Compliant Example

create or replace trigger logon after logon on database
begin
   null; -- some monitoring activity
end;
/

Issues

Line	Column	Message
1	27	Trigger logon does not match '(?i)^[a-z][a-z0-9$#_]*_(trg\|((b\|a)r?\|io)_i?u?d?)$'.

Compliant Solution - ★★★★★

create or replace trigger logon_monitor_trg after logon on database
begin
   null; -- some monitoring activity
end;
/

Tests

Test SQL query

select owner || '.' || trigger_name as identifier,
       'Trigger ' || trigger_name || ' does not match ''' ||
       lower(#TriggerPattern#) || '''.' as message
  from dba_triggers
 where owner in (#SchemaNames#)
   and not regexp_like(trigger_name, replace(lower(#TriggerPattern#), '(?i)', null), 'i')
   and trigger_name not like 'BIN$%'
 order by identifier

Test results

Identifier	Message	Migration
DBL_OWNER.LOGON	Trigger LOGON does not match '(?i)^[a-z][a-z0-9$#_]*_(trg\|((b\|a)r?\|io)_i?u?d?)$'.	-

Parameters

Use parameters to customize the rule to your needs.

Parameter	Description	Default Value
TriggerPattern	Regular expression pattern for SQL triggers.	(?i)^[a-z][a-z0-9$#_]*_(trg\|((b\|a)r?\|io)_i?u?d?)$
SchemaNames	Comma-separated list of database schemas owning the database objects of an application.	dbl_owner

References

similar to Trivadis Database Object Naming Conventions - DML / Instead of Trigger
Covers only naming conventions for DML triggers and instead of triggers.
similar to Trivadis Database Object Naming Conventions - System Trigger
Covers only naming conventions for system triggers.