About : Trigger Creation

Hi,

Try to answer the following question.

Is it possible to create a trigger using “CREATE OR REPLACE TRIGGER TRIGGER_NAME”, where the trigger_name is already being used by another trigger under some other table ?. If your answer is YES, then this article may help you.

In general we used to say that, if we create a trigger using Create or replace statement, then the trigger will be altered with the new trigger code. But if use CREATE OR REPLACE statement to create a trigger and if the trigger name is already being used by a trigger under another table, then ORACLE will raise an ORA-04095 exception.

The following code will give you some more clarity on the above explanation:

Table 1 Creation :

create table trig1(id number(3));

Trigger 1 creation for the above table :

create or replace trigger trg_trig1

before insert on trig1

begin

null;

end;

/

Table 2 Creation :

create table trig1(id number(3));

Trigger 2 creation for the above table :

create or replace trigger trg_trig1

before insert on trig2

begin

null;

end;

/

create or replace trigger trg_trig1

*

ERROR at line 1:

ORA-04095: trigger 'TRG_TRIG1' already exists on another table, cannot replace

It

Explaination :

Thus the above code creates two table called trig1 and trig2. Trig1 has a before insert trigger called trg_trig1.Then we have the code for trg_trig1 trigger on trig2. Oracle doesn’t allow you to create the trigger by raising ORA-04095 exception.