Where did my triggers go ?

You need to be careful when coding and using triggers when it comes to Datapump (or anything that transposes triggers between schemas). A lot of people make assumptions about what will happen with their triggers, and often get a nasty shock when they see the reality. Lets explore with an example

I’m going to add three triggers to the standard EMP table in the SCOTT schema


SQL> connect scott/tiger

Connected.

SQL> create or replace
  2  trigger TRG1 before insert on scott.EMP
  3  for each row
  4  declare
  5    x int;
  6  begin
  7    select count(*) into x from scott.dept;
  8  end;
  9  /

Trigger created.

SQL>
SQL>
SQL> create or replace
  2  trigger TRG2 before insert on EMP
  3  for each row
  4  declare
  5    x int;
  6  begin
  7    select count(*) into x from scott.dept;
  8  end;
  9  /

Trigger created.

SQL>
SQL>
SQL> create or replace
  2  trigger TRG3 before insert on EMP
  3  for each row
  4  declare
  5    x int;
  6  begin
  7    select count(*) into x from dept;
  8  end;
  9  /

Trigger created.

Now we’ll unload the entire schema and copy it to a new schema called SCOTT2


expdp directory=TEMP dumpfile=schema.dmp logfile=schema.log schemas=scott

Export: Release 12.1.0.2.0 - Production on Fri Nov 27 10:31:29 2015

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Username: system/********

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** directory=TEMP dumpfile=schema.dmp logfile=schema.log schemas=scott
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 256 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
. . exported "SCOTT"."ABC"                               5.898 KB       1 rows
. . exported "SCOTT"."DEPT"                              6.031 KB       4 rows
. . exported "SCOTT"."EMP"                               8.781 KB      14 rows
. . exported "SCOTT"."SALGRADE"                          5.960 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  C:\TEMP\SCHEMA.DMP
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri Nov 27 10:31:56 2015 elapsed 0 00:00:24


impdp directory=TEMP dumpfile=schema.dmp logfile=schemaimp.log schemas=scott remap_schema=scott:scott2

Import: Release 12.1.0.2.0 - Production on Fri Nov 27 10:31:57 2015

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Username: system/********

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/******** directory=TEMP dumpfile=schema.dmp logfile=schemaimp.log schemas=scott remap_schema=scott:scott2 Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT2"."ABC"                              5.898 KB       1 rows
. . imported "SCOTT2"."DEPT"                             6.031 KB       4 rows
. . imported "SCOTT2"."EMP"                              8.781 KB      14 rows
. . imported "SCOTT2"."SALGRADE"                         5.960 KB       5 rows
. . imported "SCOTT2"."BONUS"                                0 KB       0 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
ORA-39082: Object type TRIGGER:"SCOTT2"."TRG1" created with compilation warnings
ORA-39082: Object type TRIGGER:"SCOTT2"."TRG2" created with compilation warnings
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 2 error(s) at Fri Nov 27 10:32:16 2015 elapsed 0 00:00:14

SQL> select trigger_name, status
  2  from dba_triggers
  3  where owner = 'SCOTT2';

TRIGGER_NAME                   STATUS
------------------------------ --------
TRG1                           ENABLED
TRG2                           ENABLED
TRG3                           ENABLED

So at this point, everything seems to be ok. But we should look more closely…


SQL> select object_name, status
  2  from dba_objects
  3  where owner = 'SCOTT2'
  4  and object_name like 'TRG%';

OBJECT_NAME                              STATUS
---------------------------------------- -------
TRG3                                     VALID
TRG2                                     INVALID
TRG1                                     INVALID

Two of the triggers are invalid. And here’s where things really catch people out …


SQL> select trigger_name, table_owner, table_name
  2  from   dba_triggers
  3  where  owner = 'SCOTT2';

TRIGGER_NAME                   TABLE_OWNER          TABLE_NAME
------------------------------ -------------------- -----------
TRG1                           SCOTT                EMP
TRG2                           SCOTT2               EMP
TRG3                           SCOTT2               EMP

Ouch…we have a trigger owned by SCOTT2 but the triggering table is SCOTT ! Probably not what we intended.

That’s how we do it because people forget that triggers do not have to be in the same schema as the object they are triggering on. For example, I might have a schema called “SCOTT_TRIGGERS” where I define all of my triggers for tables owned by SCOTT. So even though an import might ask to remap a schema, we cant really assume that every reference to “SCOTT” in either the trigger definition or trigger body can just be changed. We can only remap the schema (hence the name of the parameter). In the case above, TRG2 is invalid because the trigger body is trying to reference SCOTT.EMP and does not have any permissions to do so.

So be careful when transposing (either by datapump or any other means) triggers between schemas.

Got some thoughts? Leave a comment Cancel reply

Enter your comment here...

Fill in your details below or click an icon to log in:

Email (required) (Address never made public)

Name (required)

Website

You are commenting using your WordPress.com account. ( Log Out / Change )

You are commenting using your Twitter account. ( Log Out / Change )

You are commenting using your Facebook account. ( Log Out / Change )

Cancel

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Share this!

Like this:

Related

Got some thoughts? Leave a comment Cancel reply