Updating remote database from a trigger
Mark needed inserts into a view in one database to write to a table in another database on the same instance; his INSTEAD OF trigger/procedure doing a remote insert failed with error -556 (cannot modify an object external to the current database). ER was suggested but ruled out since his databases are unlogged; running sacego/sperform with -d only allows one database per run. Gary Gu's suggestion worked: put the table, the region-specific view, the INSTEAD OF trigger and its procedure in the remote database, then create a synonym in the local database pointing at that view, so inserts through the synonym fire the remote trigger.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Informix 11.50.fc6we
HP-UX 11.31
I need to be able to insert a row into a table in a remote database (on the
same instance) when a user attempts to insert into a view. The trigger is
attempting to insert into the table on which the view is based. The actual
table is:
create table "dbowner".comstock
(
region char(3),
col1 char(2),
col2 decimal(11,0),
col3 money(10,2),
col4 date
);
The view is:
create view "dbowner".borden as
select col1
, col2
, col3
, col4
from remote_database:comstock
where region = "01"
;
I have made the following INSTEAD OF trigger:
create trigger tr_borden_insertinstead of insert on borden
referencing new as n
for each row
( execute procedure tr_borden_insert);
Because I need to retrieve a value from a second table, I have to use a
trigger procedure, which looks like:
create procedure tr_borden_insert ()referencing new as n
for "dbowner".borden
define p_region char(3);
select region
into p_region
from config_info
where id = 1;
insert into remote_database:comstock
values ( p_region
, n.col1
, n.col2
, n.col3
, n.col4
)
;
end procedure;
When I attempt to do the insert on the view borden, I get -556 Cannot create,
drop, or modify an object that is external to current database.
Any way to do this with triggers and/or procedures? Other ideas?
Thanks in advance.
On 07/09/2011 01:12, MARK COLLINS wrote: > > Other ideas? ER? -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com I will now proceed to pleasure myself with this fish.
>> ER Ouch. We don't currently have any ER in place, hope we don't have to resort to that for this issue. More setup than we were wanting to have to do at this time. Thanks, though.
On 07/09/2011 01:20, MARK COLLINS wrote: >>> ER > > Ouch. We don't currently have any ER in place, hope we don't have to resort to > that for this issue. More setup than we were wanting to have to do at this > time. I don't really understand why you have to jump through all these hoops. Why are the databases in separate instances? Why are they even separate databases? Why are you trying to insert into a view? Why can the app not just do a remote insert? -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com I will now proceed to pleasure myself with this fish.
Well, you asked. The application is a collection of Ace reports and Perform screens, so changing connections between databases is not supported. The multiple databases is because there is a separate database for each region. The view is because we are attempting to consolidate one function into a single database, so we want data from all of the regional databases into a central database, but we want to keep the regional application unchanged, so we create a view that looks just like the original, regional version of the table. Thus, when we do an update/insert, we have to update the actual table.
That data centralization/distribution is EXACTLY what ER is designed to do for you simply and efficently with no code changes. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Sep 6, 2011 at 8:50 PM, MARK COLLINS <markc@myfastmail.com> wrote: > Well, you asked. > > The application is a collection of Ace reports and Perform screens, so > changing connections between databases is not supported. > > The multiple databases is because there is a separate database for each > region. > > The view is because we are attempting to consolidate one function into a > single database, so we want data from all of the regional databases into a > central database, but we want to keep the regional application unchanged, > so > we create a view that looks just like the original, regional version of the > table. Thus, when we do an update/insert, we have to update the actual > table. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --90e6ba6e844026d86204ac4f677b
On Tue, Sep 6, 2011 at 17:50, MARK COLLINS <markc@myfastmail.com> wrote: > The application is a collection of Ace reports and Perform screens, so > changing connections between databases is not supported. > sacego -d dbase reportname sperform -d dbase reportname This notation overrides the compiled in database name. -- Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." --000e0cd23e721045e304ac5171b4
On Tue, Sep 6, 2011 at 20:21, Jonathan Leffler <jonathan.leffler@gmail.com>wrote: > > On Tue, Sep 6, 2011 at 17:50, MARK COLLINS <markc@myfastmail.com> wrote: > >> The application is a collection of Ace reports and Perform screens, so >> changing connections between databases is not supported. >> > > sacego -d dbase reportname > > sperform -d dbase reportname > > Subject to you having a form reportname.frm (I meant to write formname). :-( > This notation overrides the compiled in database name. > -- Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." --90e6ba3fd2832a055504ac5174ca
May create views for each region in the remote db, and create a synonym (same name) for a view in other db, then should be able to do insert via the synonym in your function. Regards, Gary
>> That data centralization/distribution is EXACTLY what ER is >> designed to do for you simply and efficently with no code >> changes. Does ER require logged databases? Because I forgot to mention, ours are not logged.
>> sacego -d dbase reportname >> >> sperform -d dbase reportname >> >> This notation overrides the compiled in database name. That works, if you only need to do updates to one database. But we need to update table(s) in database_a and in database_b. If this were ESQL/C, we'd do it with two CONNECT TO statements and SET CONNNECTION TO whichever database we were trying to update. When I said that Ace and Perform wouldn't let us switch databases, I meant within the same execution.
Yes, ER requires a logged database. Honestly, I will NEVER understand why anyone would want a transactional database that is not logged. Data warehouse? Yes, I can see that often logging is unnecessary, though just as often useful (such as when multiple data marts share code/lookup tables that have to be kept in sync - ER, therefore logging). But a production level database that experiences constant updates? Logging is a requirement. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Sep 7, 2011 at 9:40 AM, MARK COLLINS <markc@myfastmail.com> wrote: > >> That data centralization/distribution is EXACTLY what ER is > >> designed to do for you simply and efficently with no code > >> changes. > > Does ER require logged databases? Because I forgot to mention, ours are not > logged. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --90e6ba21241dd7497a04ac5a2fac
>> But a production level database that experiences constant >> updates? Logging is a requirement. Agreed, and that is the direction we want to go, but that is not the case today. Thus, ER is not an option.
>> May create views for each region in the remote db, and create >> a synonym (same name) for a view in other db, then should be >> able to do insert via the synonym in your function. I had thought of that in passing, but assumed that the updates would fail because the target of the synonym is a remote table. But I just tried this approach and it works. It's clumsy, but it works. To summarize: Table in database_a; Region-specific view in database_a; INSTEAD OF trigger on region-specific view in database_a, updating the table in database_a; Trigger procedure (called by INSTEAD OF trigger) in database_a; Synonym in database_b, referring to region-specific view in database_a; With this in place, updates to the synonym in database_b do in fact try to update the view in database_a, which fires the INSTEAD OF trigger, and results in the update being done to the base table in database_a. Thanks, Gary.