how to check trigger status in oracle : Definition, Disable,Enable

Triggers are an important construct in Oracle. These are the codes that are fired when a row is updated, inserted, or deleted in the Oracle table. Let’s find out how to check trigger status in Oracle, how to check trigger definition in Oracle, How to find all Trigger in the table, and many more

Table of Contents

How to check Trigger status in Oracle

Trigger status can be checked using the dictionary view dba_triggers or user_triggers depending on the privileges. Status can be enabled or disabled. You can specify either the table name or trigger name to check the status

The below query checks the status from dba_triggers

select table_name, trigger_name,status
FROM dba_triggers
WHERE table_name = upper ('&table_name');
or
WHERE trigger_name = upper ('&trigger_name');

The below query checks the status from User_triggers

select table_name, trigger_name,status
FROM user_triggers
WHERE table_name = upper ('&table_name');
or
WHERE trigger_name = upper ('&trigger_name');

How to check if a trigger exists in Oracle

select table_name, trigger_name,status FROM 
dba_triggers WHERE  trigger_name = upper ('&trigger_name');

How to check trigger definition in Oracle

You can find the trigger definition by selecting the trigger body column

Set long 20000;
SELECT table_name, trigger_name, trigger_body
FROM dba_triggers
WHERE table_name = upper ('table name');
or
WHERE trigger_name = upper ('trigger name');

Set long 20000;
SELECT table_name, trigger_name, trigger_body
FROM user_triggers
WHERE table_name = upper ('table name');
or
WHERE trigger_name = upper ('trigger name');

Trigger definition can also be obtained using dbms_metadata as given below

set pagesize 0 
set long 10000 
SELECT DBMS_METADATA.GET_DDL('TRIGGER','<VIEW_NAME>','<OWNER') FROM dual;

How to find all Trigger in the table

The following query can be executed on how to check triggers on a table in Oracle

select table_name, trigger_name,status
FROM dba_triggers
WHERE table_name = upper ('&table_name');

select table_name, trigger_name,status
FROM user_triggers
WHERE table_name = upper ('&table_name');

How to disable the trigger in Oracle

ALTER TRIGGER <trigger name> DISABLE;

How to disable all the triggers on a table

We can disable all the triggers on the table in a single command

ALTER TABLE <table name> DISABLE ALL TRIGGERS;

How to enable the trigger in Oracle

ALTER TRIGGER <trigger name> ENABLE;

How to enable all the triggers on a table

We can enable all the triggers on the table in a single command

ALTER TABLE <table name> enable ALL TRIGGERS;

How to list all disabled triggers

select table_name, trigger_name,status
FROM user_triggers
WHERE status='DISABLED';

how to check if the trigger is fired in Oracle

There are various things to check out

(a) You can enable trace on the session and the tkprof output should show trigger being fired
(b) if you are inserting any rows with the trigger in another table, you can check that table for this
(c) we can add a sleep statement into the trigger for a minute or so, and in another session check the “plsql_entry_object_id” value in v$session for that SID and see if that object_id in dba_objects corresponds to your trigger name.

select owner, object_name
from dba_objects
where object_id =
( select PLSQL_ENTRY_OBJECT_ID
from v$session
where sid = &1 );

How to recompile trigger in Oracle

Sometimes trigger could become invalid, we can compile using the below command

alter trigger <trigger name> compile;

If it reports Trigger compiled with errors (or something similar), just type SHOW ERRORS for more information.

You can check the status using the below query

select object_name ,status from dba_objects where object_name='<trigger name>';
or
select object_name ,status from user_objects where object_name='<trigger name>';

How to rename the trigger in Oracle

ALTER TRIGGER <trigger name> RENAME TO <new trigger name>;

Hope you like this post on how to check trigger status in oracle, how to check trigger definition in oracle, How to find all Triggers in the table. Please do provide the feedback

Also Reads
oracle create table : Tables are the basic unit of data storage in an Oracle Database. we cover how to use Oracle create table command to create a table with a foreign key /primary key
Primary key in Oracle : primary key in oracle uniquely identifies the row in the table. It cannot be null & can be created at the time of table creation or after the table is created
Oracle check constraint : Oracle Check Constraint are used to enforce integrity rules based on logical expressions, such as comparisons. The check condition must return true or false
Oracle list all tables:we can get the List All Tables in Oracle by either querying all_tables or user_tables or dba_tables. we can select column and where clause as per the need
https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7004.htm