Home » SQL & PL/SQL » SQL & PL/SQL » Trigger Problem,Please help me out
Trigger Problem,Please help me out [message #259648] Thu, 16 August 2007 03:09 Go to next message
pandya
Messages: 7
Registered: August 2007
Junior Member
Hi,

I have two diffrent database schemas A and B.Bot having tables xx and yy.There is DBlink in A to B.I have created an after INSERT trigger in A on table yy such that it also inserts new records to table yy in B.Using the same trigger I wanted to insert records from A.XX to B.XX.So I have included an INSERT statement thet selects the value from A.XX and inserts them to
B.XX.The selecting criteria uses the :NEW primary key values of A.YY(As there is a relation between X and Y)

Now the problem is that when i manually insert the records(In order to TEST)in A.YY the trigger inserts in B.XX and B.YY.So it works fine.

But we have a batch jobrunning that feeds A.X and A.Y daily.But the trigger is not working with that automation.It feeds records on ly in B.YY not in B.XX.

Trigger-pseudo Code


AFTER INSERT ON A.YY
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW

BEGIN

INSERT INTO B.XX@LINK(SELECT * from A.XX using :NEW PRIMARY KEY A:YY = PRIMARY KEY OF A.XX)

INSERT INTO B.YY@LINK VALUES(all :NEW of A:YY)

END

Can anyone please help me with this ?


Re: Trigger Problem,Please help me out [message #259653 is a reply to message #259648] Thu, 16 August 2007 03:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
With what you said it is no possible.
But maybe there is a "when others then null" or the like somewhere...

Regards
Michel
Re: Trigger Problem,Please help me out [message #259663 is a reply to message #259648] Thu, 16 August 2007 04:06 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Your whole approach is questionable. You seem to be relying on the records in A.XX having been inserted before the records in A.YY. This is introducing a dependency on the order of the inserts, which is poor practice.

You would be better of putting a trigger on A.XX to replicate the changes into B.XX.
Re: Trigger Problem,Please help me out [message #259664 is a reply to message #259653] Thu, 16 August 2007 04:10 Go to previous messageGo to next message
pandya
Messages: 7
Registered: August 2007
Junior Member
Thanks,Guys for reply

Ya i have when others then NULL
Re: Trigger Problem,Please help me out [message #259665 is a reply to message #259663] Thu, 16 August 2007 04:12 Go to previous messageGo to next message
pandya
Messages: 7
Registered: August 2007
Junior Member
A.XX always gets the records before A.YY.I dont have previllages to create a trigger on A.XX
Re: Trigger Problem,Please help me out [message #259681 is a reply to message #259664] Thu, 16 August 2007 04:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Ya i have when others then NULL

So you hide the errors.
They happened but you said I don't want to see it.
This is one of the reasons "when others then null;" is BAD.

Regards
Michel
Re: Trigger Problem,Please help me out [message #260010 is a reply to message #259681] Fri, 17 August 2007 02:41 Go to previous messageGo to next message
pandya
Messages: 7
Registered: August 2007
Junior Member
I also tried catching the exception.But i am not getting any error.
Re: Trigger Problem,Please help me out [message #260014 is a reply to message #260010] Fri, 17 August 2007 02:47 Go to previous message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't try to catch them, let them raise then you know the reason of your issue.

Regards
Michel
Previous Topic: Changing columns
Next Topic: UTL_FILE problem
Goto Forum:
  


Current Time: Mon Dec 05 09:14:50 CST 2016

Total time taken to generate the page: 0.12425 seconds