SQL Server 2008 - Execute sp_start_job from Insert Trigger - Does the job run asynchronously?

Question

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.

Answer 1

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

Answer 2

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.