Email when record added to database

[ldy_hale]

I currently created an application in asp where a user can enter a project request and I would like to know how to have an email sent to a specified person when a new recorded has been added. I have not ever used this before and not sure what I need to do for this. Wondering if I could get any feedback on how to do this and how complex this would be to complete.

Thx

[sevenhalo]

It's not too bad if you have MSSQL.
MS SQL 2000 INSERT Trigger code:

CREATE TRIGGER trig_NewEmp ON [dbo].[Employees] 
FOR INSERT
AS
	EXEC Master .. XP_SENDMAIL @recipients = 'Me@MyDomain', @subject = 'New Emp Added', @Message = 'We got new peeps'

That snippet is designed for NorthWind.Employees

[ldy_hale]

That does look very simple but can you explain this code to me somewhat. I understand triggers but what is the FOR INSERT for?

thx

[sevenhalo]

You can apply a trigger to happen for INSERT, UPDATE and/or DELETE. That line is saying "On Insert, do this."

[ldy_hale]

Als would this go in the page with the submit button or the save page?

[sevenhalo]

Neither, it goes on your database.

The code is transact. If you're using MS SQL, right click the table and go to design. One of the toolbar buttons will be called "Triggers." If you click on that, the template is loaded and you can apply code there.

If you're wanting to execute this everytime a record is added to the table, it's best to do it in the database. A year or two from now, there could potentially be other applications that will insert into this table as well. If you have the functionality tied to the table and not your application, it will work without a hitch.

[sevenhalo]

If you want to go the app-side route, this thread might be of some help:
http://www.vbforums.com/showthread.php?t=388700

[ldy_hale]

OK I understand some what now but I get an error when I try to add a new record:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E09)
[Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE permission denied on object 'xp_sendmail', database 'master', owner 'dbo'.

Do I need to add something to my insert statement?

[sevenhalo]

You need to give dbo (or whatever login/user you plan on using) permission to xp_sendmail.

To do this (I'll use dbo as an example), you would do the following:
Go into your master database, right click the user in question and select properties. From here, click the permissions buttons and you should get something like the following on your screen:

Make sure that xp_sendmail has execute priviledges for that user.

[sevenhalo]

Do I need to add something to my insert statement?

Nope, nothing. This is totally database side. Anytime you use a program, QA or manually insert a record; this trigger will fire.

[ldy_hale]

OK, I understand. I do not have access to go in and set that setting, since it is for my company. I guess I will have fill out more paper work to get that done. I do appreciate all your help. You are very knowledgable about ASP.

Thx

[Beast777]

I am interested in using this trigger method to send emails. I am looking at your screenshot of the database user but when I bring up my database user there are no xp_anythings. Is there something I need to do to add them first? I wish to use the xp_sendemail.

[mendhak]

What are you logged in as? sa?

[Beast777]

The administrator for the box, not the sa.

[sevenhalo]

Are you looking through the Master database permissions ?

The function is specific to the master database, it won't be listed under anywhere else. You'd need to alter it there.

[Beast777]

Thank you. that was it.

[Beast777]

I have the trigger setup but it doesn't seem to do anything so I put the trigger in to the sql query analyzer and got this for an error:

Server: Msg 18030, Level 16, State 1, Line 0
xp_sendmail: Either there is no default mail client or the current mail client cannot fulfill the messaging request. Please run Microsoft Outlook and set it as the default mail client.

My query is
Exec Master .. XP_SENDMAIL @recipients = [email protected]', @subject = 'New Telecomm Request'

[mendhak]

http://support.microsoft.com/kb/263556/