SQL Server after create trigger

Question

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

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?
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.

This can be achieved by exception management inside trigger,check this discussion http://stackoverflow.com/questions/884334/tsql-try-catch-transaction-in-trigger — Pratik, Sep 26 '13 at 16:11
What does "trigger fails" mean? Could you be more specific? The type of failure that happens here can dictate what you can do to work around the problem. — Aaron Bertrand, Sep 26 '13 at 16:22
@AaronBertrand: I have updated my question. Even if there is no failure on the trigger, the transaction time could be improved by letting the trigger run on its own outside of the insert transaction — usha, Sep 26 '13 at 16:41
@AaronBertrand: the trigger parses the data that got inserted and creates a row in another table. Its a pretty complex parsing. So i would want the transaction to not wait until that parsing is complete — usha, Sep 26 '13 at 16:44

score 1 · Answer 1 · answered Sep 26 '13 at 16:03

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?

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.

answered Sep 26 '13 at 16:03

Tom Squires

8,848
12
46
72

since the data integrity is checked before the transaction is executed, why does it have to be part of the transaction? – usha Sep 26 '13 at 16:09
`AFTER specifies that the DML trigger is fired only when all operations specified in the triggering SQL statement have executed successfully. All referential cascade actions and constraint checks also must succeed before this trigger fires.` -> this is from microsoft documentation – usha Sep 26 '13 at 16:11

score 1 · Answer 2 · answered Sep 26 '13 at 16:49

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...

SQL Server after create trigger

2 Answers2