SQL Email Trigger on new record

[RJL1]

I have a order database. We receive and order from the customer and it is porcess by the system. If an item is short (not enough stock) on the order it creates a new record with the items that is short and an indicator that this is a back order.

I need to set a trigger that send an email every time a back order is created with the order number from the new record.

I'm very green on triggers so as much help as possible on this one would be appreciated.

The 3 fields involve are

INTERNAL_SHIPMENT_NUM
SHIPMENT_ID
USER_DEF2 (this would be set to BO if its a back order)

Thanks

 

[gmmastros]

I would encourage you to NOT do this. I know a lot of SQL Server DBA's (database administrators) and not a single one would allow a trigger to send an email.

Instead, I would suggest that you add a new table to your system. The table should include all of the information you need to send the email, like recipient email address, subject line, content of the email, a status flag for the email (pending, processing, sent), etc...

Then you should write your trigger to insert data in to this table. This will allow the trigger to operate as quickly as possible without slowing down your system.

Finally, write a scheduled task that fires every couple minutes to actually send the email. Tasks would run on a separate thread of execution asynchronously so there wouldn't be any performance penalty.

Make sense?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[RJL1]

Again gmmastros another great suggestion. I will work on this. I'm already using some schedule taks to send results of stored procedures that create a table (snapshot of the invetory) so this should be similar.

being new to triggers I'm guessing the trigger will fire when the recorrd is created and insert the data I need into the new table.

Thanks

 

[gmmastros]

You can write triggers for various situations. A trigger can be fired when an insert occurs, update, or even delete. Depending on your application, you may want to consider a trigger that fires for new rows AND updated rows.

Imagine a situation like this... someone wants to buy your widgets. Originally, they WANT to buy 1,000 widgets but accidentally enter 100. If you only have 500 on hand, you originally have enough to fulfill the order, but then an update occurs changing this from 100 to 1,000 and now you don't have enough on hand. Conversely, if a customer originally orders 1,000 and then updates the order to 100, what was originally on back-order can now be shipped, right? Obviously, you cannot un-send an email, but you should be flipping the appropriate bits so that the order will ship.

Anyway... I would suggest that you write trigger the way that you think it should be, and then post it here so that we can take a look. There are some hidden gotchas with triggers so you need to be careful.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom