SQL: Does disabling triggers count as schema change? ("Could not complete cursor operation because the table schema changed")

Question

Does disabling triggers count as schema change in SQL Server? I am getting error: "Could not complete cursor operation because the table schema changed"

I have a stored procedure p_DeleteA that deletes a row from table A and all of its child records from table B; however, as a row in the table B gets deleted, grandchild records in table C, D, and E gets deleted as well. The above is implemented by cursor around table B where AId = xyz, then calls p_DeleteB, which deletes C, D, and E records, then B.

In the development database, the procedure works fine. However in the build environment the above mentioned error occurs. The only thing that I could image that count as "schema change" is the fact that triggers are being disabled to avoid stepping on to each other on table B. Does disabling triggers count as schema change? If not, what could cause the error message assuming that I am not changing schema in the middle. Autoshrink, which I read can cause this error, is turned off.

Edit: I am emulating cascade deletion, but I am manually deleting all records.

Development environment version: 9.00.4035.00
Build environment version: 9.00.1399.00

Answer 1

So... your cursor is expecting records to be there in other tables, but then it's finding they're not.

But I'm confused - do you have cascade deletes turned on, or are you deleting them yourself? You're talking about a cursor that calls p_DeleteB, which doesn't sound like cascade deletes.

In fact, it sounds like you don't have FKs implemented, which isn't good either.

If I were you, I'd look at doing this without a cursor at all - that may well solve your problem, as without a cursor, the reliance on schema shouldn't be there.

Answer 2

Thinking that I am hitting KB930775: FIX: Error message when you try to retrieve rows from a cursor that uses the OPTION (RECOMPILE) query hint in SQL Server 2005: "Could not complete cursor operation because the table schema changed after the cursor was declared or other known issue, I updated to SP3, but did not solve the problem.

Declaring the cursor to be local and static seemed to have done the trick:

DECLARE BCursor CURSOR LOCAL STATIC
FOR
    SELECT BId
    FROM B
    WHERE AId = @AId

See DECLARE CURSOR (Transact-SQL).

STATIC
Defines a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in tempdb; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications.

Answer 3

Yes. A trigger create/drop/alter is a schema change on the table and will trigger recompilation and invalidate dynamic cursors.

Answer 4

adding or deleteing triggers is a schema change. triggers are a world of pain because of these exact types of non-deterministic data driven side effects. always avoid triggers because they make your table dml non-orthogonal.