Triggers

Hello all, I have an Informix database with a Primary, HDR secondary and a RSS. I have written a trigger and procedure to update a column in the table when a row is inserted in the table, here is the code: CREATE PROCEDURE OpenRecInsertProc(openrecnum int) DEFINE host VARCHAR(255); SELECT tty INTO host FROM SysMaster:SysSessions WHERE sid = DBINFO("sessionid"); update open_rec set insert_datetime_stamp = current, tty = host where open_rec_num = openrecnum; // open_rec_num is a serial column END PROCEDURE; CREATE TRIGGER open_rec_insert INSERT ON open_rec REFERENCING new AS pre_ins FOR EACH row ( EXECUTE PROCEDURE OpenRecInsertProc(pre_ins.open_rec_num) ); The problem is when I insert into the primary system the time stamp and the tty columns are updated with the correct information, but when I insert into the secondary or the RSS the fields are not updated. Any help would be appreciated. Thanks, ~Gene

Gene What you are describing here is not desirable and should not be possible. The whole point of an HDR Secondary and an RSS is that they are exact replicas of your HDR Primary (live) database and are only updated by the internal mechanisms written for that purpose. As soon as you amend the data in them independently (which IDS will not allow anyway) they will diverge and become useless for their purpose of being a fail-over in the event the live server fails in some way. Can you re-check what is really happening, are you updating the Secondary and RSS or are you actually getting a failure that is not being trapped. Keith On 24 October 2013 17:24, Eugene Turner <eturner@allamericanasphalt.com>wrote: > Hello all, > > I have an Informix database with a Primary, HDR secondary and a RSS. I have > written a trigger and procedure to update a column in the table when a row > is > inserted in the table, here is the code: > > CREATE PROCEDURE OpenRecInsertProc(openrecnum int)> > DEFINE host VARCHAR(255); > > SELECT tty> > INTO host > > FROM SysMaster:SysSessions > > WHERE sid = DBINFO("sessionid"); > > update open_rec > > set insert_datetime_stamp = current, tty = host > > where open_rec_num = openrecnum; // open_rec_num is a serial column > > END PROCEDURE; > > CREATE TRIGGER open_rec_insert INSERT ON open_rec> > REFERENCING new AS pre_ins > FOR EACH row > ( > > EXECUTE PROCEDURE OpenRecInsertProc(pre_ins.open_rec_num) > );> > The problem is when I insert into the primary system the time stamp and the > tty columns are updated with the correct information, but when I insert > into > the secondary or the RSS the fields are not updated. Any help would be > appreciated. > > Thanks, > ~Gene > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --bcaec51d255230405604e98f56bb

I would have approached this by setting the date time to a default of = current and letting the engine handle it. j. On Oct 24, 2013, at 12:24 PM, "Eugene Turner" = <eturner@allamericanasphalt.com> wrote: > Hello all,=20 >=20 > I have an Informix database with a Primary, HDR secondary and a RSS. I = have=20 > written a trigger and procedure to update a column in the table when a = row is=20 > inserted in the table, here is the code:=20 >=20 > CREATE PROCEDURE OpenRecInsertProc(openrecnum int)=20>=20 > DEFINE host VARCHAR(255);=20 >=20 > SELECT tty=20 >=20 > INTO host=20 >=20 > FROM SysMaster:SysSessions=20 >=20 > WHERE sid =3D DBINFO("sessionid");=20 >=20 > update open_rec=20 >=20 > set insert_datetime_stamp =3D current, tty =3D host=20 >=20 > where open_rec_num =3D openrecnum; // open_rec_num is a serial column=20= >=20 > END PROCEDURE;=20 >=20 > CREATE TRIGGER open_rec_insert INSERT ON open_rec=20>=20 > REFERENCING new AS pre_ins=20 > FOR EACH row=20 > (=20 >=20 > EXECUTE PROCEDURE OpenRecInsertProc(pre_ins.open_rec_num)=20 > );=20>=20 > The problem is when I insert into the primary system the time stamp = and the=20 > tty columns are updated with the correct information, but when I = insert into=20 > the secondary or the RSS the fields are not updated. Any help would be=20= > appreciated.=20 >=20 > Thanks,=20 > ~Gene=20 >=20 >=20 > = **************************************************************************= *****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20 >=20

