| Trigger Problem,Please help me out [message #259648] |
Thu, 16 August 2007 03:09  |
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 #259663 is a reply to message #259648] |
Thu, 16 August 2007 04:06  |
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.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
