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

Creating a Matrix report from an OLAP cube

Hello there
I am trying to create a Matrix report from an OLAP cube
but not having any luck. the end result I want is the
UserName along the Row section, Months of the year along
the column section (the date range will be specified in
the MDX statement), and the Data will be a count of hits
(which is my Measure) to my website on a per month basis
and Grouped By the UserName. My MDX statement looks like
this:
SELECT
{[Time].[Calendar].[2004].[May]:[Time].[Calendar].[2004].
[June]} on Columns ,
{[Business].[User].[Business Entity Id].[5611].Children }
on Rows
FROM UsageStats_Phase1
This query executed in the MDX Sample Application returns
exactly what i want. but I am having trouble getting the
same information in a Matrix or Table report. Would anyone
have any idea how to go about doing this? I'm quite new to
MDX, OLAP, and Reporting Services (not a very strong
position to be in, i know!) - so any help would be very
much appreciated!
Thanks in anticipation
MariaMaybe you need to explicit the measures (something like:
SELECT {[Measures].[<MISURE_NAME>], [Measures].[<MISURE_NAme>],...} on Columns,
{[Business].[User].[Business Entity Id].[5611].Children }
on Rows,
{[Time].[Calendar].[2004].[May]:[Time].[Calendar].[2004].
[June]} ON PAGES
FROM UsageStats_Phase1
maybe you need also to use the DESCENDANTS function for rows and pages
)
than, using a matrix control, drag the measures in the data fields.
hth
Antonio
"Jano" wrote:
> Hello there
> I am trying to create a Matrix report from an OLAP cube
> but not having any luck. the end result I want is the
> UserName along the Row section, Months of the year along
> the column section (the date range will be specified in
> the MDX statement), and the Data will be a count of hits
> (which is my Measure) to my website on a per month basis
> and Grouped By the UserName. My MDX statement looks like
> this:
> SELECT
> {[Time].[Calendar].[2004].[May]:[Time].[Calendar].[2004].
> [June]} on Columns ,
> {[Business].[User].[Business Entity Id].[5611].Children }
> on Rows
> FROM UsageStats_Phase1
>
> This query executed in the MDX Sample Application returns
> exactly what i want. but I am having trouble getting the
> same information in a Matrix or Table report. Would anyone
> have any idea how to go about doing this? I'm quite new to
> MDX, OLAP, and Reporting Services (not a very strong
> position to be in, i know!) - so any help would be very
> much appreciated!
> Thanks in anticipation
> Maria
>|||Thanks for the reply. I managed to get around it by doing
the following:
SELECT
{ Measures.members } on Columns ,
{ Crossjoin( [Time].[Calendar].[2004].[May]:[Time].
[Calendar].[2004].[July], [Business].[User].[Business
Entity Id].[5611].Children ) } on Rows
FROM UsageStats_Phase1
All the best
Jano
>--Original Message--
>Maybe you need to explicit the measures (something like:
>
>SELECT {[Measures].[<MISURE_NAME>], [Measures].
[<MISURE_NAme>],...} on Columns,
>{[Business].[User].[Business Entity Id].
[5611].Children }
>on Rows,
>{[Time].[Calendar].[2004].[May]:[Time].[Calendar].[2004].
>[June]} ON PAGES
>FROM UsageStats_Phase1
>maybe you need also to use the DESCENDANTS function for
rows and pages
> )
>
>than, using a matrix control, drag the measures in the
data fields.
>
>hth
>Antonio
>
>"Jano" wrote:
>> Hello there
>> I am trying to create a Matrix report from an OLAP cube
>> but not having any luck. the end result I want is the
>> UserName along the Row section, Months of the year
along
>> the column section (the date range will be specified in
>> the MDX statement), and the Data will be a count of
hits
>> (which is my Measure) to my website on a per month
basis
>> and Grouped By the UserName. My MDX statement looks
like
>> this:
>> SELECT
>> {[Time].[Calendar].[2004].[May]:[Time].[Calendar].
[2004].
>> [June]} on Columns ,
>> {[Business].[User].[Business Entity Id].
[5611].Children }
>> on Rows
>> FROM UsageStats_Phase1
>>
>> This query executed in the MDX Sample Application
returns
>> exactly what i want. but I am having trouble getting
the
>> same information in a Matrix or Table report. Would
anyone
>> have any idea how to go about doing this? I'm quite new
to
>> MDX, OLAP, and Reporting Services (not a very strong
>> position to be in, i know!) - so any help would be very
>> much appreciated!
>> Thanks in anticipation
>> Maria
>.
>