Home » SQL & PL/SQL » SQL & PL/SQL » Insert Trigger (oracle 9i)
Insert Trigger [message #317008] Tue, 29 April 2008 02:40 Go to next message
sumanthapp
Messages: 2
Registered: April 2008
Location: pune
Junior Member
Hi ,

I have two tables say emp1 and emp2.I am inserting data in emp1 table and I have a insert/delete trigger on it
Through the insert trigger i am inserting data into emp2 table with sysdate and rowid of emp1 table
and through the delete triggeri am inserting data into temp table with sysdate and rowid.
when iam inserting 2 rows into emp1 table it has to insert 2rows rows in emp2 table.
But here my problem is it is inseting 3 rows into emp2 table ,but the extra third row is taking rowid of first row
and data is of 2nd row.

Please see the bleow example

Table 1:

Emp1

eno number
ename varchar2(100)

Table 2:

Emp2

eno number
ename varchar2(100)
sys_date timestamp
row_id varchar2(50)

Table 3:

Temp

eno number
ename varchar2(100)
sys_date timestamp
row_id varchar2(50)

Trigger

create or replace trigger emp_trig after insert or delete on emp1 foreachrow
declare
pragma atonomous_transaction;

begin
if inserting then
insert into emp2 values(:new.eno,:new.ename,sysdate,:new.rowid); commit;
elsif deleting then
insert into temp values(:old.eno,:old.ename,sysdate,:old.rowid); commit;
end if;
end;



EMP1 ENO ENAME EMP1_ROWID
101 ABC AAHPpyABOAAAO+PAAA

EMP1 ENO ENAME EMP1_ROWID
102 CDE AAHPpyABRAAAkDeAAu


EMP2 ENO ENAME SYS_DATE EMP1_ROWID
101 ABC 12-APR-08 08.36.54.000000 PM AAHPpyABOAAAO+PAAA

EMP2 ENO ENAME SYS_DATE EMP1_ROW_ID
102 CDE 13-APR-08 06.23.54.000000 AM AAHPpyABRAAAkDeAAu

EMP2 ENO ENAME SYS_DATE EMP1_ROW_ID
102 CDE 12-APR-08 04.50.35.000000 PM AAHPpyABOAAAO+PAAA DUPLICATE


Can any one solve the above problem. Any ones help is appreciable.

[Updated on: Tue, 29 April 2008 02:50]

Report message to a moderator

Re: Insert Trigger [message #317010 is a reply to message #317008] Tue, 29 April 2008 02:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ When you have complex thing to do use a procedure not a trigger
2/ Rowid may change
3/ Committing in a trigger means only one thing: you don't know what is a trigger.

In addition, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: Insert Trigger [message #317014 is a reply to message #317010] Tue, 29 April 2008 03:30 Go to previous messageGo to next message
sumanthapp
Messages: 2
Registered: April 2008
Location: pune
Junior Member
Thanks for the reply,

But here i should use trigger only.Why because i am inserting data in emp1 table through sql loader and that too it will be running continuously every day. And one more thing if iam not commiting in trigger it is not inserting in emp2 table.
Re: Insert Trigger [message #317018 is a reply to message #317014] Tue, 29 April 2008 03:44 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Have a look in your TEMP table - I reckon you've inserted a row, then deleted it, and when you re-insert the ROWID is being reused.

Michel is right - the fact that you are using an Autonomous transaction for this means that you have some grave misunderstandings about Triggers, or Autonomous Transactions, or more likely both.

I flatly do not believe you when you say that the data is not put into Table_2 without the Aut. Transaction. If you write that trigger without the A-T, insert a row into Table_1 and commit that insert, there will be a row in Table_2
Re: Insert Trigger [message #317020 is a reply to message #317014] Tue, 29 April 2008 03:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Insert using external table
2/ Your assertion that without commit it does not insert in the other table is wrong. You missed something else.
3/ Rowids may change DO NOT STORE THEM

Regards
Michel
Re: Insert Trigger [message #317325 is a reply to message #317008] Wed, 30 April 2008 08:44 Go to previous message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For information, same kind of question on AskTom:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:811951200346275991

Regards
Michel
Previous Topic: Help Needed
Next Topic: how to alter the blocksize value ?
Goto Forum:
  


Current Time: Fri Dec 09 04:24:30 CST 2016

Total time taken to generate the page: 0.07090 seconds