dba_triggers
Name Type
--------------------- ---------------
OWNER VARCHAR2(30)
TRIGGER_NAME VARCHAR2(30)
TRIGGER_TYPE VARCHAR2(16)
TRIGGERING_EVENT VARCHAR2(227)
TABLE_OWNER VARCHAR2(30)
BASE_OBJECT_TYPE VARCHAR2(16)
TABLE_NAME VARCHAR2(30)
COLUMN_NAME VARCHAR2(4000)
REFERENCING_NAMES VARCHAR2(128)
WHEN_CLAUSE VARCHAR2(4000)
STATUS VARCHAR2(8)
DESCRIPTION VARCHAR2(4000)
ACTION_TYPE VARCHAR2(11)
TRIGGER_BODY LONG
Descriptions of all triggers in the database.
To demonstrate dba_tiggers lets implement a simple auditing trigger. First, create two tables; one will hold an audit for inserts carried out on the other:
create table test_table(
id number
, text char(10) primary key
)
/
create table test_table_audit(
ts date primary key
, text varchar2(200)
)
/
Now create the trigger:
create or replace trigger test_table_trg
after insert
on test_table
for each row
declare
begin
insert into test_table_audit
values (sysdate, 'new row inserted.');
end test_trg;
/
Finally, query dba_triggers:
set lines 100 pages 999
select owner
, trigger_name
, trigger_type
, table_name
, status
from dba_triggers
order by
owner
, status
, table_name
/
You will see output similar to this:
OWNER TRIGGER_NAME TRIGGER_TYPE TABLE_NAME STATUS
----- ------------ --------------- ------------ --------
ANDY TEST_TRG AFTER EACH ROW TEST_TABLE ENABLED
If you want to see what triggers a table has you could use a query like this one:
set lines 100 pages 999
select trigger_name
, trigger_type
, status
from dba_triggers
where owner = '&owner'
and table_name = '&table'
order by status, trigger_name
/
To disable a trigger use the following syntax:
alter trigger <trigger_name> disable;
And to re-enable:
alter trigger <trigger_name> enable;
For more useful DBA queries click here.
| 