1

Using Azure SQL Server, on a managed instance (i.e. a single server running multiple databases, where the db in question is one of them).

I've created a trigger on INSERT to table A, that performs an insert of the same data (with some massaging) to table B.

When checking the sys.triggers table, it seems that the trigger is there and is not disabled. When performing a single manual insert into the table for testing purposes, that trigger works. When looking at the audit logs for that database, that insert is indeed there (i.e. the insert created by the trigger).

When an application is doing the insert, however, the trigger does not fire. I.E. no rows are inserted the table B, and the audit logs are not showing inserts created by the trigger (i.e. the inserts to table B), but are showing the inserts to table A.

Not sure how to debug this further - suggestions for further explorations / solutions are welcome.

Improve this question
4
  • 1
    Is the application using Bulk insert by any chance? One of the options for doing bulk insert is to disable triggers. Does your trigger properly handle sets of data? Some applications do an insert with almost no data to get a primary key and then update it in the next statement. Does your trigger handle that scenario? Permissions?
    – Jonathan Fite
    Commented Oct 25, 2019 at 14:33
  • @JonathanFite Bulk Insert - Yes! How do I disable that option?
    – Tom Granot
    Commented Oct 25, 2019 at 15:10
  • depends on what you are using, but this stackoverflow.com/questions/12366510/… may help
    – Jonathan Fite
    Commented Oct 25, 2019 at 15:11
  • Awesome! Can you please add it as an answer so I can accept it later?
    – Tom Granot
    Commented Oct 25, 2019 at 15:56

2 Answers 2

Reset to default
1

One of the more likely reasons for this is (as you confirmed) Bulk Insert. By default, Bulk Insert does not fire triggers. You can change this behavior in the options for the bulk insert when your application/code is setting up the operation.

How to do that can vary by the specifics of your code, Powershell, .Net, etc. But this other answer may be able to help. https://stackoverflow.com/questions/12366510/trigger-not-firing-for-multirow-bulk-insert

Improve this answer
1
  • This has indeed solved the issue - I've manually instructed the server to honor triggers on bulk inserts and Voila - the trigger fires. Thank you!
    – Tom Granot
    Commented Oct 28, 2019 at 16:56
1

Yes! How do I disable that option?

It's controlled by permissions.

Requires INSERT and ADMINISTER BULK OPERATIONS permissions. In Azure SQL Database, INSERT and ADMINISTER DATABASE BULK OPERATIONS permissions are required. Additionally, ALTER TABLE permission is required if one or more of the following is true:

-Constraints exist and the CHECK_CONSTRAINTS option is not specified.

-Triggers exist and the FIRE_TRIGGER option is not specified.

-You use the KEEPIDENTITY option to import identity value from data file.

So revoke or deny ALTER TABLE permissions for the app to prevent bypassing triggers and constraints.

Improve this answer
1
  • But I need my app to alter tables on startup. In addition, this seems like a funny workaround, that the next guy who maintains my code might have a very hard time debugging.
    – Tom Granot
    Commented Oct 26, 2019 at 6:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.