April 18, 2002 at 12:07 pm
Hi there,
I am trying to write my first trigger. I need it to fire off an email to the help desk when the enroll_separation field in the academic table is changed from enrolled to kicked out. Does anyone have any idea where I could look for an example? Never having done this before I am at a serious disadvantage.
Thanks
Laura
April 18, 2002 at 1:18 pm
I wouldn't do the mail in a trigger but rather a stored procedure and then create a job to run the procedure every 5 minutes or so. That way when (and notice I said when) you have SQL Mail hang then your table won't get locked up. Just my two cents.
The trigger would be great to populate a "notices" table that would have information like Mail recipient, Message, Subject, etc. You may want to consider looking at the variables used in xp_sendmail and creating your table "notices" to be similar in style. Then your procedure could loop through this table to send out the notices on the schedule mentioned above.
If you would like an example, let me know and I will put something up.
Hope this helps.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
April 19, 2002 at 4:27 am
David,
Stored procs I can do! I'll try to make it on my own, if I have problems I'll come back. Can xp_sendmail work with SQL 7.0 or is that just with SQL 2K?
Thanks again
Laura
April 19, 2002 at 4:44 am
xp_sendmail works in both versions.
Andy
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply