Re: Triggers in other databases

In article <4c1f84$k8r@redstone.interpath.net>, Mike Lemon <mdl@interpath.com> says: > > >Have question about the execution of trigger procedures in databases other than the >current one. > >I have an application which posts from two separate purchasing databases into a single >inventory database. I have two triggers on the detail inventory table which simply >maintain header summaries when packages are deleted or inserted. > >The 4GL program updating inventory runs on each purchasing database, and executes a >statement such as: > > insert into inventory:inv_detail values ( r_detail.* )> >to insert the new records. > >Why then does it seem that the procedures executed by the inv_detail table triggers >are running against the purchasing database??!?? I even went into the procedures and >hardwired the database name on all my update SQL statements to go to the inventory >database, but it still gives me a -556 error. > >First question: Anyone else do this, and if so, how do you set the database context >for your procedures/triggers? > >Second: Why the heck is the cryptic -556 error returned? "Remote object"? If >anything, it should say 'Table does not exist', since the inv_detail doesn't exist in >the purchasing databases. I believe that the reason that you get the 'remote object' error is because a trigger cannot act on a table in a 'remote' database and because the executing process is connected to a different database than the one on which the trigger is acting it -thinks- that it is trying to work on a -remote- object. Try running your program slightly differently - connect to the database with the triggers in it and then read from the purchase db eg database inventory insert into inventory (cols) select cols from purchase:table I think this will work