trigger is not always running - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

SQL Server Administration (2012)

trigger is not always running

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2014-02-06 : 14:25:21

I have a trigger set up on a table


create TRIGGER [dbo].[insertl] ON [dbo].[images]
FOR INSERT
AS
declare @id int
select @id=id from inserted
exec sendsms @id=@id


GO

Now the strange thing is that it is sometimes and sometimes not - I don't know what the difference is.
For tetsing purposes I added the first line in sendsms sp to add the id to a test table - to verify that it really isn't running for all the records

out of 200,000 rows only 89 got added to the new table
how can I debug why the trigger is only working a small part of the time?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-02-06 : 16:52:14

Are you ALWAYS inserting images one by one?

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2014-02-07 : 00:24:50

the way it works is i have a stored procedure that reads an xml file and insert them all at once
could this be the problem? Is a trigger not active in that case?

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2014-02-07 : 02:59:03

the way it's inserted is as follows (this is done after my #t2 is set to the records in the xml file

is this the problem ? are triggers not run if there is multiple insert

what is the best way to do this trigger

insert into images(date,picture)
select date,picture from #t2

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-02-07 : 03:17:15

the trigger did execute. When it did, the inserted contains all the rows that was inserted into the table
and this statement


select @id=id from inserted

only get one of the id

KH
[spoiler]Time is always against us[/spoiler]

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2014-02-07 : 03:31:16

oh now this all makes sense

I didn't know what

how can I get the list of id's so I can loop through?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-02-07 : 03:55:17

yes. that is one way.

or you can change the sendsms to accept csv or Table-Valued Parameters
http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm
http://msdn.microsoft.com/en-us/library/bb510489%28SQL.100%29.aspx

ideally, you should do the sending of sms outside of trigger.

KH
[spoiler]Time is always against us[/spoiler]

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2014-02-07 : 04:02:55

ideally, you should do the sending of sms outside of trigger.

why is this

the reason I wanted to do a trigger is that I find sometimes jobs don't run
why is it not good to use a trigger?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-02-07 : 05:12:29

it will hold up your transaction while it is sending the sms.

KH
[spoiler]Time is always against us[/spoiler]

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2014-02-10 : 13:02:46

quote:
Originally posted by esthera

ideally, you should do the sending of sms outside of trigger.

why is this

the reason I wanted to do a trigger is that I find sometimes jobs don't run
why is it not good to use a trigger?

If you have an error in your sendsms procedure, the whole insert will be rolled back. If there is a problem with the SMS server that causes the procedure to send to hang - then the insert will hang also.

I would recommend using a service broker for this instead of a trigger. A service broker can be setup to monitor the table and when new rows are added or updated - send out your SMS messages. This is a bit more work - but would be the better option over all.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-02-10 : 14:12:08

quote:
Originally posted by esthera

the reason I wanted to do a trigger is that I find sometimes jobs don't run

I would figure out the root cause behind the job issue. You should not have a trigger like this. But I agree with Jeff that Service Broker should be used. If you don't want to use that, then do it via a job. Do not do the sms in a trigger. It should be done asynchronously.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Kristen
Test

22859 Posts

Posted - 2014-02-10 : 18:26:07

We use the trigger to insert a row(s) in an "SmsToBeSent" table, and then a job runs periodically that processes any Not Yet Sent rows in SmsToBeSent table.

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2014-02-11 : 01:59:15

thanks :)

I put it as a job. occasionally i think jobs stop running - it could be sql is restarted and the agent doesn't start

Kristen
Test

22859 Posts

Posted - 2014-02-11 : 03:13:34

We have an alert on the Front End App if a job like that stops running. We have a Status Bar on all Back Office Staff pages, that is refreshed on every new page and runs an Sproc just to check that "Oldest SmsToBeSent is not more than 5 minutes" and so on. If any of those raises an error it is displayed in the status bar along with "Please inform admin" or something like that.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-02-11 : 12:06:44

quote:
Originally posted by esthera

thanks :)

I put it as a job. occasionally i think jobs stop running - it could be sql is restarted and the agent doesn't start

You need to have alerts in place and figure out why this keeps happening. If SQL restarts, I get paged. If the Agent restarts, it'll attempt to auto-restart, otherwise I'll get paged. Someone needs to be responsible for this server to ensure its health.

Because I don't like to get paged, I get down to the root cause and fix it. I don't just let a system keep failing and paging me.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources