Need help on a trigger or how to overlay a table field

Hi Gurus, i'm still waiting full of hope, would nobody give me a hit ? a have to change a key-number of our db and only a few days time, the problem: From a given date on, all use of a number, say oldnr is forbidden and a new number, say newnr has to use. The oldnr is part of a table X with other (unique) fields. Many data in the running system has containing the oldnr in it and is using it in further executions. What i tried to do: Create a trigger on table X, and for _every select_ on it change oldnr via a translationtable to newnr. E.G.: select a, b, c, oldnr from X .... ---> trigger fire and so the select comes up with newnr in oldnr Who can i do that, trigger seems to work on inset/update/delete only? Thanks a lot Peter

well, you have a sticky problem. I'd try this: optionally rename original table to something new or secret define one view selecting the original rows where allowable (pre special date or whatever) define another view selecting the rows to be mapped and include the lookup table and use it in the view. define a final view which is a union of the two other views. This view may have the name of the orignal table. Because the final view is a union, you will not be able to insert or update with it - at least, this was true last time I checked Informix. It is theoretically possible to insert into many complex views, but a bitch to implement according to articles I've read, and I wouldn't be surprised if Informix never chews on this feature - although I'd like to see that. Now - why or why not to rename the original table to a secret name? If it's easy to modify the insert/update code, then you can make it insert/update to the new table name and therefore you can leave all other code performing selects alone. If it's not easy to modify all the insert/update code, then this solution is probably quite second rate. Finally, the performance when using views and unions may OR MAY NOT be shocking, so you would have to test the performance and see if it's still the same, slower but acceptable or totally hopeless. After that, I canna see a solution. Peter wrote in message <3a17b574.1001172@news.btx.dtag.de>... >Hi Gurus, i'm still waiting full of hope, would nobody give me a hit ? > >a have to change a key-number of our db and only a few days time, the >problem: >From a given date on, all use of a number, say oldnr is forbidden and >a new number, say newnr has to use. The oldnr is part of a table X >with other (unique) fields. Many data in the running system has >containing the oldnr in it and is using it in further executions. >What i tried to do: >Create a trigger on table X, and for _every select_ on it change oldnr >via a translationtable to newnr. >E.G.: select a, b, c, oldnr from X .... > ---> trigger fire and so the select comes up with newnr in oldnr >Who can i do that, trigger seems to work on inset/update/delete only? >Thanks a lot >Peter

>define one view selecting the original rows where allowable (pre special >date or whatever) > >define another view selecting the rows to be mapped and include the lookup >table and use it in the view. > >define a final view which is a union of the two other views. This view may >have the name of the orignal table. > >Because the final view is a union, you will not be able to insert or update >with it - at least, this was true last time I checked Informix. It is >theoretically possible to insert into many complex views, but a bitch to >implement according to articles I've read, and I wouldn't be surprised if >Informix never chews on this feature - although I'd like to see that. > >Now - why or why not to rename the original table to a secret name? > >If it's easy to modify the insert/update code, then you can make it >insert/update to the new table name and therefore you can leave all other >code performing selects alone. > >If it's not easy to modify all the insert/update code, then this solution is >probably quite second rate. > >Finally, the performance when using views and unions may OR MAY NOT be >shocking, so you would have to test the performance and see if it's still >the same, slower but acceptable or totally hopeless. > >After that, I canna see a solution. > >Peter wrote in message <3a17b574.1001172@news.btx.dtag.de>... >>Hi Gurus, i'm still waiting full of hope, would nobody give me a hit ? >> >>a have to change a key-number of our db and only a few days time, the >>problem: >>From a given date on, all use of a number, say oldnr is forbidden and >>a new number, say newnr has to use. The oldnr is part of a table X >>with other (unique) fields. Many data in the running system has >>containing the oldnr in it and is using it in further executions. >>What i tried to do: >>Create a trigger on table X, and for _every select_ on it change oldnr >>via a translationtable to newnr. >>E.G.: select a, b, c, oldnr from X .... >> ---> trigger fire and so the select comes up with newnr in oldnr >>Who can i do that, trigger seems to work on inset/update/delete only? Hi, thanks for the response! I tried this before posting here, but the problem is the update in the original table which will not work in that way .-((. The App is not changeable in using the db-tables. Why was there all the time no need of a select-trigger??? What will be the problem of such one? Cheers Peter