CODEWITHSUNDEEP
sql-serversql-server-2008triggersdatabase-securitysql-server-job
Chris Cannon
Chris Cannon

Reputation: 1167

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

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.

Upvotes: 3

Views: 1933

Answers (2)

Diego
Diego

Reputation: 36156

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

Upvotes: 2

Andomar
Andomar

Reputation: 238196

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.

Upvotes: 1

Related Questions