SQL trigger to create email

[Domo]

Using SQL2000.

When a record is added into a particular table with one of 2 specific codes I would like SQL Server to send out an email to two recipients.

I've browsed Google and found most threads/articles confusing and over complicated (they inc. creating tables etc which I don't need).

The email which goes out will need to include data stored in several fields for that record.

Any advice or Urls would be greatly apprecited

I should add I'm not sure how to create the trigger (I know where to create it!) or the code to allow the email!

 

[Reezer]

We'll I'll start by saying triggers are the spawn of Satan - we have a massive product with an ~ 80 gig database per client (5 companies currently running it live) and 80% of the strange database problems or slow downs we come across are because of triggers.

That said, if you don't know how to do the trigger yourself I'd recommend installing SQL Management Studio 2005 CTP which you can get from the MS site. If you then right click on your table you can have it create a template trigger for you which will have everything (and more) that you need.

However, I don't think SQL server can send emails (or make calls to anything that can) so you'll have to have some service polling a table every so often and doing the emails in code when a certain table has a certain record in it.

We do this for welcome letter production; we produce them in batches of 1000 but only email them out once a second or so not to completely thrash the clients email all at once. We put the 1000 in a "to be processed" table then when the service processes one it deletes it and makes an entry in a "sent mail" table.

If you have a dedicated host you can set up a windows service doing it, if not you can probably get a Chron job to call a php/asp page every x seconds/hours which then does the emailing for you.

I would be interested to hear if there is some method of SQL server actively triggering an event such as email but doubt that it's possible (probably not the right thing for a db to be doing but useful none-the-less)...



[EDIT] Actually a quick google came up with http://www.databasejournal.com/features/mssql/article.php/3489111 which shows that it is quite easy to send email from SQL... learn something new every day![/EDIT]


[EDIT2] And to save you finding the management studio thing here is the syntax for a trigger:

-- ================================================
-- Template generated from Template Explorer using:
-- Create Trigger (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- See additional Create Trigger templates for more
-- examples of different Trigger statements.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE TRIGGER <Schema_Name, sysname, Schema_Name>.<Trigger_Name, sysname, Trigger_Name> 
   ON  <Schema_Name, sysname, Schema_Name>.<Table_Name, sysname, Table_Name> 
   AFTER <Data_Modification_Statements, , INSERT,DELETE,UPDATE>
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for trigger here

END
GO

[/EDIT2]

 

[Domo]

Thanks the reply Reezer. Totally appreciated . I was totally stuck!
I won't be able to try this out till tomorrow - I'll post back with what happens.

 

[~J~]

However, I don't think SQL server can send emails (or make calls to anything that can) so you'll have to have some service polling a table every so often and doing the emails in code when a certain table has a certain record in it.

Piece of cake!!

http://www.sqlteam.com/item.asp?ItemID=5003

Or write a DTS package that uses the Outlook extension.

We do them all the time to send reports off the depots on a nightly basis.

 

[Reezer]

I tried it out myself and once I had enabled the ole extensions I got my email straight away. Had to put my isp's smtp server in but other than that it was easy - infact sql 2005 has the stored proc he referes too set up already on master...

I recon this trigger should work but I haven't created the tables to test it

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER SEND_USER_EMAIL 
   ON MESSAGE_DAT
   AFTER INSERT
AS 
BEGIN
	SET NOCOUNT ON;
	declare	
		@_TO varchar(500),
		@_SUBJECT varchar(500),
		@_BODY varchar(4000)
		@_MESSAGE_ID int

	select top (1)
		@_TO = USER_DAT.EMAIL_ADDRESS, 
		@_SUBJECT = MESSAGE_DAT.SUBJECT, 
		@_BODY = MESSAGE_DAT.BODY,
		@_MESSAGE_ID = MESSAGE_DAT.MESSAGE_ID
	from
		USER_DAT,
		MESSAGE_DAT
	where
		USER_DAT.USER_ID = MESSAGE_DAT.USER_ID

	delete from
		MESSAGE_DAT
	where
		MESSAGE_ID = @_MESSAGE_ID

	exec master..usp_send_cdosysmail
		@from = '[email protected]', 
		@to = @_TO,
		@subject = @_SUBJECT,
		@body = @_BODY,
		@smtpserver ='smtp.ntlworld.com',
		@bodytype ='HTMLBody'

END
GO

Good luck