Re: [Fwd: Re: a trigger question]

rkusenet wrote: > "Jonathan Leffler" <jleffler@us.ibm.com> wrote: >>Testing on IDS 7.31.UD5 (I must upgrade, and fix my 9.30 system after a >>change of hard disks), I get the error I'd expect. > > Please elaborate. Was it an unlogged database? > > When I ran the script thru dbaccess, I too got the error message > "Number less than 100". But the row that generated that exception > still got inserted. > > The version is 9.21.UC4 on Solaris 2.6 Oh; I didn't do the SELECT after getting the error -- I'll check tomorrow. I'm not sure that I can think of a way to reconcile a successful insert with doubling operation and a failure to respond to an exception -- it makes me think of creepy-crawlies. Somewhere along the line, someone - possibly you, Ravi - suggested that this should be the expected behaviour. Does anybody have a reference to the manuals where such niceties are discussed (page number and edition would be helpful)? -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

"Jonathan Leffler" <jleffler@earthlink.net> wrote > I'm not sure that I can think of a way to reconcile a successful > insert with doubling operation and a failure to respond to an > exception -- it makes me think of creepy-crawlies. Somewhere along > the line, someone - possibly you, Ravi - suggested that this should be > the expected behaviour. Does anybody have a reference to the manuals > where such niceties are discussed (page number and edition would be > helpful)? I don't remember about manual, but I think the following is considered as expected behaviour in an unlogged database:- A triggered action on a table is independent of the table. For e.g. create trigger trg_table_ainsert on table_a referencing new as n for each row (update table_b where fld1 = n.fld1); If the update on table_b fails, it won't rollback the insert on table_a which invoked the trigger. I think a trigger action , is in effect , an independent DML statement, even if it is a re-entrant trigger working on the same table which invoked the trigger. Using the above reason, I concluded that in this thread that it is an expected behavior. Unlogged database makes serious compromise in data integrity. I don't know about other databases, but in informix, in an unlogged database, it is very easy to run into a situation when a NOT NULL column can have NULL values. Ravi