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

On Feb 21, 10:21 am, 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.

If it doesn't need to be instantaneous, maybe you can periodically use the minus to see what is different, something like this:

insert into LocalTable (select field1,field2... from LocalView minus select field1,field2... from LocalTable);

That would likely have to move a lot of data over the link, it would be much better if you could do just what you need remotely or if there is some qualifier by time (like ora_rowscn http://download-west.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_flashback.htm#i1018117 ). Hmmmm, maybe you can keep the last updated remote scn and get all remote rows with a higher one... reinvent replication with a suck. That might be more generally useful, though it might get hinky with rows that are being updated nulling ora_rowscn or something.

jg

--
@home.com is bogus.
Iceberg ahead, rearrange deck chairs.
http://www.signonsandiego.com/uniontrib/20070221/news_1b21ebay.html