SQL: are disconnect triggers disabled when a schema changes? ("Failed to complete cursor operation because table schema changed")

Question

Are disabled triggers enabled as a schema change in SQL Server? I get an error: "Failed to complete cursor operation because table schema changed"

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

In the development database, the procedure works fine. However, in the build environment, the above error occurs. The only thing I could imagine, considering the “circuit change”, is the fact that the triggers are disabled so as not to go to each other in table B. Is the disconnecting trigger considered a circuit change? If not, you may receive an error message suggesting that I am not changing the circuit in the middle. The autoshrink that I read may cause this error to be disabled.

Change . I emulate cascading deletion, but manually delete all entries.

Version for the development environment: 9.00.4035.00
Version of the development environment: 9.00.1399.00

Answer 1

. create/drop/alter - .

Answer 2

, KB930775: FIX: , OPTION (RECOMPILE) SQL Server 2005: " , , SP3, .

, , :

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

. DECLARE CURSOR (Transact-SQL).

, , . tempdb; , , , , , , .

Answer 3

- . - - , . , dml .

Answer 4

... , , , .

But I'm confused - do you have cascading deletes enabled, or do you delete them yourself? You are talking about a cursor that calls p_DeleteB, which is not like cascading deletes.

Actually, it sounds like you haven't implemented FK, which is also bad.

If I were you, I would look at it without a cursor - this may very well solve your problem, since without a cursor you cannot rely on a circuit.