Re: update trigger

Hi Tom. Though I know you already know this, I thought I would post this here so it could help others. The solution to your problem is this. Create a trigger . . create procedure "informix".get_current_year_to_fraction() returning datetime year to fraction; define w_current datetime year to fraction; let w_current = current; return w_current; end procedure; 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, update_date on "informix".comment referencing new as new for each row (execute procedure get_current_year_to_fraction() INTO update_date); > On Tuesday, April 28, 2009 4:26 PM tomcam wrote: > 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 >> On Wednesday, April 29, 2009 3:05 PM Dirk Gunsth?vel wrote: >> 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) >>> On Wednesday, April 29, 2009 4:19 PM Ian Michael Gumby wrote: >>> --_bb896226-d6fe-4fd6-a111-79159e8c1e3f_ >>> Content-Type: text/plain; charset="iso-8859-1" >>> Content-Transfer-Encoding: quoted-printable >>> >>> >>> Hibernate does not scale. >>> Ok and by scale=2C I will admit that its a relative issue. For a lot of thing= >>> s=2C it scales well enough... >>> >>> >>> t=2Cupdate_version=2Ccreated_by=2Ccreate_date=2Cupdated_by >>> d >>>> On Thursday, April 30, 2009 4:52 AM Jonathan Leffler wrote: >>>> _version, >>>> te =3D >>>> >>>> Can you show an example update statement - or sequence of update >>>> statements - that triggers the problem...preferably on a reduced >>>> table. In other words, a complete working example of the code and the >>>> UPDATE that causes the trouble and an indication of what the error >>>> message is? >>>> >>>> This is my counter-example - tested IDS 11.50.FC3W2 on Solaris 10, using SQ= >>>> LCMD: >>>> >>>> Black JL: sqlcmd -d stores -e begin -xf trigger.sql -e rollback >>>> + CREATE TABLE trigger_happy >>>> ( >>>> main_data INTEGER NOT NULL PRIMARY KEY, >>>> update_tm DATETIME YEAR TO SECOND DEFAULT CURRENT YEAR TO SECOND NOT NU= >>>> LL >>>> ); >>>> + CREATE TRIGGER t_upd_happy UPDATE OF main_data ON trigger_happy >>>> REFERENCING NEW AS NEW >>>> FOR EACH ROW >>>> ( >>>> UPDATE trigger_happy SET update_tm =3D CURRENT YEAR TO SECOND >>>> WHERE main_data =3D new.main_data >>>> );>>>> + INSERT INTO trigger_happy(main_data) VALUES(0); >>>> + SELECT * FROM trigger_happy; >>>> 0|2009-04-30 08:50:50 >>>> + sleep 1; >>>> + UPDATE trigger_happy SET main_data =3D 1 WHERE main_data =3D 0; >>>> + SELECT * FROM trigger_happy; >>>> 1|2009-04-30 08:50:51 >>>> + DROP TABLE trigger_happy; >>>> + rollback >>>> Black JL: >>>> >>>> That looks correct to me. >>>> >>>> >>>> --=20 >>>> Jonathan Leffler #include <disclaimer.h> >>>> Email: jleffler@earthlink.net, jleffler@us.ibm.com >>>> Guardian of DBD::Informix v2008.0513 -- http://dbi.perl.org/ >>>> "Blessed are we who can laugh at ourselves, for we shall never cease >>>> to be amused." >>>> NB: Please do not use this email for correspondence. >>>> I do not necessarily read it every week, even. >>>> George Bernard Shaw - "We learn from experience that men never learn >>>> anything from experience." - >>>> http://www.brainyquote.com/quotes/authors/g/george_bernard_shaw.html >>>>> On Thursday, April 30, 2009 3:27 PM robsosno wrote: >>>>> tomcaml@gmail.com pisze: >>>>> I am using NHibernate (.NET prot of Hibernate). Feature named "dynamic >>>>> update" is just exactly what you need - only changed fields are updated. >>>>> I do not know if JPA also have this feature. >>>>> >>>>> Robert >>>>> Submitted via EggHeadCafe >>>>> ASP.NET GridView Persist User-Selected CheckBox Values >>>>> http://www.eggheadcafe.com/tutorials/aspnet/58db18a0-fcd6-43d2-954a-ae076427b739/aspnet-gridview-persist-userselected-checkbox-values.aspx