SQL trigger based on a true/false field value

[andy_mcdougall]

Hi,

I have an MS SQL 2000 database with a table that is used to store the header information on product returns. One of the fields in the table is a true/false field that indicates if the return has been processed (become a credit note).

Can anyone tell me how I could go about creating a trigger that would send an email notifying a specific user that this field has been updated from processed=false to processed=true.

To make your eyes bleed a little more, and provide a "why" to this question, when our finance guys process a return and issue a credit note, they then have to switch to their email client and send an email to the internal individual that initiated return process. This takes 11 seconds and leaves the finance guys exhausted. So they have asked if I can automate the process.

Any guidance would be appreciated.

[MCP]

I don't have my sql server readily available, but you could use the "create trigger" command, then use the logical tables "inserted" and "deleted" (which represent what is and what was, respectively) to determine if processed=false in deleted and processed=true in inserted (be sure to handle the case where you have multiple items updated -- even if for a future enhancement that you may not be thinking of). Then you can use xp_sendmail to send the mail -- provided you've got it correctly installed and configured... check books online (SQL Server help) for more info. They have sample code too.

hope this helps.. I just don't have sql server handy to write up a sample..

Hi,

I have an MS SQL 2000 database with a table that is used to store the header information on product returns. One of the fields in the table is a true/false field that indicates if the return has been processed (become a credit note).

Can anyone tell me how I could go about creating a trigger that would send an email notifying a specific user that this field has been updated from processed=false to processed=true.

To make your eyes bleed a little more, and provide a "why" to this question, when our finance guys process a return and issue a credit note, they then have to switch to their email client and send an email to the internal individual that initiated return process. This takes 11 seconds and leaves the finance guys exhausted. So they have asked if I can automate the process.

Any guidance would be appreciated.