Oracle triggers 6-manage triggers

ArticleDirectory

• 1. Disable, enable, and delete triggers
• 2. Create a disabled trigger
• 4. Check the validity of the trigger.

1. Disable, enable, and delete triggers

Alter triggerTrigger_nameDisable;

Alter triggerTrigger_name enAble;

Drop triggerTrigger_name;

Example: Disable or enable all triggers on a table

Create or replace procedure settrig (tab in varchar2,
Sch in varchar default null,
Action in varchar2) is
L_action varchar2 (10): = upper (action );
Rochelle other_action varchar2 (10): = 'Disabled ';
Begin
If l_action = 'disable' then
Rochelle other_action: = 'enabled ';
End if;
For REC in (select trigger_name
From user_triggers
Where table_owner = upper (nvl (SCH, user ))
And table_name = Tab
And status = l_other_action) loop
Execute immediate 'alter trigger' | Rec. trigger_name | ''|
Rochelle action;
End loop;
End;

2. Create a disabled trigger

Create or replace trigger just_testing
After insert on ABC disable
Begin
NULL;
End;
-- Note: If you recompile a disabled trigger, the trigger is activated.

3. Data Dictionary view related to the trigger

Dba_triggers

All_triggers

User_triggers

For more information, see Oracle reference

4. Check the validity of the trigger.

SQL> select object_name,
Object_type,
Status
From user_objects
Where object_name = 'invalid_trigger ';