Sending email when a record is added

[MikeV]

Hello,

We have an online form that collects user info and writes it to our SQL server.
I want to send an email notification every time a new record is added so
that some follow up can be done.

I recognize that I need to write an insert trigger, but I don't know how
to get the contents of the row that was just added and send that in the body
of the email.

I created a simple test trigger and used my browser to fill out the form
and submit it

Here is the trigger I used:

CREATE TRIGGER [New Lead] ON [table1]
FOR INSERT
AS

EXEC master..xp_sendmail 'TestAccount', 'Insert Trigger Test'

When I submit the form, I get this error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e09'

[Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE permission denied
on object 'xp_sendmail', database 'master', owner 'dbo'.

/inforequest2.asp, line 157


2 questions:
1. Why am I getting that error
2. How do I write the trigger to send the contents of the record via email

Thanks!

[Mike]

Ok, I figured out that how to change the permissions on xp_sendmail so I am
no longer getting that permissions error.

Can anyone post some trigger code that will send record contents via email
on an insertion?

Thanks!

"MikeV" <mike.veltre@house-loan.com> wrote:
>
>Hello,
>
>We have an online form that collects user info and writes it to our SQL
server.
> I want to send an email notification every time a new record is added so
>that some follow up can be done.
>
>I recognize that I need to write an insert trigger, but I don't know how
>to get the contents of the row that was just added and send that in the
body
>of the email.
>
>I created a simple test trigger and used my browser to fill out the form
>and submit it
>
>Here is the trigger I used:
>
>CREATE TRIGGER [New Lead] ON [table1]
>FOR INSERT
>AS
>
> EXEC master..xp_sendmail 'TestAccount', 'Insert Trigger Test'
>
>When I submit the form, I get this error:
>
>Microsoft OLE DB Provider for ODBC Drivers error '80040e09'
>
>[Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE permission denied
>on object 'xp_sendmail', database 'master', owner 'dbo'.
>
>/inforequest2.asp, line 157
>
>
>2 questions:
>1. Why am I getting that error
>2. How do I write the trigger to send the contents of the record via email
>
>Thanks!
>