CODEWITHSUNDEEP
oracle-databasestored-proceduresdatabase-trigger
Akshay
Akshay

Reputation: 348

Oracle Triggers not inserting latest records into tables

I have TABLE_1 that is parent table of these records which is in SCHEMA_1 and populating from JAVA/Node.js application on transaction. Also have below trigger (SCHEMA_1.TRIGGER_CALL_SP_OF_SCHEMA_2) on same SCHEMA_1 and calling another Stored procedure for populate to another TABLE_2 which is in SCHEMA_2 on the basis of Table_1 transactions.

Trigger: 

create or replace trigger SCHEMA_1.TRIGGER_CALL_SP_OF_SCHEMA_2
  AFTER UPDATE OR INSERT OR DELETE ON SCHEMA_1.TABLE_1
  REFERENCING NEW AS NEW OLD AS OLD
  FOR EACH ROW
  DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    SCHEMA_2.SP_OF_SCHEMA_2(:NEW.RECORD_ID);
    commit;
  EXCEPTION   WHEN OTHERS THEN
    RAISE;
END;

Stored Procedure: 

create or replace PROCEDURE SCHEMA_2.SP_OF_SCHEMA_2(P_RECORD_ID NUMBER ) AS
BEGIN
    DELETE FROM SCHEMA_2.TABLE_2 WHERE RECORD_ID = P_RECORD_ID;           
    FOR rec IN (SELECT RECORD_ID, COL2, COL3, COL4  from SCHEMA_1.TABLE_1 where RECORD_ID = P_RECORD_ID)
    LOOP
         INSERT INTO SCHEMA_2.TABLE_2 (RECORD_ID, COL2, COL3, COL4) VALUES(rec.RECORD_ID, rec.COL2, rec.COL3,rec.COL4 );
   END LOOP;
 COMMIT;
END;

So the problem is in Table_2 which don't have always latest records. Eg: When first record_Id inserted into Table_1 but in Table_2 there is no any entry. When second time with same records_id inserted with another values in Table_1 but in Table_2 only last record_id with details inserted.(again not latest entry). Below are records sample: (Note - No any PK available in both the tables.)

Table_1

+------------+-------+----------+---------+
| RECORD_ID  |  COL2 |   COL3   |  COL4   |
+------------+-------+----------+---------+
|          1 |   101 | abc      |  insert |
+------------+-------+----------+---------+

Table_2

+------------+-------+----------+------+
| RECORD_ID  |  COL2 |   COL3   | COL4 |
+------------+-------+----------+------+

Table_1

+------------+-------+----------+---------+
| RECORD_ID  |  COL2 |   COL3   |  COL4   |
+------------+-------+----------+---------+
|          1 |   101 | abc      |  insert |
|          1 |   102 | xyz      |  insert |
+------------+-------+----------+---------+

Table_2

+------------+-------+----------+---------+
| RECORD_ID  |  COL2 |   COL3   |  COL4   |
+------------+-------+----------+---------+
|          1 |   101 | abc      |  insert |
+------------+-------+----------+---------+

Is this b'cze of any database commit sequence or any transaction related issues or any other possible scenario? How can achieve that to keep Table_2 update always. Thanks in Advance.

Upvotes: 0

Views: 73

Answers (1)

Chris Saxon
Chris Saxon

Reputation: 9765

The trigger has an autonomous transaction.

So the insert/update/delete that fires the trigger is in a separate transaction to the call of the procedure in the trigger.

Transactions can only view committed changes from others.

So the transaction in the trigger can't see the row you've inserted!

create table t1 (
  c1 int
);
create table t2 (
  c1 int
);

create or replace trigger trig
after insert on t1
for each row
declare
  pragma autonomous_transaction;
begin
  insert into t2
    select * from t1
    where  c1 = :new.c1;

  dbms_output.put_line ( 'Rows added: ' || sql%rowcount );
  commit;
end;
/

insert into t1 values ( 1 );

Rows added: 0
commit;
insert into t1 values ( 1 );

Rows added: 1

select * from t2;

        C1
----------
         1

As referring to table_1 without the autonomous transaction will raise a mutating trigger error (ORA-04091), you'll need to rethink this process.

Upvotes: 0

Related Questions