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!