Insert Trigger [message #317008] |
Tue, 29 April 2008 02:40  |
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 #317014 is a reply to message #317010] |
Tue, 29 April 2008 03:30   |
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   |
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
|
|
|
|
|