---------- Original Message -----------
From: K Z <peixinhosdalua@...>

> 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