3

I wish to use sp_start_job to start a job from within an insert trigger.

The table which the insert trigger is on only has a few inserts a day atm.

The point of doing this is so that the insert trigger doesn't have to wait for the job to complete - is this correct?

The job sends emails to key people within the company notifying of new data.

Also, what are the drawbacks of this idea?

Does the login/user which is used to insert the data need any special permissions/roles?

Or does the job need to be owned by the login/user?

Does the trigger run with the login/user's permissions?

Thanks in advance for your help.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Chris Cannon
  • 1,157
  • 5
  • 15
  • 36

2 Answers2

2

yes, you can see from the output of the procedure that it doesn't send the email on the fly, it adds it to a queue so it wont lock anything by waiting the email to be sent and then continue with the trigger execution.

you can see your email log at 

SELECT * FROM sysmail_log

another query that may help you is 

SELECT * FROM sysmail_mailitems
Diego
  • 34,802
  • 21
  • 91
  • 134
  • Ok thanks for that clarification. But in the future, I may still wish to start a job from a trigger or stored procedure using sp_start_job - does the calling procedure have to wait for the job to complete? – Chris Cannon Apr 05 '12 at 14:16
1

Sending email using sp_send_email is really fast. You can call it from inside a trigger an probably lose 20 milliseconds. Not something you'd normally need to do asynchronously.

Andomar
  • 232,371
  • 49
  • 380
  • 404
  • Yeah I am right in thinking that sp_send_email actually puts the email in a queue and can complete before the email is actually sent? – Chris Cannon Apr 05 '12 at 14:05