first a redesign; there is no reason to put this in a trigger. if the trigger fails, your data disappears because the transaction gets rolled back.

the second issue is permissions: every end user who is able to Insert, Update must also be a user in the msdb database, and also be in the DatabaseMailUserRole.

the third is design: this is just a notification that goes out via email, so timing is not of the essence. it doe snot have to be done the absolute millisecond the quantity goes to zero, since you are only notifying people via email,and not kicking off an ordering functionality or something.

instead, create a query that detects whether your quantity has fallen to zero.

UPDATE(Cribben) only tells you if the columns was included in the insert or update statement, not that it changed value.

then you create a scheduled job that runs that query every x minutes.

that job has to check if it already sent an email for that specific outage(don't want to send the same email every x minutes, right?)

the, since the job runs under the context of sa, the permissions issue is resolved.

example of the detection script?

select * from [dbo].[TRANS] T1

WHERE T1.BinQty = 0

AND NOT EXISTS(SELECT * FROM EMailLog E WHERE E.PRIMARYKEY = T1.PRIMARYKEY AND DateNotified > DATEADD(dd,-2,getdate()) )