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.
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