Insert using DBLink; Trigger is not executing

Hi All,

I have a procedure [on DB-1], where in i am using DBLink to insert the data to a table on DB-2. and i have trigger on the same table of DB2.

Inserting the data from DB-1 to TABLE.DB-2 is working fine, but the trigger on TABLE.DB-2 is not getting executed immediately. The trigger is firing only when the 2nd record is inserted.

Even though the records exist, trigger is not happening.

Ex:
insert into DB-2.TABLE values ('A',1); -- On this insert trigger shul work.
insert into DB-2.TABLE values ('B',2); -- On this insert trigger shul work.

But the first trigger [A,1] is happening when B&2 are there, also B&2 will trigger on insert of C&3.

Has anyone faced the same issue, what is the fix for this.

Any help wud be highly appreciated.

Thanks,

Triggers fire once per transaction.
Behavior is consistent with having both insert statements in a single transaction.

BTW... posting the code of your trigger and your Oracle version wouldn't hurt

Hello,

Thanks for your quick reply.

In the procedure [DBA] i have a insert statement and after that; i have a commit

INSERT INTO APPS.RSC_INTERFACE@RSC_LINK_APPS_88 (INT_ID,INT_READER_ID,INT_EPC_IDS,INT_CREATION_DATE)
VALUES (seqVal,readerID,(tagDataOnly),SYSDATE);
COMMIT;
and in trigger is like

CREATE OR REPLACE TRIGGER APPS.RSC_TRG_INTERFACE_TRANSACTION
AFTER INSERT
ON APPS.RSC_INTERFACE for each row --REFERENCING NEW AS NEW OLD AS OLD
DECLARE

.....

Its not working, Only if the 2nd record is inserted, trigger on 1st rec is executed..

Can you help me on this.

Thanks,

It looks like trigger is fired late, isn't it?

When 2nd row inserted... first row trigger is fired
When 3rd row inserted... second row trigger is fired
etc.

In a distributed transaction applies the concept of commit-point-site. Commit-point-site is the place where the query is actually running and commit is processed there first, then distributed to the other nodes (in this case the place where you want to insert the data).

I've noticed your query is pushing the data over the DBLink.

I'm wondering if there is any chance of pulling the data over the DBLink meaning, running your process on the target database and reading data from the source one. This way the Target database will be your commit-point-site, commit will be executed there first and (hopefully) trigger will be fired at the time you expect it to be fired.

A slight amendment to the previous comment.
A DML trigger fires either once per row or once per statement. This one is defined as FOR EACH ROW in this case.
However the effect of the trigger will generally only become apparent (to any other session) after the commit. As indicated, it is probably due to the way the commit is propogated that the trigger does not APPEAR to have fired.
If you put something not transactional in the trigger, you could verify this. For example a call to dbms_application_info.set_client_info should become visible in the client_info column of v$session (for the session on the remote db) immediately the trigger fires, without depending on the commit.

Sharinagu,

What's your oracle release?