You have a table in Oracle database, and there are some triggers defined on that table. You want to know what all triggers are defined for that table.

Query to display triggers

To display all the triggers on a table in an Oracle database, you can use the following SQL query:

SELECT trigger_name, trigger_type, triggering_event, table_name
FROM user_triggers
WHERE table_name = 'your_table_name';

This query selects information about all triggers owned by the current user that are defined on the specified table, “your_table_name”.

If you want to see triggers from all users, you can replace “user_triggers” with “all_triggers” or “dba_triggers” and add a condition for the owner.

Here’s a brief explanation of the columns in the output:

• trigger_name: the name of the trigger.
• trigger_type: the type of trigger (e.g. “BEFORE INSERT”).
• triggering_event: the event that triggers the trigger (e.g. “INSERT”).
• table_name: the name of the table the trigger is defined on.

This query should help you to see all the triggers defined on a table in your Oracle database.

Example

Here, we are trying to display triggers on FND_NODES table

-- show all triggers on a table
select trigger_name, trigger_type, table_name from dba_triggers where table_name like 'FND_NODES%';

Output :

TRIGGER_NAME       TRIGGER_TYPE      TABLE_NAME
FNDSM              AFTER EACH ROW    FND_NODES#
UPNAME             BEFORE EACH ROW   FND_NODES#

Show Trigger definition

You can use the following query to display the definition of a trigger in an Oracle database:

SELECT dbms_metadata.get_ddl('TRIGGER', 'trigger_name', 'trigger_owner') FROM dual;

In this query, replace trigger_name with the name of the trigger you want to view, and replace trigger_owner with the schema that owns the trigger. The query will return the SQL statement used to create the trigger, including any trigger actions or conditions.

example :

-- query to show trigger definition
select dbms_metadata.get_ddl('TRIGGER', 'FNDSM', 'APPS') from dual;

this query will display the trigger definition.