Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

We recently made a change to create constraints based on logic previously used in stored procedures, and part of that included the use of INSTEAD OF triggers to centralize logic.

The logic is generally simplistic:

  1. The sproc contains an INSERT (or UPDATE) statement against a view, which has INSTEAD OF INSERT/UPDATE triggers
  2. The trigger contains the actual logic -- usually two statements, dealing with INSERT/UPDATE of a record into a main table and one (or more) supporting tables (incl. referential integrity).

The problem is when something like a CHECK constraint is hit within the statement in the trigger. The transaction gets dumped, and everything is rolled back. I'm looking at having to duplicate the CHECK/etc constraint in the offending sprocs to validate prior to the trigger executing -- is there any alternative?

share|improve this question
can you give an example of the logic that's happening? – DForck42 Mar 16 '12 at 16:26
@DForck42: Updated, currently we've dumped the INSTEAD OF triggers & moved the logic back into the sprocs with a check for an active TRANSACTION. – OMG Ponies Mar 17 '12 at 2:08
Triggers implicitly have XACT_ABORT on which means that all errors will abort the tran. Without testing not sure if you can just turn this off. – Martin Smith Mar 18 '12 at 10:02
up vote 3 down vote accepted

Probably not. Violating a declarative constraint will always raise an error and prevent the transaction from committing writes that would violate the constraints. The point of database constraints - checks, foreign keys, uniques etc. - is that they prevent invalid data from being recorded in the database by aborting writes that will violate the constraints. The constraint violation raises an error and rolls back the transaction.

If you want the transaction to fail gracefully you will either have to deal with the exception or pre-validate the data before attempting to write it. In the latter case you will have to replace the validations in the stored procedures or implement validation in the application.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.