Trigger on Insert statement

Question

Post reply

Trigger on Insert statement

cpatel

SSC Veteran

Points: 246
More actions
November 8, 2010 at 3:15 pm

#242699

Hi,
I develop one program and I am generating invoices for my customer using web services. This web services is taking data and calculating it and generating invoices. If I have 200 customer than it is taking 10 mins to generate invoice through web services (which is written in .net using IP connection).
I want to remove this time so I created one permanent temp table where I am inserting one row that how many invoices I have to generate. In this table I wrote one trigger which take newly inserted record and using one stored procedure it will generate invoices.
Now I am not using web services but when I am inserting record from outside (using PHP) into my permanent temp table and it is taking same time because insert statement is not relesing cursor until it finish trigger.
Is there any way where I insert record in temp table and it fire trigger but do not hold process until it finish?
Please let me know if you need more clearification on this.
Thanks in Advance,
Chandresh Patel

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135658 More actions · Answer 1

as you have noticed any trigger is "in transation".

Your transaction will not complete until your trigger is completed.

Check out SQLServer service broker to get a async solution for this.

Service broker is a SQLserver message queuing solution, which supports a payload and the concept of a conversation.

Another thing you could do is just store the affected primary key info in your trigger to a staging table and have a sqlagent job processing this table in the background.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data and code to get the best help

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Who am I ? Sometimes this is me but most of the time this is me

cpatel SSC Veteran Points: 246 More actions · Answer 2

I will try this but tell me that if I have to execute stored procedure in trigger with async than how can I call that.

Thanks

Johan Bijnens SSC Guru Points: 135658 More actions · Answer 3

cpatel (11/8/2010)
I will try this but tell me that if I have to execute stored procedure in trigger with async than how can I call that.
Thanks

Don't rush in to fast.

I would go for the sqlagent solution to start with. This will buy you some time to get confortable with SSB.

I've published a little article some time ago, that may help you getting started:

http://www.sqlservercentral.com/articles/Service+Broker/2897/

There are a number of other good articles at SSC.

If you want a book, I would recommend Klaus Aschenbrenners