CODEWITHSUNDEEP
sql-servertriggers
usha
usha

Reputation: 29359

SQL Server after create trigger

I have an after create trigger that reads the data from the row inserted, parse it and add it into another table.

But when this trigger fails, my whole insert fails. My question is

  1. Why is after create trigger considered as part of the insert operation even though it gets fired after the insert is done and constraints and validations are checked?

  2. Is there any way to detach the execution of trigger from the insert operation?

The failure could be something like a data constraint violation on the other table that the trigger tries to insert to. Even if there is no failure, the transaction time could be improved if the trigger executes outside of the insert transaction.

Upvotes: 0

Views: 121

Answers (2)

Aaron Bertrand
Aaron Bertrand

Reputation: 280431

You could use a queue table and a background job to prevent the trigger from failing the outer transaction. All that would have to succeed is the insert into the queue table.

CREATE TABLE dbo.TriggerProcessingQueue
(
  ...columns...,
  Processed BIT NOT NULL DEFAULT = 0
);

So now the trigger can just perform an insert into this queue table, and some background job could perform the processing of all the rows where Processed = 0 and mark them as Processed = 1. Now, this could interfere slightly with the trigger when the background job is running, but you could fine-tune that by simply adjusting the schedule of the job and/or the number of rows processed each time.

You could also think about service broker, but the activation procedure would have to know what data to deal with...

Upvotes: 1

Tom Squires
Tom Squires

Reputation: 9296

Why is after create trigger considered as part of the insert operation even though it gets fired after the insert is done and constraints and validations are checked?

The insert and trigger are considered one transation. If one fails the transation failed and so is rolled back to preserve data integrity

Is there any way to detach the execution of trigger from the insert operation?

You could wrap it in a try catch block.

Upvotes: 1

Related Questions