в 13:45, 26 мая 2010

2010-05-26T13:45:46Z

Admin: 1 версия

2010-05-26T10:08:10Z

1 версия

Новая страница

==30. Create alter trigger command by query user_triggers table==



<source lang="sql">
SQL>
SQL> SELECT "ALTER TRIGGER " || trigger_name || " DISABLE;"
2 FROM user_triggers
3 where rownum < 50
4 ORDER BY table_name;
"ALTERTRIGGER"||TRIGGER_NAME||"DISABLE;"
-----------------------------------------------------
ALTER TRIGGER APPLICATION_USERS_IOI DISABLE;
ALTER TRIGGER HRC_ORG_SITE_TRIG DISABLE;
ALTER TRIGGER STUDENT_TRIG DISABLE;
ALTER TRIGGER CAPTURE_TRACE_FILES DISABLE;
ALTER TRIGGER WHAT_COLUMN DISABLE;
SQL>
SQL></source>

==30. find out all triggers from user_triggers table==



<source lang="sql">
SQL>
SQL>
SQL> SELECT table_name, trigger_name, trigger_type type,
2 triggering_event event, status
3 FROM user_triggers
4 where rownum < 2
5 ORDER BY table_name;
TABLE_NAME TRIGGER_NAME TYPE
-------------------- ------------------------------ ----------------
EVENT
--------------------------------------------------------------------------------
STATUS
--------
HRC_ORG_SITE HRC_ORG_SITE_TRIG INSTEAD OF
INSERT OR UPDATE OR DELETE
DISABLED

SQL>
SQL></source>

==30. Getting Information on Triggers==

<p>You can get information on your triggers from the user_triggers view.</p>



<source lang="sql">
SQL> desc user_triggers;
Name Null? Type
---------------------
TRIGGER_NAME VARCHAR2(30)
TRIGGER_TYPE VARCHAR2(16)
TRIGGERING_EVENT VARCHAR2(227) --Event that causes the trigger to fire.
TABLE_OWNER VARCHAR2(30) --User who owns the table that the trigger references.
BASE_OBJECT_TYPE VARCHAR2(16) --Type of the object referenced by the trigger.
TABLE_NAME VARCHAR2(30) --Name of the table referenced by the trigger.
COLUMN_NAME VARCHAR2(4000) --Name of the column referenced by the trigger.
REFERENCING_NAMES VARCHAR2(128) --Name of the old and new aliases.
WHEN_CLAUSE VARCHAR2(4000) --Trigger condition that limits when the trigger runs its code.
STATUS VARCHAR2(8) --Whether the trigger is enabled or disabled. Set to ENABLED or DISABLED.
DESCRIPTION VARCHAR2(4000) --Description of trigger.
ACTION_TYPE VARCHAR2(11) --Action type of the trigger. Set to CALL or PL/SQL.
TRIGGER_BODY LONG --Code contained in the trigger body.</source>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL Tutorial/System Tables Data Dictionary/user triggers - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия