update trigger
hello all we have a trigger on an update_date field - pretty simple: create trigger "informix".t_upd_comment update of id,from_class_id,from_id,type_cid,status_cid,seq_num,comment,update_version,created_by,create_date,updated_by on "informix".comment referencing new as new for each row ( update "informix".comment set "informix".comment.update_date = CURRENT year to fraction(3) where (id = new.id ) ); the issue is developers using a JPA tool called Hibernate, which automatically includes every field in their update sql generated by the tool - apparently they have no control over it and cannot exclude this one update_date field, so the update trigger i have on the field gets fired off and stuck in a loop when their sql does the same? they are asking if their is a way to tweak out trigger so it is like an after update trigger, that their sql would update that field, and our trigger would just "re-update" the same field afterwards and not got in the updating loop. thanks in advance tom
Hi :-) Oh, my.... you find yourself in the nowadays not so uncommon 'why care about databases when I can donload Hibernate' trap :-( And yes, one CAN control what Hibernate (imho Hibernate is a wonderful library when you use it right) does when it writes data - but not everybody does. If you ask me you have 3 possible options now (I dont count options which are likely to lead to law/job consequences here): 1. Convince your boss that letting ppl access the database who cant control the behavior of their programs is not a good idea. or: 2. Convince the Hibernate guys to learn how to change the behavior of their programs. or: 3. Do something ugly to make the Hibernate stuff work. The last time I decided that there was no other way than to use option 3 (about 2 years ago) I created a (nearly) identity view including all columns of the table but not the dangerous column and told the Hibernate guys to update the new 'table' (the concept of a view was too much for them to understand anyways). I dont know if this would still work with Hibernate but maybe its worth trying.... Regards, Dirk -- -- -- Dipl.-Math. Dirk Gunsth'vel -- -professional services- -- -- Dirk Gunsth'vel IT Systemanalyse - GunCon -- Hammer Str. 13 -- D-48153 Muenster -- phone: +49 (0) 251 28446- 0 -- fax: +49 (0) 251 28446-55 -- web: http://www.GunCon.de -- email: info@GunCon.de -- UStId: DE 189527667 -- -- 'One now understands why some animals eat their young.' -- (Andrew in 'Bicentennial Man' 1999) <tomcaml@gmail.com> schrieb im Newsbeitrag news:c9269409-1ec5-4f17-a0bd-75dbf110ef3a@j9g2000prh.googlegroups.com... > hello all > > we have a trigger on an update_date field - pretty simple: > > create trigger "informix".t_upd_comment update of > id,from_class_id,from_id,type_cid,status_cid,seq_num,comment,update_version,created_by,create_date,updated_by > on "informix".comment referencing new as new for each row > ( > update "informix".comment set "informix".comment.update_date = > CURRENT year to fraction(3) where (id = new.id ) ); > > the issue is developers using a JPA tool called Hibernate, which > automatically includes every field in their update sql generated by > the tool - apparently they have no control over it and cannot exclude > this one update_date field, so the update trigger i have on the field > gets fired off and stuck in a loop when their sql does the same? > they are asking if their is a way to tweak out trigger so it is like > an after update trigger, that their sql would update that field, and > our trigger would just "re-update" the same field afterwards and not > got in the updating loop. > > thanks in advance > tom
Hibernate doesn't scale. Ok and by scale, I'll admit that its a relative issue. For a lot of things, it scales well enough... > From: dirk@guncon.de > Subject: Re: update trigger > Date: Wed, 29 Apr 2009 21:05:29 +0200 > To: informix-list@iiug.org > > Hi :-) > > Oh, my.... you find yourself in the nowadays not so > uncommon 'why care about databases when I can > donload Hibernate' trap :-( > > And yes, one CAN control what Hibernate (imho > Hibernate is a wonderful library when you use it right) > does when it writes data - but not everybody does. > > If you ask me you have 3 possible options now (I > dont count options which are likely to lead to > law/job consequences here): > > 1. Convince your boss that letting ppl access the > database who cant control the behavior of their > programs is not a good idea. > > or: > 2. Convince the Hibernate guys to learn how to > change the behavior of their programs. > > or: > 3. Do something ugly to make the Hibernate stuff > work. > > The last time I decided that there was no other way > than to use option 3 (about 2 years ago) I created > a (nearly) identity view including all columns of the > table but not the dangerous column and told the > Hibernate guys to update the new 'table' (the concept > of a view was too much for them to understand > anyways). > > I dont know if this would still work with Hibernate > but maybe its worth trying.... > > Regards, > Dirk > > -- > -- > -- Dipl.-Math. Dirk Gunsthövel > -- -professional services- > -- > -- Dirk Gunsthövel IT Systemanalyse - GunCon > -- Hammer Str. 13 > -- D-48153 Muenster > -- phone: +49 (0) 251 28446- 0 > -- fax: +49 (0) 251 28446-55 > -- web: http://www.GunCon.de > -- email: info@GunCon.de > -- UStId: DE 189527667 > -- > -- 'One now understands why some animals eat their young.' > -- (Andrew in 'Bicentennial Man' 1999) > > > <tomcaml@gmail.com> schrieb im Newsbeitrag > news:c9269409-1ec5-4f17-a0bd-75dbf110ef3a@j9g2000prh.googlegroups.com... > > hello all > > > > we have a trigger on an update_date field - pretty simple: > > > > create trigger "informix".t_upd_comment update of > > id,from_class_id,from_id,type_cid,status_cid,seq_num,comment,update_version,created_by,create_date,updated_by > > on "informix".comment referencing new as new for each row > > ( > > update "informix".comment set "informix".comment.update_date = > > CURRENT year to fraction(3) where (id = new.id ) ); > > > > the issue is developers using a JPA tool called Hibernate, which > > automatically includes every field in their update sql generated by > > the tool - apparently they have no control over it and cannot exclude > > this one update_date field, so the update trigger i have on the field > > gets fired off and stuck in a loop when their sql does the same? > > they are asking if their is a way to tweak out trigger so it is like > > an after update trigger, that their sql would update that field, and > > our trigger would just "re-update" the same field afterwards and not > > got in the updating loop. > > > > thanks in advance > > tom > > _________________________________________________________________ Rediscover Hotmail®: Get e-mail storage that grows with you. http://windowslive.com/RediscoverHotmail?ocid=TXT_TAGLM_WL_HM_Rediscover_Storage2_042009