Instead of Triggers SQL 2000 And ADO.NET Transactions

  • Thread starter Thread starter Chris Alm
  • Start date Start date
C

Chris Alm

I have a database with some tables that form a cyclical relationship. In
order to implement the equivalent of a delete cascade contsraint for this
situation in SQL Server 2000, I am required to do it with an INSTEAD OF
trigger at the database level (Sql Server will not allow you to enable a
cascade constraint on cyclic relationships).

The problem I am having is this...

I perform an update in code using DataAdapters that are all part of the same
transaction. If that transaction in code gets rolled back, the DML changes
that were made by the INSTEAD OF trigger are left in the database and NOT
rolled back.

How can I accomplish my objective of enabling a cascade like effect in the
database for these cyclic tables while also being able to ROLLBACK the
changes with my transaction in code?


Thanks for any help
Chris
 
Back
Top