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:
- The stored procedure contains an INSERT (or UPDATE) statement against a view, which has INSTEAD OF INSERT/UPDATE triggers
- 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 stored procedures to validate prior to the trigger executing -- is there any alternative?