Cause an insert or update to fail based on code in a trigger in SA12

[Former Member]

Based on code in a trigger I want to stop the insert or update that triggered it to fail. Since this is an atomic statement, can this be done at the database level?

[VolkerBarth · Accepted solution]

Just call ROLLBACK TRIGGER within the trigger under the according condition.

To cite from the docs:

The ROLLBACK TRIGGER statement rolls back the work done in a trigger, including the data manipulation that caused the trigger to fire.

Optionally, a RAISERROR statement can executed. If a RAISERROR statement is executed, an error is returned to the application. If no RAISERROR statement is executed, no error is returned.

If a ROLLBACK TRIGGER statement is used within a nested trigger and without a RAISERROR statement, only the innermost trigger and the statement which caused it to fire are undone.

[Breck_Carter · Accepted solution]

...and here's an example: http://sqlanywhere.blogspot.ca/2009/02/one-row-only.html