Fix ORA-04095: Trigger Already Exists On Another Table

One of my readers asked me what is the cause of the following error, and what is the work around for it?

CREATE OR REPLACE TRIGGER TEST_USER.TRIGGER_1
*
ERROR at line 1:
ORA-04095: trigger 'TRIGGER_1' already exists on another table, 
cannot replace it

When I saw the error, it reminded me of a situation I also encountered it. I have gotten the same error when I ran an import for PROD_USER schema, into the TEST_USER schema in the same database.

During the import process, I used the REMAP_SCHEMA clause to remap from PROD_USER to TEST_USER. What the REMAP_SCHEMA does, it will create all the objects owned by PROD_USER, in the TEST_USER schema.

What the REMAP_SCHEMA doesn’t do, it does NOT rewrite the object DDL to reference TEST_USER inside the DDL.

Let me give you an example, with the trigger DDL, since we got an error about the trigger.

Initially, the trigger in the PROD_USER schema looks like this.

CREATE or REPLACE TRIGGER PROD_USER.TRIGGER_1
...
... ON PROD_USER.TABLE_A
...
;

Notice the fact that TABLE_A is fully qualified with the schema name, in this case PROD_USER.TABLE_A.

After the import, the trigger in the TEST_USER schema looks like this:

CREATE or REPLACE TRIGGER TEST_USER.TRIGGER_1
...
... ON PROD_USER.TABLE_A
...
;

Notice here, that the trigger is created in the TEST_USER schema, however the table within the trigger is referencing the PROD_USER. The import cannot rewrite the trigger DDL.

After the import, when you try to recreate the trigger with the correct table owner, such as below

CREATE or REPLACE TRIGGER TEST_USER.TRIGGER_1
...
... ON TEST_USER.TABLE_A
...
;

You will get the error mentioned at the top of this post:

CREATE OR REPLACE TRIGGER TEST_USER.TRIGGER_1
*
ERROR at line 1:
ORA-04095: trigger 'TRIGGER_1' already exists on another table, 
cannot replace it

And this error occurs, because, indeed, that same trigger already exists on the PROD_USER.TABLE_A table.

To fix the problem, all you need to do is, drop the trigger and recreate it:

DROP TRIGGER TEST_USER.TRIGGER_1;

CREATE or REPLACE TRIGGER TEST_USER.TRIGGER_1
...
... ON TEST_USER.TABLE_A
...
;

You will no longer get the error! That simple!

If you enjoyed this article, and would like to learn more about databases, please sign up below, and you will receive

The Ultimate 3 Step Guide To Find The Root Cause Of The Slow Running SQL!