Re: update trigger

On Tue, Apr 28, 2009 at 13:26, <tomcaml@gmail.com> wrote: > 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. 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 SQLCMD: 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 NULL ); + 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 = CURRENT YEAR TO SECOND WHERE main_data = 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 = 1 WHERE main_data = 0; + SELECT * FROM trigger_happy; 1|2009-04-30 08:50:51 + DROP TABLE trigger_happy; + rollback Black JL: That looks correct to me. -- 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 don't 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