I want to alter a database to add a filegroup using dynamic SQL inside a trigger, but the following error occurs:
我想更改一个数据库，以便在触发器中使用动态SQL添加文件组，但出现以下错误:
ALTER DATABASE statement not allowed within multi-statement transaction.
在多语句事务中不允许修改数据库语句。
I tried the following but it doesn't work:
我试过以下方法，但没有效果:
- Commit transactions before executing the statement
- 在执行语句之前提交事务
- Set implicit transactions off and on
- 设置隐式事务
- Call stored procedure from the trigger to execute the statement
- 从触发器调用存储过程来执行语句
This because the trigger executes in its own transaction.
这是因为触发器在它自己的事务中执行。
How can I solve this problem?
我如何解决这个问题?
1 个解决方案
#1
4
Consider following quotes from documentation:
考虑以下来自文件的引用:
- ALTER DATABASE (Transact-SQL)
- 改变数据库(transact - sql)
The ALTER DATABASE statement must run in autocommit mode (the default transaction management mode) and is not allowed in an explicit or implicit transaction.
ALTER DATABASE语句必须以自动提交模式(默认事务管理模式)运行，在显式或隐式事务中不允许这样做。
- Autocommit Transactions
- 自动提交事务
A connection to an instance of the Database Engine operates in autocommit mode until a BEGIN TRANSACTION statement starts an explicit transaction, or implicit transaction is set on. When the explicit transaction is committed or rolled back, or when implicit transaction mode is turned off, the connection returns to autocommit mode.
与数据库引擎实例的连接在自动提交模式中运行，直到启动事务语句开始显式事务，或隐式事务被设置。当显式事务被提交或回滚，或隐式事务模式被关闭时，连接返回到自动提交模式。
- Rollbacks and Commits in Stored Procedures and Triggers, on Triggers:
- 在存储过程和触发器中对触发器执行回滚和提交:
A trigger operates as if there were an outstanding transaction in effect when the trigger is executed. This is true whether the statement firing the trigger is in an implicit or explicit transaction.
触发器的操作就好像在执行触发器时存在一个有效的事务一样。无论触发触发器的语句是隐式事务还是显式事务，都是如此。
When a statement begins executing in autocommit mode, there is an implied BEGIN TRANSACTION to allow the recovery of all modifications generated by the statement if it encounters an error. This implied transaction has no effect on the other statements in the batch because it is either committed or rolled back when the statement completes. This implied transaction is still in effect, however, when a trigger is called.
当语句开始以自动提交模式执行时，会有一个隐含的BEGIN事务，允许在语句遇到错误时恢复该语句生成的所有修改。这个隐含的事务对批处理中的其他语句没有影响，因为当语句完成时，它要么被提交，要么被回滚。然而，当调用触发器时，这个隐含的事务仍然有效。
The conclusion is, you can't do what you want from inside a trigger.
结论是，你不能在触发器内部做你想做的事。
#1
4
Consider following quotes from documentation:
考虑以下来自文件的引用:
- ALTER DATABASE (Transact-SQL)
- 改变数据库(transact - sql)
The ALTER DATABASE statement must run in autocommit mode (the default transaction management mode) and is not allowed in an explicit or implicit transaction.
ALTER DATABASE语句必须以自动提交模式(默认事务管理模式)运行，在显式或隐式事务中不允许这样做。
- Autocommit Transactions
- 自动提交事务
A connection to an instance of the Database Engine operates in autocommit mode until a BEGIN TRANSACTION statement starts an explicit transaction, or implicit transaction is set on. When the explicit transaction is committed or rolled back, or when implicit transaction mode is turned off, the connection returns to autocommit mode.
与数据库引擎实例的连接在自动提交模式中运行，直到启动事务语句开始显式事务，或隐式事务被设置。当显式事务被提交或回滚，或隐式事务模式被关闭时，连接返回到自动提交模式。
- Rollbacks and Commits in Stored Procedures and Triggers, on Triggers:
- 在存储过程和触发器中对触发器执行回滚和提交:
A trigger operates as if there were an outstanding transaction in effect when the trigger is executed. This is true whether the statement firing the trigger is in an implicit or explicit transaction.
触发器的操作就好像在执行触发器时存在一个有效的事务一样。无论触发触发器的语句是隐式事务还是显式事务，都是如此。
When a statement begins executing in autocommit mode, there is an implied BEGIN TRANSACTION to allow the recovery of all modifications generated by the statement if it encounters an error. This implied transaction has no effect on the other statements in the batch because it is either committed or rolled back when the statement completes. This implied transaction is still in effect, however, when a trigger is called.
当语句开始以自动提交模式执行时，会有一个隐含的BEGIN事务，允许在语句遇到错误时恢复该语句生成的所有修改。这个隐含的事务对批处理中的其他语句没有影响，因为当语句完成时，它要么被提交，要么被回滚。然而，当调用触发器时，这个隐含的事务仍然有效。
The conclusion is, you can't do what you want from inside a trigger.
结论是，你不能在触发器内部做你想做的事。