Home » SQL & PL/SQL » SQL & PL/SQL » Insert using DBLINK; Trigger not getting executed.
Insert using DBLINK; Trigger not getting executed. [message #228571] Tue, 03 April 2007 07:12 Go to next message
Shrinagu
Messages: 10
Registered: April 2007
Junior Member
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.

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

.....


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

Any help wud be highly appreciated.

Thanks,
Re: Insert using DBLINK; Trigger not getting executed. [message #228579 is a reply to message #228571] Tue, 03 April 2007 07:26 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
How do you come to know that the trigger is firing for the first record, when you insert the second record?

As the trigger is "after insert", it is firing after the first record insertion.

Can you please elaborate the problem?

By
Vamsi
Re: Insert using DBLINK; Trigger not getting executed. [message #228582 is a reply to message #228579] Tue, 03 April 2007 07:30 Go to previous messageGo to next message
Shrinagu
Messages: 10
Registered: April 2007
Junior Member
Hi,

In the trigger, i am calling some procedure, where in i am logging the values passed to that proc, there i can see that the values are not the new one but that of the previous insert from DB1.



Razz

Thanks,
Re: Insert using DBLINK; Trigger not getting executed. [message #228586 is a reply to message #228571] Tue, 03 April 2007 07:36 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Hmm sounds a bit strange to me

Why dont you post a sqlplus session so we can se exactly what you are doing.
Dont forget to include select statment's (before and after)the inserts so we can see what's happening to the table data.
Re: Insert using DBLINK; Trigger not getting executed. [message #228589 is a reply to message #228586] Tue, 03 April 2007 07:45 Go to previous messageGo to next message
Shrinagu
Messages: 10
Registered: April 2007
Junior Member
Hi,

Here is the snippet of Insert in a proc and the tigger on DB2..

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

.....


Thanks,
Re: Insert using DBLINK; Trigger not getting executed. [message #228591 is a reply to message #228589] Tue, 03 April 2007 07:48 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
This is already there in your first post.
Quote:
Why dont you post a sqlplus session so we can se exactly what you are doing.
Dont forget to include select statment's (before and after)the inserts so we can see what's happening to the table data.

By
Vamsi
Re: Insert using DBLINK; Trigger not getting executed. [message #228595 is a reply to message #228591] Tue, 03 April 2007 07:59 Go to previous messageGo to next message
Shrinagu
Messages: 10
Registered: April 2007
Junior Member
Here is the full code.

TRIGGER ON DB2

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


RDID WW_INTERFACE.INT_RD_ID%TYPE;
tagID WW_INTERFACE.INT_PC_IDS%TYPE;

pragma autonomous_transaction;

CURSOR T1Cursor IS
SELECT INT_RD_ID, INT_PC_IDS FROM WW_INTERFACE
where INT_CREATION_DATE = (select max(INT_CREATION_DATE) FROM WW_INTERFACE )
and ROWNUM <= 1;

BEGIN

OPEN T1Cursor;

LOOP
-- Retrieve each row of the result of the above query into PL/SQL variables:
FETCH T1Cursor INTO RDID, tagID;

EXIT WHEN T1Cursor%NOTFOUND;

dbms_output.put_line('RDID --: ' || RDID );
dbms_output.put_line('tagData --: ' || tagID );

tag_send( tagID, RDID, x_return_value, X_RETURN_MESSAGE );

END LOOP;


/* Free cursor used by the query. */
CLOSE T1Cursor;

END;
/


PROC on DB1



CREATE OR REPLACE PACKAGE BODY WW.WW_PKG_INTERFACE AS

PROCEDURE dataTransferToEBS IS

readEventID WW_GB_READ_EVENT.READEVENTID%TYPE;
RDID WW_GB_READ_EVENT.RDID%TYPE;
tagID WW_GB_READ_EVENT_DETAIL.TAGID%TYPE;
tagData varchar2(4000);
seqVal NUMBER;
tagDataOnly varchar2(4000) default null;


pragma autonomous_transaction;

/* Cursor declaration: */

CURSOR T1Cursor IS

SELECT eventTab.RDid, eventDetTab.tagid
FROM WW_GB_READ_EVENT eventTab, WW_GB_READ_EVENT_DETAIL eventDetTab
WHERE eventTab.READEVENTDATETIME = (SELECT MAX(READEVENTDATETIME) FROM WW_GB_READ_EVENT) and
eventTab.READEVENTID = eventDetTab.READEVENTID;


BEGIN

OPEN T1Cursor;

LOOP
-- Retrieve each row of the result of the above query into PL/SQL variables:
FETCH T1Cursor INTO RDID, tagID;

EXIT WHEN T1Cursor%NOTFOUND;

tagData := tagData || tagID || ',' ;
--dbms_output.put_line('Only tagData is --: ' || tagID);


END LOOP;

/**
* Tokenize the String and insert the data to EBS tables;
* from there it will be posted to RFID API..
*/
SELECT rtrim (tagData, ',') into tagDataOnly FROM dual;

--dbms_output.put_line('Only tagData is --: ' || RDID);

dbms_output.put_line(' RDID is --: ' || RDID);
dbms_output.put_line(' tagID is --: ' || tagData);

SELECT WW_INT_SEQ.nextval INTO seqVal FROM dual;

INSERT INTO APPS.WW_INTERFACE@WW_LINK_APPS_88 (INT_ID,INT_RD_ID,INT_PC_IDS,INT_CREATION_DATE)
VALUES (seqVal,RDID,(tagDataOnly),SYSDATE);
COMMIT;


/* Free cursor used by the query. */
CLOSE T1Cursor;

END;

END WW_PKG_INTERFACE ;
/
Re: Insert using DBLINK; Trigger not getting executed. [message #228610 is a reply to message #228595] Tue, 03 April 2007 08:18 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Have a read on
pragma autonomous_transaction


Also you call
 tag_send( tagID, RDID, x_return_value, X_RETURN_MESSAGE );


Cant see any code for that ?

Re: Insert using DBLINK; Trigger not getting executed. [message #228622 is a reply to message #228610] Tue, 03 April 2007 09:00 Go to previous messageGo to next message
Shrinagu
Messages: 10
Registered: April 2007
Junior Member
Hi tahpush,

Thanks for your quick reply.
i commented pragma autonomous_transaction and it worked.

I am not a DB developer and i made some copy paste for it. Smile

anyways, thanks again!!..

Re: Insert using DBLINK; Trigger not getting executed. [message #228688 is a reply to message #228571] Tue, 03 April 2007 13:57 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
I suspect that you never had a problem. On DB1 did you perform a commit? You will NOT see the new or updated row on DB2 until the process on DB1 had committed. Using an autonomous transaction is not a real fix. What happens if the initial insert on DB1 is rolled back or fails, the new row in DB2 will still be done. Except for logging files that track both success and failures, I have never seen a valid reason for a autonomous transaction. They tend to mess up referential integrity in your database.
Re: Insert using DBLINK; Trigger not getting executed. [message #228697 is a reply to message #228688] Tue, 03 April 2007 14:37 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
autonomous transactions are one of the most useful features introduced in 8i, but sure - you need to understand how they work (pretty basic behavior) before using them.

Try incrementing a sequence number in the trigger to really see if the trigger fires. Set the sequence CACHE setting to 1 or make sure you understand the CACHE setting first...
Previous Topic: SQL Division
Next Topic: Call a .net web service from pl/sql where the input parameter is a .net object
Goto Forum:
  


Current Time: Fri Dec 09 05:45:09 CST 2016

Total time taken to generate the page: 0.26563 seconds