Re: SE Trigger/Exception problem

Clifford Frisby wrote: > > Here's hoping the answer to this isn't too obvious! > > I have an insert trigger on a table which is designed to reject the row > being inserted if certain consistency rules are violated. Nothing > unusual there, I hope. The mechanism used is that the trigger calls a > stored procedure 'for each row', and the stored procedure raises a -746 > exception if the consistency checks fail. OK, sane approach. > This always seemed to work fine. Attempting to insert a row which I know > should fail results in the exception being reported to DB-Access, and > the table in question does not then contain the offered row. As expected. > However, I've now discovered that if I try the same insert within a > transaction (i.e. after a 'begin work' -- it's a non-ANSI database) > then, although I still see the exception report, the offered row *does* > find its way into the table. I can 'commit work' without any problem > too. AHH, here is the fallacy, without an explicit transaction the engine creates an implicit singleton transaction for the simple INSERT in the example above which is automatically rolled back when the trigger returns the -746 error. However, if you BEGIN an explicit transaction then it is YOUR responsibility to either COMMIT or ROLLBACK the error. One might mistakenly infer from the fact that if an INSERT fails on a built-in error code the row is not INSERTed EVEN if you COMMIT. Keep in mind that this is not because the engine protected you from the error or rolled it back for you but because all of those built-in error codes that an INSERT might return indicate that the engine was not ABLE to insert the row in the first place. In the case of your trigger it is POSSIBLE to insert the row, and indeed the row is successfully inserted BEFORE the trigger is even fired (to see the proof try inserting a row with you consistency errors AND a duplicate key and see which error is returned). > Clearly, I don't expect a user to be able to circumvent my business > rules in this way. Therefore, I suspect that my means of implementation > is incorrect in some way. But my implementation is similar to the > upd_items_p2 example on page 13-15 of Informix Guide to SQL - Tutorial > (April 1996 Part Number 000-7883A), where, although not explicitly > stated, the intuitive implication seems to be that the operation would > be inhibited. > > This is: > Informix SE 7.24.UC5 > Red Hat Linux 6.0 Most assuredly there should be a method to completely reject an operation from within a trigger. Currently there is no such method beyond making sure all applications check errors and rollback when appropriate. Art S. Kagel

"Art S. Kagel" wrote: > <words of experience, summing up with:> > > Most assuredly there should be a method to completely reject an operation > from within a trigger. Currently there is no such method beyond making > sure all applications check errors and rollback when appropriate. > > Art S. Kagel Thanks for your advice, Art. I kind of suspected that this would be the situation, but was desperately hoping otherwise. (You see, I really _want_ to like Informix, not least because I've paid the $99 for IDS Linux Edition!) Anyway, I'm now trying to argue with myself that it's okay really, because the inconsistency I'm checking for is just one of a whole myriad of potential input errors, most of which I have no means of checking for. But it's not easy! Cliff.