Dear Tom,

See updated scenario.

I have a very strange scenario. I have one table which gets populated by SQL Loader on contineous basis. This table doesn't have any unique column and hence which data came on what date is very difficult to find out. This table is populating thoughout the month and I have to capture daily inserted data for that only. This table structure can not be changed because this is third party application table. Hence I have created hourly basis partition table which will hold similar record of original record along with original rowid. To populate this partition table I have created simple row level trigger on original table which inserts the record along with rowid. Upon investigation it is found that, partition table is having duplicate rowid of original table but corrosponding records in partition table are different. See following senario.

I have two tables say emp1 and emp2. I am inserting data in emp1 table and I have a insert trigger on it
Through the insert trigger i am inserting data into emp2 table with sysdate and rowid of emp1 table.
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:

create table Emp1
(eno number
ename varchar2(100))

Table 2:


create table Emp2
(eno number
ename varchar2(100)
sys_date timestamp
row_id varchar2(50))
NOLOGGING
PARTITION BY RANGE (SYS_DATE)
(PARTITION P_20080402_00 VALUES LESS THAN (TIMESTAMP'2008-04-01 01:00:00')),
(PARTITION P_20080402_00 VALUES LESS THAN (TIMESTAMP'2008-04-01 02:00:00')),
(PARTITION P_20080402_00 VALUES LESS THAN (TIMESTAMP'2008-04-01 03:00:00')),
(PARTITION P_20080402_00 VALUES LESS THAN (TIMESTAMP'2008-04-01 04:00:00')),
(PARTITION P_20080402_00 VALUES LESS THAN (TIMESTAMP'2008-04-01 05:00:00')),
.
.
.
(PARTITION P_20080402_00 VALUES LESS THAN (TIMESTAMP'2008-04-01 23:00:00')),
(PARTITION P_20080402_00 VALUES LESS THAN (TIMESTAMP'2008-04-02 00:00:00'))


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

begin
insert into emp2 values(:new.eno,:new.ename,sysdate,:new.rowid);
commit;
end;


Two records inserted in EMP1
ENO ENAME EMP1_ROWID
101 ABC AAHPpyABOAAAO+PAAA
102 CDE AAHPpyABRAAAkDeAAu


Records in EMP2 table inserted due to trigger
ENO ENAME SYS_DATE EMP1_ROWID
101 ABC 12-APR-08 08.36.54.000000 PM AAHPpyABOAAAO+PAAA
102 CDE 13-APR-08 06.23.54.000000 AM AAHPpyABRAAAkDeAAu
102 CDE 12-APR-08 04.50.35.000000 PM AAHPpyABOAAAO+PAAA DUPLICATE




Regards

Manish Bhavsar