What have you learned, read, looked at that caused you to come to the conclusion that a trigger is the best way to do this?
Fair. I came here looking for help, I should not assume I am right.
The reason for this is working for is because you are either testing this with a high level of permissions, or the login that the application is using has a very high level of permissions. Not a good practice in either case.
When I am working on the triggers, I am doing so with a high level of access to the database in question. The software itself also runs at a high level - but that is unfortunately by design. The application handles security against the database internally. I am not going to say it is right or wrong, but that is how it works.
If running out of inventory is the cardinal sin, wouldn't it make more sense to define a minimum quantity, and when that is hit, start the process to trigger re-ordering of the item?
From what you are saying, zero puts the users out of business until more stock comes in. Don;t let that happen.
All these things exist already, but inventory does not just appear. If an item normally has 1 piece a week consumed, and an employee takes a burst run at it - and pulls all 10 on hand pieces. We want to know. Our other processes automatically order it - and we have been handling those actions for our customers. I just want the specific incident of zero to trigger deeper and immediate investigation.
At minimum, use the trigger to insert a record into a table that says "do something" when another process looks at it.
When you call sp_send_dbmail, any latency from the time the proc is called until the mail is actually processed by the SMTP server will cause your code to wait.
To the users, this will appear to be an application hang.
Good to know. I was not aware a hang in SMTP would delay from sp_send_dbmail. I was under the impression that the process was handed off. Other areas where I use this, have not had any performance issues we have noticed, so I did not correlate. Fortunately the way our remote systems work, they store and send when the employee is complete - like a checkout record. Which means they have already walked away. They will not see the latency. Our management team will however, so that is good to know.
To learn more on Service Broker, go here:
Type in Service Broker.
I stared at that screen for a good ten minutes. You forgot to say press search or enter. 😛
(Yes I already did this before you typed your reply, I just simply asked because sometimes people have resources that they have found very useful, and if they had one to share, I would have appreciated reading it. That is all.)