Insert Trigger not getting row data for email body

hello,

need help with a simple trigger i have been working on. the trigger automatically sends me an email out when a record is inserted, how ever i can't seem to get the row column data into the email. The part i do not understand is that I get the row column data information in the email if I update the row.

This is for 2005 SQL

Any direction would be greatly appreaciated

OneIDesigned

Maybe the code for the trigger is incorrect?

|||

Here is the trigger code

<code>

setANSI_NULLSON

setQUOTED_IDENTIFIERON

go

ALTERTRIGGER [dbo].[TestimonalEmailInsert]

ON [dbo].[CMRC_Testimonal]

AFTERINSERT

AS

BEGIN

SETNOCOUNTON;

declare @.TestimonalIDint

declare @.FullNamenvarchar(150)

declare @.FullNameNewnvarchar(150)

declare @.CompanyNamenvarchar(150)

declare @.CompanyNameNewnvarchar(150)

declare @.Emailnvarchar(150)

declare @.EmailNewnvarchar(150)

declare @.Testimonalvarchar(2000)

declare @.TestimonalNewvarchar(2000)

declare @.messagevarchar(2000);

SELECT @.TestimonalID= [dbo].[CMRC_Testimonal].TestimonalID,

@.FullName= [dbo].[CMRC_Testimonal].FullName

FROM [dbo].[CMRC_Testimonal]WHERE [dbo].[CMRC_Testimonal].TestimonalID=(SELECT TestimonalIDFROM Inserted)

Set @.message='Testimonial ID '+ltrim(@.TestimonalID)+' has been updated'+

' previous First Name is '+ @.FullName+' and the new Full Name is '+ @.FullNameNew

EXEC msdb.dbo.sp_send_dbmail

@.profile_name='MSQLMAILProfile',

@.recipients='myemail@.myemail.com,

@.body= @.message,

@.subject='Testimonal has been inserted';

END

</code>

Hope this helps

|||

Do you use a Stored Procedure to do the insert? If so, you can use Visual Studio to step into the procedure and then into the trigger. This will allow you to debug the trigger and see if any of the variables are being set.

Also, you should change your sql statement to use a join to the inserted table.

|||

Basically I found that the problem was related to having <NULL> in some of the rows of column data that I was trying to insert into the body of the email. If one of the colums had a <NULL> the entire body of the email would not show up.

The work around I am currently using is to add default information at the table level so each record will column will have data. "Not Supplied"

I would like to configure the trigger to insert "" into the body if <NULL> exist, but I have not found any code to resolve the issue at the trigger level.

Thanks

|||

OneIdesigned:

The work around I am currently using is to add default information at the table level so each record will column will have data. "Not Supplied"

That isn't really a good idea. Null is different to "Not Supplied" as it means it is an unknown quantity so you should leave it as Null if the user hasn't supplied it.

OneIdesigned:

I would like to configure the trigger to insert "" into the body if <NULL> exist, but I have not found any code to resolve the issue at the trigger level.

Use the IsNull function.