Creating a Trigger that emails when new record is added.

I'm hoping one of you will be able to help me because I haven't had very good luck finding any information on this.

I'm kind of new to SQL, but I'm learning as much as I can.

I created a web form that sends a new record to a SQL 2005 table I setup.

This all works exactly as it should, but I would like to have an email sent out every time a record is added to this table.

I have SQL Mail setup and I ran a test and it worked, but I can't seem to find any info on how to create a trigger that will send an email to me when a new record is added to the table.

My Database is called Engineering

The table is called ESSPartNumLog

And I have the following Columns that I would like to send in my email..

ESSSequence (PK,int, not null)

MaterialType (nvarchar(255, null)

ESSPrefix (nvarchar(255, null)

PartDescription (nvarchar(255, null)

Project (nvarchar(255, null)

PM (nvarchar(255, null)

Any ideas, or can you point me in the right direction?

Hello,

Try this:

create trigger tr_ESSPartNumLog_Insert on dbo.ESSPartNumLog

for insert

as

declare @.id as integer

declare @.q as varchar(255)

--Get ID of record inserted.

select @.id = ESSSequence from inserted

--Create query string to return the record

select @.q = 'select ESSSequence, MaterialType, ESSPrefix, PartDescription, Project, PM

from ESSPartNumLog

where ESSSequence = ' + convert(varchar(10), @.id)

exec msdb.dbo.sp_send_dbmail

@.recipients = 'your_email@.domain.com',

@.subject = 'New record added in ESSPartNumLog',

@.query = @.q,

@.execute_query_database = 'Engineering'

go

Hope this helps.

Jarret