Skip to main content
Database Administrators

Return to Answer

added 277 characters in body
Source Link
J.D.
J.D.
  • 39.5k
  • 12
  • 60
  • 134

All Triggers fire within the scope of the same Transaction that the INSERT statement that generated them runs in. Therefore if the Transaction of the INSERT statement you executed ran to completion, then so did the After Insert Trigger that fires from that INSERT statement. Here's some straight to the point information and tests that prove this out:

So in short a trigger executes in the context of the calling transaction and a rollback in a trigger will rollback the calling transaction.

So my guess is either your original INSERT is still executing (which can then be aborted and rolled back) or the issue is somewhere between after your Trigger runs and the mechanism you're using to dump the data into RabbitMQ.

You can use sp_WhoIsActive to determine if your INSERT statement is still running and to get the SPID of the process so you can abort it and rollback. To abort you'd have to run KILL 123 (where 123 = the SPID of your INSERT).

Side note, if by 50,000 inserts, you mean 50,000 records in one INSERT statement, then that's small and should be performant. If you actually mean 50,000 separate INSERT statements then that's a different story that can take much longer to complete the INSERT.

All Triggers fire within the scope of the same Transaction that the INSERT statement that generated them runs in. Therefore if the Transaction of the INSERT statement you executed ran to completion, then so did the After Insert Trigger that fires from that INSERT statement. Here's some straight to the point information and tests that prove this out:

So in short a trigger executes in the context of the calling transaction and a rollback in a trigger will rollback the calling transaction.

So my guess is either your original INSERT is still executing (which can then be aborted and rolled back) or the issue is somewhere between after your Trigger runs and the mechanism you're using to dump the data into RabbitMQ.

Side note, if by 50,000 inserts, you mean 50,000 records in one INSERT statement, then that's small and should be performant. If you actually mean 50,000 separate INSERT statements then that's a different story that can take much longer to complete the INSERT.

All Triggers fire within the scope of the same Transaction that the INSERT statement that generated them runs in. Therefore if the Transaction of the INSERT statement you executed ran to completion, then so did the After Insert Trigger that fires from that INSERT statement. Here's some straight to the point information and tests that prove this out:

So in short a trigger executes in the context of the calling transaction and a rollback in a trigger will rollback the calling transaction.

So my guess is either your original INSERT is still executing (which can then be aborted and rolled back) or the issue is somewhere between after your Trigger runs and the mechanism you're using to dump the data into RabbitMQ.

You can use sp_WhoIsActive to determine if your INSERT statement is still running and to get the SPID of the process so you can abort it and rollback. To abort you'd have to run KILL 123 (where 123 = the SPID of your INSERT).

Side note, if by 50,000 inserts, you mean 50,000 records in one INSERT statement, then that's small and should be performant. If you actually mean 50,000 separate INSERT statements then that's a different story that can take much longer to complete the INSERT.

Source Link
J.D.
J.D.
  • 39.5k
  • 12
  • 60
  • 134

All Triggers fire within the scope of the same Transaction that the INSERT statement that generated them runs in. Therefore if the Transaction of the INSERT statement you executed ran to completion, then so did the After Insert Trigger that fires from that INSERT statement. Here's some straight to the point information and tests that prove this out:

So in short a trigger executes in the context of the calling transaction and a rollback in a trigger will rollback the calling transaction.

So my guess is either your original INSERT is still executing (which can then be aborted and rolled back) or the issue is somewhere between after your Trigger runs and the mechanism you're using to dump the data into RabbitMQ.

Side note, if by 50,000 inserts, you mean 50,000 records in one INSERT statement, then that's small and should be performant. If you actually mean 50,000 separate INSERT statements then that's a different story that can take much longer to complete the INSERT.