sql-server - 在触发器中执行动态 sql "alter database"

Question

我想更改数据库以在触发器内使用动态 SQL 添加文件组，但出现以下错误:

ALTER DATABASE statement not allowed within multi-statement transaction.

我尝试了以下但它不起作用:

1. 在执行语句之前提交事务
2. 关闭和打开隐式事务
3. 从触发器中调用存储过程来执行语句

这是因为触发器在它自己的事务中执行。

我该如何解决这个问题？

Answer 1

考虑文档中的以下引述:

---

1. ALTER DATABASE (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.

---

1. 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.

---

1. Rollbacks and Commits in Stored Procedures and 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.

---

结论是，您不能在触发器内执行您想要的操作。