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.

insert then update...

greetings
I am developing an application for the marketing dept at my company.Basically users can build the content of an email to be sent to oursubscriber database.
I am wanting the application to initailly save the content into a database, the update the most recently inserted row.
The save button uses the following SQL command:
Dim SqlMethod As String ="INSERT INTO CZC_email (Offer, SendDate, Destinations, Copy, BannerURL)VALUES ('" & txtCampaignName.Text & "','" &calCampaignDate.SelectedDate.ToString("yy/dd/MM") & "','" &DestinationsSelected & "','" & FreeTextBox2.Text & "', '"& txtBannerPath.Text & "')SELECT @.@.IDENTITY AS 'CZ_ID'"
And my update button has this SQL command:
Dim SqlMethod As String ="UPDATE CZC_email SET SendDate = '" &calCampaignDate.SelectedDate.ToString("yy/dd/MM") & "', Offer = '"& txtCampaignName.Text & "',Destinations = '" &DestinationsSelected & "', BannerURL = '" & txtBannerPath.Text& "' WHERE CZ_ID = @.@.IDENTITY "
but it doesnt seem to be updating. anyone know what I'm doing wrong?
Cheers

(1) Use a stored proc.
(2) Use SCOPE_IDENTITY() instead of @.@.IDENTIY. Check books on line for the differences.
(3) Use Parameterized Queries to prevent SQL Injection atatcks (google for more info on this).|||Save the @.@.IDENTITY values you get from insert command & then in the update command pass the value returned from the insertion instead of @.@.IDENTITY|||I've tried saving the @.@.identity and scope_identity as a value ofvariable varCZ_ID by using the following code (i'm using the MS DAAB)
varCZ_ID = dataReader("SCOPE_IDENTITY")
or

varCZ_ID = dataReader("CZ_ID")

however, this is erroring 'Invalid attempt to read when no data is present.'
Any ideas what I'm doing wrong? this is really doing my head in!
Thanks
|||If you use a stored proc you could save a trip to the server and get back accurate Id.
|||yes I agree, I just wanted to get it working first off.
I managed to fix the problem by saving the @.@.IDENTITY into a session variable.

|||@.@.IDENTITY does not always give you the identity value that just gotgenerated by the insert statement. If multiple calls were made at thesame time it could mix up the Id's. So it is advised to useSCOPE_DENTITY() instead of @.@.IDENTITY.
|||Thats a good point, and something I am aware off.
For this particular application is not a big deal, as it only going to be used by two people - and not at the sametime.
However i will look to improve it soon, and that will be things I will do.
Cheers