Share via

database trigger vs bacpac

ikslabuk 21 Reputation points
2022-09-21T10:13:11.073+00:00

I have a database with a trigger that does not allow schema changes in a certain situation, the solution works without any problem. The problem arose when I wanted to do an export/import to a bacpac file. It seems that first the database structures are created together with the trigger, which is set as enabled starts checking the changes.
Then there is an attempt to create an index on one of the tables which fails with a trigger. I currently disable this trigger before running the export, but this is a very unfriendly solution. is there any idea how to get around this

Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,361 questions
0 comments
Accepted answer
  1. Erland Sommarskog 107.1K Reputation points
    2022-09-21T21:24:49.773+00:00

    A variation of Tom's idea, is that the trigger checks when it was created, and gives a blind eye to all schema changes that occur in the first ten minutes (or whatever time you expect that it takes to run the bacpac) after creation of the trigger.

    0 comments

5 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Tom Phillips 17,721 Reputation points
    2022-09-21T14:04:02.923+00:00

    Database triggers are created after the database is created. So the best thing to do is to add an exception to your trigger to allow for deployment. You could do this based on the user logged in, like only run the trigger if the user is not "sa" or something.

    0 comments
  2. Alberto Morillo 33,426 Reputation points MVP
    2022-09-21T14:36:12.22+00:00

    Try to use sqlpackage utility with the /p:ExcludeObjectTypes option. For example:

    sqlpackage /Action:Publish /p:ExcludeObjectTypes="DatabaseTriggers"

    0 comments
  3. ikslabuk 21 Reputation points
    2022-09-21T15:29:25.27+00:00

    I'm afraid you have to use the export not publish process and unfortunately it doesn't have an exclude parameter.

    https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-export?view=sql-server-ver16

    0 comments
  4. Seeya Xi-MSFT 16,461 Reputation points
    2022-09-22T07:27:02.997+00:00

    Hi @ikslabuk ,

    To add, when you import, you can use this parameter(IGNORE_TRIGGERS) to import.
    Por more information, please refer to this: https://learn.microsoft.com/en-us/sql/relational-databases/import-export/bulk-import-and-export-of-data-sql-server?view=sql-server-ver16

    Best regards,
    Seeya

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.