---------- Original Message ----------- From: K Z <[email protected]> > How can i force a failure of a write operation from inside a TRIGGER? > For example, a TRIGGER for a INSERT INTO operation. Inside the TRIGGER > i will have a IF condition and if necessary, i want to cause a failure, > canceling the INSERT INTO operation itself. Is this possible? ------- End of Original Message -------
create exception e_stuff_happened 'Default message if not specified below'; create trigger bob ... begin if (...) then exception e_stuff_happened 'Optional message overrides the default chosen above'; end If any trigger raises an exception, the entire statement that caused the trigger to fire will be canceled. Your transaction is not immediately rolled back, so you have the opportunity in your client code to catch the exception and retry, within the same transaction context, if appropriate. Both BEFORE and AFTER triggers can throw exceptions, with the same effect. The choice does determine whether the data you see in the trigger is truly final or not (if you have several BEFORE triggers that modify data on the way into the table, you may want to wait until AFTER, to make sure you're throwing the exception for the right reasons.) The triggers are row-at-a-time, so in a bulk-load operation, make sure your trigger won't react badly because rows were inserted into the table in a different order than the one you expected. Also note that if your IF depends on complex conditions across rows or across tables (debits and credits must sum to 0.00 / all invoices must have at least one line-item), you may need to defer your 'constraint' to transaction-level triggers (such as right before a commit), where you could check several tables, several rows, etc. I've posted examples to this list before, you can find that in the archives. I also have a particular example at: http://pseudotheos.com/view_object.php? object_id=1552 -Philip
