x

row inserting delay cause sql Trigger

I written c# that store data in SQL table after record insert in table i fire after trigger that check alert set for this this id if set then send SMS from SQL

I inserting row continuously .0001 sec rapidly now while fire 1 row trigger next coming entry not inserted in table so while storing data it takes 10 min.

Is their any other ways...

or other than trigger their is ways that sent SMS on mobile

more ▼

asked Feb 16 at 12:19 PM in Default

avatar image

shridhar
11 3

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

If I understand you correctly, you have an after trigger on a table. The trigger sends a notification to someone via SMS you say. You don't say how the SMS is technically sent, I guess it's done using some type of mail to sms gateway. But that's not really the important thing - what's important is that you have a trigger on a table which is really transaction intensive. The trigger is waiting for some type of network Communication to be performed. Even if an after-trigger is fired after the row is inserted, it's still a part of the same transaction as the insert-operation, therefore, the transaction won't get committed until the trigger is done. I would either use some type of status column on the table, which keeps track of which records have had their notifications sent and have an agent job run at the interval of your choice, to send out the text messages. Another option would be to use service broker, and have the trigger just put a message on a queue, and have the message broker send the text message. That way, you'll get the text-messaging as an asynchronous operation. Check out this blog post on sqlsunday, where a basic message broker setup is described: https://sqlsunday.com/2013/04/14/using-service-broker-to-send-messages/

more ▼

answered Feb 16 at 01:05 PM

avatar image

Magnus Ahlkvist
21.8k 20 41 42

Thank you for quick reply.

I have 10 types of alert When row getting inserted i check alert set for specific id If yes then i sequentioly check alert Alert1 set then send sms alert2 set then send and so on.. Kindly suggest best,

Feb 16 at 04:34 PM shridhar

And so I still don't really get how you send the SMS from SQL Server. But the important thing for you here is to try to get the messaging to happen asynchronously, so that the transaction can commit without having to wait for the transmission of a message. I did make two suggestions, both would work. First suggestion is to set a status flag in the table. Set it to 0 when you insert the row, and have Another process (an agent job or an external service connecting to the database) which reads the rows with status=0, goes through these rows and sends sms-messages where appropriate and updates the status flag to 1 for the row. The other suggestion is to use Service Broker, with the same effect - move the transmission of messages away from the transaction. Both are examples of handling the sms transmissions asynchronously. If you really need the sms to be part of the transaction (if no sms can be sent, the row shouldn't be saved), you'll have to fix the problem with faster disks/more memory/other powerup on the server, though you may find that it still doesn't get any faster because you rely on a messaging service to answer Before a row is committed.

Feb 16 at 07:59 PM Magnus Ahlkvist

Hi I am using SQL server express edition so job agent is not exits. I written procedure to send sms which checking each and every alert condition can you give me idea about call stored procedure in service broker to send messages

Feb 21 at 04:06 AM shridhar

If you look at the link I posted, the SqlSunday.com-post, it gives a pretty good introduction to setting up service broker, sending a message on a queue and firing a stored procedure when messages arrive on a queue.

Feb 21 at 09:10 AM Magnus Ahlkvist
(comments are locked)
10|1200 characters needed characters left

Hi I am using SQL server express edition so job agent is not exits. I written procedure to send sms which checking each and every alert condition can you give me idea about call stored procedure in service broker to send messages

more ▼

answered Feb 17 at 07:36 AM

avatar image

shridhar
11 3

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x148
x41
x9

asked: Feb 16 at 12:19 PM

Seen: 61 times

Last Updated: Feb 21 at 09:10 AM

Copyright 2016 Redgate Software. Privacy Policy