Home » RDBMS Server » Server Utilities » Triggers does not fired while doing import in oracle9i (Oracle9i + HP-UX11iv2)
Triggers does not fired while doing import in oracle9i [message #471761] Tue, 17 August 2010 04:05 Go to next message
kumasudh
Messages: 14
Registered: August 2010
Junior Member

Hello Gurus,

I am trying one simple operation on Oracle9i DB. I am exporting a table data and trying to import it back. The triggers associated with the insert operation for this table are not being fired during import. Here is the commandline i am using to achieve the same.

1) Exporting the table data.

$ORACLE_HOME/bin/exp SMS_60/SMS_60 file=callfwd.dmp tables=callFwd_customerList TRIGGERS=Y

2) Importing the same data.
$ORACLE_HOME/bin/imp SMS_60/SMS_60 file=callfwd.dmp tables=callFwd_customerList ignore=Y

Import ends with success with all the rows imported in the table but the triggers associated to this "insert" operation. Please help if i am missing something in terms of options for import and export utilty ?

Best Regards
Re: Triggers does not fired while doing import in oracle9i [message #471766 is a reply to message #471761] Tue, 17 August 2010 04:25 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10613
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Your title says
Quote:
Triggers does not fired while doing import in oracle9i

And your posting says
Quote:

Import ends with success with all the rows imported in the table but the triggers associated to this "insert" operation

So what is your question? .
First question is normal behavior

Quoting the docs
Quote:
The order of import is as follows: new tables are created, data is imported and indexes are built, triggers are imported, integrity constraints are enabled on the new tables, and any bitmap, function-based, and/or domain indexes are built. This sequence prevents data from being rejected due to the order in which tables are imported. This sequence also prevents redundant triggers from firing twice on the same data (once when it is originally inserted and again during the import).


Second question is not clear.
First , this is not an "insert" operation. Import will
try to append data with ignore=y.

[Updated on: Tue, 17 August 2010 04:26]

Report message to a moderator

Re: Triggers does not fired while doing import in oracle9i [message #471768 is a reply to message #471766] Tue, 17 August 2010 04:35 Go to previous messageGo to next message
kumasudh
Messages: 14
Registered: August 2010
Junior Member
Hi Mahesh,

Thanks a lot for the reply. I am not expert at oracle utility usages. Sorry for putting the question in a wrong way. Let me rephrase it again.

I have a table and for this i have created triggers for all the DML operations. These triggers get fired when i manually insert/update/delete rows in this table. Now, i have exported the data of this table with exp tool with the options given in the question. After this, i delete all the rows from the table. Now, i import the data from the export data file with the options given in the original question. BUT the import does not fire the trigger for inserting the rows in the table.

I want to know if i am missing any option ? Your help is greatly appreciated.

Best Regards
Re: Triggers does not fired while doing import in oracle9i [message #471772 is a reply to message #471768] Tue, 17 August 2010 04:48 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10613
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
As said,
It is normal behavior.
Read the previous post. Triggers are created after data is imported.
Export/Import tools are meant to be used to move the data around logically.
You export some objects/schema and import them as-is from the dump file.

[Updated on: Tue, 17 August 2010 04:54]

Report message to a moderator

Re: Triggers does not fired while doing import in oracle9i [message #471778 is a reply to message #471772] Tue, 17 August 2010 05:17 Go to previous messageGo to next message
kumasudh
Messages: 14
Registered: August 2010
Junior Member
Hi Mahesh,

Thanks once again for your reply. Are you saying that existing triggers for a given table, will not be fired automatically when i import the data from the exported data file ? My question is, how can i make the existing triggers to be fired while importing the data. While going thru the oracle document, i could see the follwing statement.

"The IGNORE parameter of the IMP command determines whether triggers fire during import operations:

If IGNORE=N (default) and the table already exists, then import does not change the table and no existing triggers fire.
If the table does not exist, then import creates and loads it before any triggers are defined, so again no triggers fire.
If IGNORE=Y, then import loads rows into existing tables. Any existing triggers fire, and indexes are updated to account for the imported data."



I request you to tell me if there exist any way which can make the existing triggers for a given table fired at the time of import ?

Best Regards
Re: Triggers does not fired while doing import in oracle9i [message #471782 is a reply to message #471778] Tue, 17 August 2010 05:33 Go to previous messageGo to next message
John Watson
Messages: 4081
Registered: January 2010
Location: Global Village
Senior Member
Hi - are you sure you are reading the Oracle documentation, not just something written by a third party? The Oracle doc describig import,
http://download.oracle.com/docs/cd/B10501_01/server.920/a96652/ch02.htm#1005473
does not include the statement you quote.
Re: Triggers does not fired while doing import in oracle9i [message #471783 is a reply to message #471782] Tue, 17 August 2010 05:43 Go to previous messageGo to next message
kumasudh
Messages: 14
Registered: August 2010
Junior Member
Hi John,

Please find the link here. You could find that text under the section "Do Import and SQL*Loader Fire Triggers? " at this URL.


download.oracle.com/docs/cd/B14117_01/appdev.101/b10795/adfns_tr.htm

Best Regards
Re: Triggers does not fired while doing import in oracle9i [message #471784 is a reply to message #471778] Tue, 17 August 2010 05:47 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10613
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
I don't have a 9i database around.
Tested in 10g and it works for me.
Are you sure the trigger is not firing?

Re: Triggers does not fired while doing import in oracle9i [message #471792 is a reply to message #471784] Tue, 17 August 2010 06:09 Go to previous messageGo to next message
John Watson
Messages: 4081
Registered: January 2010
Location: Global Village
Senior Member
You're right! sorry I doubted you. But it works for me (11.2):
jw> create table t1 (c1 date);

Table created.

jw> create table t2 (c1 date);

Table created.

jw> insert into t1 values(sysdate);

1 row created.

jw> commit;

Commit complete.

jw> create trigger trig1 after insert on t1 begin
  2  insert into t2 values(sysdate);
  3  end;
  4  /

Trigger created.

jw> host exp jon/jon file=t1.dmp tables=t1

Export: Release 11.2.0.1.0 - Production on Tue Aug 17 12:07:41 2010

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


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                             T1          1 rows exported
Export terminated successfully without warnings.

jw> host imp jon/jon file=t1.dmp full=y ignore=y

Import: Release 11.2.0.1.0 - Production on Tue Aug 17 12:07:47 2010

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


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing JON's objects into JON
. importing JON's objects into JON
. . importing table                           "T1"          1 rows imported
Import terminated successfully without warnings.

jw> select * from t2;

C1
---------
17-AUG-10

jw>

Re: Triggers does not fired while doing import in oracle9i [message #471798 is a reply to message #471784] Tue, 17 August 2010 06:28 Go to previous messageGo to next message
kumasudh
Messages: 14
Registered: August 2010
Junior Member
Hi Mahesh,

Yes, I am sure the triggers are not fired. I tried on Oracle 10g as well. There also i dont see triggers get fired. Could you please copy/paste all the options you have used while doing exp/imp ?

Best Regards
Sudhir
Re: Triggers does not fired while doing import in oracle9i [message #471805 is a reply to message #471798] Tue, 17 August 2010 06:57 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10613
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Pretty much the same as John Watson's session.

:
  1  create or replace trigger ttestTrig
  2  after insert on dept
  3  for each row
  4  begin
  5  insert into ttest values (:new.dname);
  6* end;
SQL> /

Trigger created.

oracle@kaapi#exp scott/tiger@someDb tables=dept triggers=y                

Export: Release 10.2.0.1.0 - Production on Tue Aug 17 07:41:20 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                           DEPT          4 rows exported
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.



oracle@kaapi#sqlplus -s scott/tiger@someDb <<EOF
> truncate table dept;          
> truncate table ttest;
> exit;
> EOF

Table truncated.


Table truncated.



oracle@kaapi#imp scott/tiger@someDb tables=dept ignore=y 

Import: Release 10.2.0.1.0 - Production on Tue Aug 17 07:43:42 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing scott's objects into scott
. importing scott's objects into scott
. . importing table                         "DEPT"          4 rows imported
Import terminated successfully without warnings.
oracle@kaapi#


oracle@kaapi#sqlplus -s scott/tiger@someDb <<EOF
> select * from dept;                              
> select * from ttest;
> exit;
> EOF

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON


C1
------------------------------
ACCOUNTING
RESEARCH
SALES
OPERATIONS


Re: Triggers does not fired while doing import in oracle9i [message #471927 is a reply to message #471805] Tue, 17 August 2010 23:09 Go to previous messageGo to next message
kumasudh
Messages: 14
Registered: August 2010
Junior Member
Hi Mahesh/John,

Thanks a lot for the nice example. This works like a charm. Now coming back to my problem, I have two users both created as "identified externally". I am doing exp/imp on a table owned by SMS_60 user. The trigger defination for insert/update/delete operation on any row in this table will insert a row in a different table which is owned by the other user "SMSDBA" .So to make exp/imp work i had to change the password for SMS_60 user as SMS_60. After this passwd change, The manuall insert as SMS_60 user also doesnot trigger the row insertion in the other table. Does password change causes any permission change ? Can we run imp/exp for the users created as "identified externally" ?

Best Regards
Re: Triggers does not fired while doing import in oracle9i [message #471938 is a reply to message #471927] Wed, 18 August 2010 00:19 Go to previous message
kumasudh
Messages: 14
Registered: August 2010
Junior Member
Hello Guys,

I have just learnt about the sysdba role for exp/imp tools. with this role my problem is solved.

Thanks a ton for all the kind help.

Best Regards
Sudhir
Previous Topic: Import dump
Next Topic: SQLLDR WHEN condition with IN CLAUSE
Goto Forum:
  


Current Time: Wed Apr 16 20:59:53 CDT 2014

Total time taken to generate the page: 0.25617 seconds