Transaction Order - Debate

Question

Transaction Order - Debate

Viewing 3 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99143 More actions · Answer 1

An after insert trigger is still all part of the same transaction, it just means that the trigger happens after the insertion, rather than before or instead of. If any part of a transaction fails (in this case permission denied), then the whole transaction rolls back.

You'll either need to stop the trigger sending an email when the user making the insert is the service account (use an if statement in the trigger to check the user), grant permission for the service account to send emails, or remove/disable the trigger.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Phil Parkin SSC Guru Points: 247183 More actions · Answer 2

I would consider architecting this differently.
Instead of using a trigger, set up a job which runs periodically (every x minutes), checks for new inserts (I assume that there is a suitable data stamp somewhere) and sends an e-mail if any are found.

Joe Torre SSChampion Points: 10248 More actions · Answer 3

GRANT IMPERSONATE to the Service account on a login having send mail permissions. Add execute as the login with mail permissions to the procedure.


ALTER PROCEDURE myproc @param1 WITH EXECUTE AS 'Login with email permissions'
AS
...
GO