Re: Updating tables when a view is updated by a dblink

On Feb 21, 1:21 pm, mcamp..._at_yahoo.es wrote:
> Hi there!
> I don`t know if the title of this message is clearly enough... i will
> try to explain my problem...
>
> I have a dblink to another database. Im my database I have a view
> (lets call it LocalView) of a table (lets call it RemoteTable) of the
> remote database.
>
> Then, i also have a table (LocalTable) in my database.
>
> What i want is to update LocalTable when a new row is inserted in
> RemoteTable. I tried with a INSTEAD-OF trigger in LocalView, but this
> only work when I insert a row directly in LocalView.
>
> Does anybody have a solution to this?
>
> Thank you very much in advance!!!
>
> P.D: There's no way i can modifiy the remote database.

You add an after insert trigger on the remote table to send an insert to your local table version each time it (the remote table) is inserted into successfully. This would require that both databases and the network link between them be available for the remote table to be updated. An alternate method is to use a trigger to populate a holding table and have a process schedulted that ships the updates (inserts in this case) accross. During a down window or other failure the data would be held. Oracle Replication can be used to capture and ship the data.

HTH -- Mark D Powell -- Received on Wed Feb 21 2007 - 12:32:27 CST