Re: SE Trigger/Exception problem

A couple of thoughts (that may or may not apply): 1. Can you share the code? Might make it easier to pinpoint the problem. 2. Have you included the statements "set debug file to ... " and "trace on" in the stored procedure, to see what happens when the trigger fires? 3. Are there any other actions to be taken as a result of this insert? If the logical unit of work does not extend beyond the insert and its validation (your trigger/stored procedure combo), what is the necessity of issuing an explicit transaction? 4. Are you using global values? If so, are they necessary? In this case, it sounds like the only data you should be feeding to the procedure is what is passed from the trigger. Assuming you are comparing the value passed by the trigger with some value you derive from the database, you shouldn't need your variables to be defined as global. 5. Is this strictly a business rule violation? Or possibly a data integrity issue? Meaning, is the data sound in and of itself? Should the record be rejected in its own right without the insert trigger being present (i.e., null values where data is required, lack of a corresponding foreign key, etc.)? -mka Michael Ackerbauer SP System Test Clifford Frisby <cjfrisby@scarpia.demon.co.uk> on 10/17/99 08:28:47 AM Please respond to Clifford Frisby <cjfrisby@scarpia.demon.co.uk> To: informix-list@iiug.org cc: Subject: SE Trigger/Exception problem 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. 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. 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. 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 Many thanks for any help offered. Cliff.