Wednesday, June 15, 2011

Getting triggers information

If you want to know information about triggers owned by someone, triggers linked to a table or a trigger's code, you can query the dba_triggers table:

SQL> set linesize 150
SQL> set wrap on
SQL> select OWNER, TRIGGER_NAME, TRIGGER_TYPE, TABLE_OWNER, TABLE_NAME from dba_triggers
where owner in ('SOMEUSER') order by OWNER, TRIGGER_NAME;

OWNER TRIGGER_NAME TRIGGER_TYPE TABLE_OWNER TABLE_NAME
------------------ -------------------- ---------------- ------------------ ------------------
SOMEUSER SOMETRIGGER AFTER EACH ROW SOMEUSER SOMETABLE
SOMEUSER ATRIGGER AFTER EACH ROW SOMEUSER OTHERTABLE
SOMEUSER OTHERTRIGGER AFTER EACH ROW SOMEUSER ATABLE
SOMEUSER MYTRIGGER AFTER EACH ROW SOMEUSER MYTABLE

4 rows selected.

SQL> select TABLE_OWNER, TABLE_NAME, OWNER, TRIGGER_NAME, TRIGGER_TYPE from dba_triggers
where TABLE_OWNER in ('SOMEUSER') order by TABLE_OWNER, TABLE_NAME;

TABLE_OWNER TABLE_NAME OWNER TRIGGER_NAME TRIGGER_TYPE
------------------ ------------------ ------------------ -------------------- ----------------
SOMEUSER SOMETABLE SOMEUSER TRIGGER_ONE BEFORE STATEMENT
SOMEUSER SOMETABLE SOMEUSER TRIGGER_TWO AFTER STATEMENT
SOMEUSER SOMETABLE SOMEUSER SOMETRIGGER AFTER EACH ROW
SOMEUSER OTHERTABLE SOMEUSER TRIGGER_THREE AFTER STATEMENT

4 rows selected.

SQL> set long 20000
SQL> select TRIGGER_BODY from dba_triggers where OWNER='SOMEUSER' and TRIGGER_NAME='SOMETRIGGER';

TRIGGER_BODY
--------------------------------------------------------------------------------
BEGIN
IF INSERTING THEN
log_id ('', NEW.id);
ELSE
log_id (OLD.id, NEW.id);
END IF;
END;

No comments:

Post a Comment