The jist of the problem is this: When executing a DB update through ASP using a single recordset, executing two SQL statements serially, each with their own triggers, causes a run-time error.

Here's the situation... bear with me, it may seem like an SQL issue, but it's not... let me give you the background on the DB schema first (for the sake of sanity - mine and yours - I've made this schema up, but it should help get the point across)...

In a Database there is a parent table... call it "Products". Under this table there are a handful of subordinate tables, which have tertiary subordinate tables.

Parent Table: Products
Subordinate Table: ProductProperties
Tertiary Subordinate Table: PropertyValues

Each Product could have Properties that are associated with it, and those Properties could have different values for different products, hence the tertiary table PropertyValues.

Currently, if a Product is updated or deleted, triggers execute to update the appropriate subordinate tables (including the tertiary subordinate tables). So the trigger exists entirely on the Product table to do all of this. The purpose of these triggers are to create a new record of the Product and the associated records in the subordinate tables when a change occurs to version the information. This functions properly.

What makes more sense is to cascade the updates correct? If the Product is updated/deleted, it should fire a trigger to update/delete values in the ProductProperties table, which should fire a second trigger to do the same to it's subordinate table PropertyValues. So the process is: Update Parent Table > Fire Trigger to Update Secondary Table > Fire Subordinate's trigger to update the tertiary table. This would also support the ability to update the Subordinate table "ProductProperties" and it's subordinate "ProductValues" without having to touch the product.

This is where it falls apart. When the Parent table handles all updates to all tables through it's trigger, the world is a beautiful place (but this requires me to separately execute queries when I want to do updates to the ProductProperties and ProductValues tables, but do not include updates to the product). Now, if I distribute the triggers to cascade updates and deletes, I begin experiencing problems. It shows up when I need to update the ProductProperties independently of the update to the Product (in a series of ASP executions), the triggers seem to conflict. Example of Process Path with Distributed Triggers (Cascade):

'Start Processing ASP'

Form values are submit

Begin Transaction

These values require an update to the ProductProperties before we update the Product. This update triggers an update to the ProductValues

The Product is now updated which fires a trigger to duplicate the product's associated records in the subordinate table ProductProperties, which then fires another trigger to duplicate the products records in the tertiary table PropertyValues.

Close Transaction

'End Processing ASP'

As you can see, this process path fires three triggers. One trigger in the first execution, and two triggers in the second execution.

At the execution of that second statement, I am confronted with the following ASP error:

-------------------------------------
Microsoft OLE DB Provider for SQL Server error '80004005'

Cannot create new connection because in manual or distributed transaction mode.

/Products.asp, line 317
-------------------------------------

When the triggers are all housed within the Product table, this executes fine. But if I need to touch the secondary and tertiary tables separately of the Products table, it requires me to make additional SQL queries to clean up the subordinate and tertiary tables. When I attempt to do this through cascading triggers, it seems as though the recordset is still in use executing the first trigger when the second query attempts to execute, and it collides causing a run-time error.

I know this is rather lengthy.. and rather detailed... but it comes down to this.. A single recordset, executing two statements serially, each with their own triggers, causes a run-time error. Whereas, a single recordset, executing two statements, one without a trigger, and a second with triggers, executes fine (but with caviats).

This error can also be experienced by using a single recordset to execute a statement that fires triggers immediately followed by another statement that does or does not fire triggers.

Any ideas what the issue is here? Thanks!

I would replace a direct update of either Products or ProductProperties with a call to a stored procedure that would do that. In fact, this way you may get rid of triggers all together. If you still insist on using direct updates against your tables, - see if you can open the recordset differently by modifying the cursor side or cursor attributes like adOpenKeyset or adOpenStatic.

I'm not so worried about ME making direct updates, I'm thinking in the future, if I'm not here, how do I protect my app. I could use SP, and I have been for some things, this just seems like a natural fit for triggers. I did try fiddling with the server side cursors, but I still have the same issue. I didn't know if there was another property I should be setting on the recordset, that I haven't looked at. I think it comes down to how the recordset and triggers interact. When SQL executes, and fires a trigger, does the recordset simply return after the SQL executes leaving the server to process the triggers in the backend, or does the recordset wait for the triggers to finish before returning back to the ASP.

It depends on how you open the recordset, - sync or async.