database trigger vs bacpac

Question

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

Answer 1

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.

Answer 2

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.

Answer 3

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

Answer 4

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

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

Answer 5

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.