I'm coding an application that does an INSERT into a database that triggers a delete on another record. Currently, the application has no way of letting the user know whether or not the triggered delete worked, only whether the INSERT failed. The coworker who will be coding the trigger said that he would set the trigger to send a SQLSTATE message indicating whether or not the triggered delete (after the successful insert) worked. My question is how do I capture this SQLSTATE message so that I can send it back to the user as an error/notification message? Will the SQLSTATE trigger an exception, or will I have to use some part of the JDBC API to capture this message.
Hi Gabriel, I don't know what database you are working with (I didn't see any mention of it in your post), but with the Oracle 8i database that I work with, database triggers _always_ (as far as I know) execute in the same transaction context as their triggering event. In other words, you cannot have a situation where the DELETE operation worked, but the INSERT operation failed. If the INSERT succeeded, then the DELETE also succeeded (and vice-versa). However, a DELETE operation may not delete any rows (since there were no rows matching the DELETE criteria). In this case -- for all the databases I have worked with -- this is _not_ considered an error. [That's why the "executeUpdate()" method -- in the "java.sql.Statement" class -- returns an "int" indicating how many rows were affected.] So if you want to know how many rows the DELETE operation actually deleted, then I don't think that this information is available via SQLSTATE, anyway. So unless I've totally misunderstood your question, I think you're barking up the wrong tree, and you need to rethink this issue/requirement/problem. Hope this helps. Good Luck, Avi.