Search code examples
sql-serverdatabasedynamictriggersalter

Execute dynamic sql "alter database" inside trigger

I want to alter a database to add a filegroup using dynamic SQL inside a trigger, but the following error occurs:

ALTER DATABASE statement not allowed within multi-statement transaction.

I tried the following but it doesn't work:

  1. Commit transactions before executing the statement
  2. Set implicit transactions off and on
  3. 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?

Solution

  • Consider following quotes from documentation:

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

    The conclusion is, you can't do what you want from inside a trigger.