Technical Note about Listing Database Trigger Names

There is a little glitch in finding out database triggers in 12.1

Summary

There are eight (8) spaces at the end of the BASE_OBJECT_TYPE column for database triggers.

Is this a bug?

References

• Database Reference 12.1
• 3.122 ALL_TRIGGERS

Initial Investigation

Naively, I though the following SQL query would give me all of the database triggers:

SELECT
    trigger_name
FROM
    dba_triggers
WHERE
    base_object_type = 'DATABASE'
ORDER BY
    trigger_name;

The result was unexpected:

no rows selected

Wider Investigation

Maybe the documentation for ALL_TRIGGERS was wrong. What were the valid values for BASE_OBJECT_TYPE?

SELECT
    base_object_type,
    count(*)
FROM
    dba_triggers
GROUP BY
    base_object_type
ORDER BY
    base_object_type;

The result was:

BASE_OBJECT_TYPE     COUNT(*)
------------------ ----------
DATABASE                    6
SCHEMA                      1
TABLE                      11
VIEW                        2

So, there are database triggers as I suspected. And the documentation appears to be correct

Deeper Investigation

Let’s do a summary by enclosing the values in single quotes:

SELECT
    ''''
    || base_object_type
    || '''' AS base_object_type,
    COUNT(*)
FROM
    dba_triggers
GROUP BY
    ''''
    || base_object_type
    || ''''
ORDER BY
    base_object_type;

The result was:

BASE_OBJECT_TYPE       COUNT(*)
-------------------- ----------
'DATABASE        '            6
'SCHEMA'                      1
'TABLE'                      11
'VIEW'                        2

This is a sixteen (16) blank-padded value! And it is the only such value in the whole view.

Correct Result

Naively, I though the following SQL query would give me all of the database triggers:

SELECT
    trigger_name
FROM
    dba_triggers
WHERE
    base_object_type = 'DATABASE        '
ORDER BY
    trigger_name;

The result was:

TRIGGER_NAME
---------------------
AW_DROP_TRG
AW_REN_TRG
AW_TRUNC_TRG
LOGMNRGGC_TRIGGER
SYSLSBY_EDS_DDL_TRIG
XDB_PI_TRIG

6 rows selected.