You are corer I restructured the trigger and all is working well. Thank you for your input. Sent from my iPhone > On Oct 25, 2013, at 4:49, "Keith Simmons" <smiley73@gmail.com> wrote: > > Gene > > What you are describing here is not desirable and should not be possible. > The whole point of an HDR Secondary and an RSS is that they are exact > replicas > of your HDR Primary (live) database and are only updated by the internal > mechanisms > written for that purpose. As soon as you amend the data in them > independently (which > IDS will not allow anyway) they will diverge and become useless for their > purpose of > being a fail-over in the event the live server fails in some way. > Can you re-check what is really happening, are you updating the Secondary > and RSS > or are you actually getting a failure that is not being trapped. > > Keith > > On 24 October 2013 17:24, Eugene Turner <eturner@allamericanasphalt.com>wrote: > >> Hello all, >> >> I have an Informix database with a Primary, HDR secondary and a RSS. I have >> written a trigger and procedure to update a column in the table when a row >> is >> inserted in the table, here is the code: >> >> CREATE PROCEDURE OpenRecInsertProc(openrecnum int)>> >> DEFINE host VARCHAR(255); >> >> SELECT tty>> >> INTO host >> >> FROM SysMaster:SysSessions >> >> WHERE sid = DBINFO("sessionid"); >> >> update open_rec >> >> set insert_datetime_stamp = current, tty = host >> >> where open_rec_num = openrecnum; // open_rec_num is a serial column >> >> END PROCEDURE; >> >> CREATE TRIGGER open_rec_insert INSERT ON open_rec>> >> REFERENCING new AS pre_ins >> FOR EACH row >> ( >> >> EXECUTE PROCEDURE OpenRecInsertProc(pre_ins.open_rec_num) >> );>> >> The problem is when I insert into the primary system the time stamp and the >> tty columns are updated with the correct information, but when I insert >> into >> the secondary or the RSS the fields are not updated. Any help would be >> appreciated. >> >> Thanks, >> ~Gene > ******************************************************************************* >> Forum Note: Use "Reply" to post a response in the discussion forum. > > --bcaec51d255230405604e98f56bb > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Can that be done within the trigger code it self? Or should I assign the viable in the procedure and return the value? Sent from my iPhone > On Oct 25, 2013, at 4:54, "Jack Parker" <jack.parker4@verizon.net> wrote: > > I would have approached this by setting the date time to a default of = > current and letting the engine handle it. > > j. > On Oct 24, 2013, at 12:24 PM, "Eugene Turner" = > <eturner@allamericanasphalt.com> wrote: > >> Hello all,=20 >> =20 >> I have an Informix database with a Primary, HDR secondary and a RSS. I = > have=20 >> written a trigger and procedure to update a column in the table when a = > row is=20 >> inserted in the table, here is the code:=20 >> =20 >> CREATE PROCEDURE OpenRecInsertProc(openrecnum int)=20>> =20 >> DEFINE host VARCHAR(255);=20 >> =20 >> SELECT tty=20 >> =20 >> INTO host=20 >> =20 >> FROM SysMaster:SysSessions=20 >> =20 >> WHERE sid =3D DBINFO("sessionid");=20 >> =20 >> update open_rec=20 >> =20 >> set insert_datetime_stamp =3D current, tty =3D host=20 >> =20 >> where open_rec_num =3D openrecnum; // open_rec_num is a serial column=20= > >> =20 >> END PROCEDURE;=20 >> =20 >> CREATE TRIGGER open_rec_insert INSERT ON open_rec=20>> =20 >> REFERENCING new AS pre_ins=20 >> FOR EACH row=20 >> (=20 >> =20 >> EXECUTE PROCEDURE OpenRecInsertProc(pre_ins.open_rec_num)=20 >> );=20>> =20 >> The problem is when I insert into the primary system the time stamp = > and the=20 >> tty columns are updated with the correct information, but when I = > insert into=20 >> the secondary or the RSS the fields are not updated. Any help would be=20= > >> appreciated.=20 >> =20 >> Thanks,=20 >> ~Gene=20 >> =20 >> =20 >> = > **************************************************************************= > *****=20 >> Forum Note: Use "Reply" to post a response in the discussion forum.=20 >> =20 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >