RE: Query

We use this: CREATE TRIGGER ts_inv_billacts UPDATE ON "dba".inv_billacts REFERENCING NEW AS post FOR EACH ROW WHEN (post.timestamp < CURRENT year to second) OR (post.timestamp IS NULL) (UPDATE "dba".inv_billacts SET "dba".inv_billacts.timestamp =CURRENT year to second WHERE (billactsid = post.billactsid); There are probably cleverer ways, but this works fine with little impact. It does require a unique identifier on the table. DC > -----Original Message----- > From: informix-list-bounces@iiug.org [mailto:informix-list- > bounces@iiug.org] On Behalf Of bozon > Sent: Wednesday, August 02, 2006 12:58 PM > To: informix-list@iiug.org > Subject: Re: Query > > You don't happen to know a better way to get that timestamp for the > update trigger. It seems like a hassle to create a procedure that just > returns current and then calling it from the trigger. > > Art S. Kagel wrote: > > Superboer wrote: > > >>We are migrating our database, and there is a change in schema. > > >>So the new table me have a column which was not there earlier. > > >>After the migration will be done we bring the system live. Now if > > >>anything goes wrong then we have to take the system to the previous > > >>position with NEW DATA WHICH WAS INSERTED OR UPDATED AFTER THE > > >>MIGRATION. > > > > > > > > > in other words the only thing you do not need is the added > > > columns???!!! > > > right.?? > > > > > > in that case alter table ... drop .... > > > > No, Chandan wants to know: "If the new DB has some problems after > > implementation and we decide to fall back to the original DB, how can I > > recover new rows and updates to existing rows from the new DB so I can > > reapply them to the original without having to reload the entire large > data > > set?" > > > > Bozon nailed it. A timestamp column with a DEFAULT CURRENT and UPDATE > > trigger to restamp updated rows. This cannot trap deletes, but they are > > easier to find then updates (obviously no harder than finding new rows > > though). To locate deletes directly Chandan will need an audit table to > > record the deleted key via a delete trigger. > > > > Art S. Kagel > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list

Thanks, Doug I wonder if you can use "where current of" or similar syntax. (You know: update <yadda-yadda> set <whatever> where current of;) Does the update statement in your trigger actually execute to find the record or is Informix smart enough to use the current record without that. If Informix isn't smart enough then you should probably use my trigger syntax and procedure because I think it is optimized to update fields in the current record. It just seems dumb to me to have to create your own "current" procedure. I know you aren't paying much penalty because the page is in the cache. I just noticed something else you should exclude the timestamp field by listing the other fields that can be updated except for that field you should then be able to get rid of the "when" clause. Does it really execute it twice for each update, once to update and once to figure out it has already been updated? I know it is a pain when a field is added to the table, but It does save a test and the fire of the trigger twice (I believe). create trigger ts_inv_billacts update of <field1>, <field2>, <fieldn3>,.. <fieldn> on inv_billacts referencing new as post for each row( execute procedure ecurrent() into timestamp ) ; Thanks, I only have 2 triggers on 1 table in my database so I don't get to work with them that much so I am not sure about the performance implications. Doug Conrey wrote: > We use this: > > CREATE TRIGGER ts_inv_billacts > UPDATE ON "dba".inv_billacts > REFERENCING NEW AS post > FOR EACH ROW > WHEN (post.timestamp < CURRENT year to second) OR (post.timestamp IS > NULL) > (UPDATE "dba".inv_billacts SET "dba".inv_billacts.timestamp => CURRENT year to second WHERE (billactsid = post.billactsid); > > There are probably cleverer ways, but this works fine with little > impact. It does require a unique identifier on the table. > > DC > > > -----Original Message----- > > From: informix-list-bounces@iiug.org [mailto:informix-list- > > bounces@iiug.org] On Behalf Of bozon > > Sent: Wednesday, August 02, 2006 12:58 PM > > To: informix-list@iiug.org > > Subject: Re: Query > > > > You don't happen to know a better way to get that timestamp for the > > update trigger. It seems like a hassle to create a procedure that just > > returns current and then calling it from the trigger. > > > > Art S. Kagel wrote: > > > Superboer wrote: > > > >>We are migrating our database, and there is a change in schema. > > > >>So the new table me have a column which was not there earlier. > > > >>After the migration will be done we bring the system live. Now if > > > >>anything goes wrong then we have to take the system to the > previous > > > >>position with NEW DATA WHICH WAS INSERTED OR UPDATED AFTER THE > > > >>MIGRATION. > > > > > > > > > > > > in other words the only thing you do not need is the added > > > > columns???!!! > > > > right.?? > > > > > > > > in that case alter table ... drop .... > > > > > > No, Chandan wants to know: "If the new DB has some problems after > > > implementation and we decide to fall back to the original DB, how > can I > > > recover new rows and updates to existing rows from the new DB so I > can > > > reapply them to the original without having to reload the entire > large > > data > > > set?" > > > > > > Bozon nailed it. A timestamp column with a DEFAULT CURRENT and > UPDATE > > > trigger to restamp updated rows. This cannot trap deletes, but they > are > > > easier to find then updates (obviously no harder than finding new > rows > > > though). To locate deletes directly Chandan will need an audit > table to > > > record the deleted key via a delete trigger. > > > > > > Art S. Kagel > > > > _______________________________________________ > > Informix-list mailing list > > Informix-list@iiug.org > > http://www.iiug.org/mailman/listinfo/informix-list