After insert trigger exec sp problems

Hi all,

I have an sp that sends cdomail which requires 4 variables.
I want an after insert trigger that fills in the values for the sp from the record just submitted, how can i do that?

Sp code
CREATE PROCEDURE [dbo].[sp_send_cdosysmail]
@.From varchar(100) ,
@.To varchar(100) ,
@.Subject varchar(100)=" ",
@.Body varchar(4000) =" "
/************************************************** *******************

This stored procedure takes the parameters and sends an e-mail.
All the mail configurations are hard-coded in the stored procedure.
Comments are added to the stored procedure where necessary.
References to the CDOSYS objects are at the following MSDN Web site:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_messaging.asp

************************************************** *********************/
AS
Declare @.iMsg int
Declare @.hr int
Declare @.source varchar(255)
Declare @.description varchar(500)
Declare @.output varchar(1000)

--************* Create the CDO.Message Object ************************
EXEC @.hr = sp_OACreate 'CDO.Message', @.iMsg OUT

--***************Configuring the Message Object ******************
-- This is to configure a remote SMTP server.
-- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp
EXEC @.hr = sp_OASetProperty @.iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
-- This is to configure the Server Name or IP address.
-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @.hr = sp_OASetProperty @.iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'smtp.bbeyond.nl'

-- Save the configurations to the message object.
EXEC @.hr = sp_OAMethod @.iMsg, 'Configuration.Fields.Update', null

-- Set the e-mail parameters.
EXEC @.hr = sp_OASetProperty @.iMsg, 'To', @.To
EXEC @.hr = sp_OASetProperty @.iMsg, 'From', @.From
EXEC @.hr = sp_OASetProperty @.iMsg, 'Subject', @.Subject

-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @.hr = sp_OASetProperty @.iMsg, 'HTMLBody', @.Body
EXEC @.hr = sp_OAMethod @.iMsg, 'Send', NULL

-- Sample error handling.
IF @.hr <>0
select @.hr
BEGIN
EXEC @.hr = sp_OAGetErrorInfo NULL, @.source OUT, @.description OUT
IF @.hr = 0
BEGIN
SELECT @.output = ' Source: ' + @.source
PRINT @.output
SELECT @.output = ' Description: ' + @.description
PRINT @.output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END

-- Do some error handling after each step if you have to.
-- Clean up the objects created.
EXEC @.hr = sp_OADestroy @.iMsg

GOTry:

CREATE TRIGGER your_trigger_name ON dbo.your_table_name
FOR INSERT
AS

SET NOCOUNT ON

DECLARE @.From varchar(100) ,
DECLARE @.To varchar(100) ,
DECLARE @.Subject varchar(100),
DECLARE @.Body varchar(4000)

SELECT @.From = i.From, @.To = i.To, @.Subject = i.Subject, @.Body = i.Body FROM inserted i

EXEC sp_send_cdosysmail @.From, @.To, @.Subject, @.Body

SET NOCOUNT OFF|||Thanx Man you where really really helpful!|||the best way would be not to do so.
instead store the records in a staging table and then configure a job to send the mails.|||Do you have an example for me?|||Try:

CREATE TRIGGER your_trigger_name ON dbo.your_table_name
FOR INSERT
AS

SET NOCOUNT ON

DECLARE @.From varchar(100) ,
DECLARE @.To varchar(100) ,
DECLARE @.Subject varchar(100),
DECLARE @.Body varchar(4000)

SELECT @.From = i.From, @.To = i.To, @.Subject = i.Subject, @.Body = i.Body FROM inserted i

EXEC sp_send_cdosysmail @.From, @.To, @.Subject, @.Body

SET NOCOUNT OFF

This workes, i used this in an insert an update trigger, now when i insert a new record it fires the insert 1 time and the update 4 times which generates 4 emails when only 1 is the good one,

The triggers are:

Insert trigger

CREATE TRIGGER KRS_email_insert ON dbo.KRS_KRFID
after INSERT
AS

SET NOCOUNT ON

DECLARE @.From varchar(100)
DECLARE @.To varchar(100)
DECLARE @.Subject varchar(100)
DECLARE @.Body varchar(4000)

SELECT @.From = 'Klachtenregistratiesysteem',
@.To = i.email,
@.Subject ='nieuwe melding onder volgnummer '+ cast(i.volgnummer as varchar),
@.Body = '<style type="text/css">
<!--
.style1 {color: #FF0000}
body {
background-color: #FFFFFF;
}
-->
</style>
<p>
<table width="*" border="0">
<tr>
<td colspan="2">Geachte '+M.NAAM+',<br>U heeft een klachtregistratieformulier ingevuld bij het JVH gaming products Klachtenregistratiesysteem, uw klacht is in het systeem opgeslagen onder volgnummer: <span class="style1">'+ cast(i.volgnummer as varchar)+'<br><br><br></span></td>
</tr>
<tr>
<td width="*" bgcolor="#CCCCCC"><div align="right">Onderwerp:</div></td>
<td width="*" bgcolor="#FFFFCC"><div align="left">'+i.onderwerp +'</div></td>
</tr>
<tr>
<td width="*" bgcolor="#CCCCCC"><div align="right">Probleemomschrijving:</div></td>
<td width="*" bgcolor="#FFFFCC"><div align="left">'+i.probleemomschrijving +'</div></td>
</tr>
<tr>
<td width="*" bgcolor="#CCCCCC"><div align="right">Melddatum:</div></td>
<td width="*" bgcolor="#FFFFCC"><div align="left">'+cast(i.melddatum as varchar) +'</div></td>
</tr>
<tr>
<td width="*" bgcolor="#CCCCCC"><div align="right">Evaluatiedatum:</div></td>
<td width="*" bgcolor="#FFFFCC"><div align="left">'+cast(i.evaluatiedatum as varchar) +'</div></td>
</tr>
<tr>
<td width="*" bgcolor="#CCCCCC"><div align="right"></div></td>
<td width="*" bgcolor="#FFFFCC"><div align="left"></div></td>
</tr>
<tr>
<td colspan="2"><div align="right"><br><br><br></div> <div align="left">LET OP: Deze e-mail is verzonden door een automatische mailbox, vragen die u naar deze mailbox stuurt worden niet beantwoord. <br>
Voor vragen of opmerkingen kunt u terecht bij het Niels Beukenex, telefoon: 0900-1793 of via email: <a href="http://links.10026.com/?link=mailto:nbeukenex@.jvh.nl?subject=Vragen en/of info over JVH gaming products BV Klachtenregistratiesysteem">nbeukenex@.jvh.nl</a>. </div></td>
</tr>
</table>
<p> </p>'
FROM inserted i, MAN_MEDEWERKERS m
--WHERE MELDDATUM < GETDATE()and m.uid = i.melder
EXEC sp_send_cdosysmail @.From, @.To, @.Subject, @.Body

SET NOCOUNT OFF

Update trigger

CREATE TRIGGER KRS_email_update ON dbo.KRS_KRFID
After update
AS

SET NOCOUNT ON

DECLARE @.From varchar(100)
DECLARE @.To varchar(100)
DECLARE @.Subject varchar(100)
DECLARE @.Body varchar(4000)

SELECT @.From = 'Klachtenregistratiesysteem',
@.To = i.email,
@.Subject ='Uw melding met volgnummer '+ cast(i.volgnummer as varchar)+' is bewerkt',
@.Body = '<style type="text/css">
<!--
.style1 {color: #FF0000}
body {
background-color: #FFFFFF;
}
-->
</style>
<p>
<table width="*" border="0">
<tr>
<td colspan="2">Geachte '+M.NAAM+',<br>U klacht met volgnummer <span class="style1">'+ cast(i.volgnummer as varchar)+' </span> is gewijzigd, klik <a href="http://links.10026.com/?link=http://pc/Support/KRS_KRFID/ShowKRS_KRFIDRecord2.aspx?KRS_KRFID='+ cast(i.volgnummer as varchar)+'">hier</a> voor meer details<br><br><br></td>
</tr>
<tr>
<td width="*" bgcolor="#CCCCCC"><div align="right">Onderwerp:</div></td>
<td width="*" bgcolor="#FFFFCC"><div align="left">'+i.onderwerp +'</div></td>
</tr>
<tr>
<td width="*" bgcolor="#CCCCCC"><div align="right">Probleemomschrijving:</div></td>
<td width="*" bgcolor="#FFFFCC"><div align="left">'+i.probleemomschrijving +'</div></td>
</tr>
<tr>
<td width="*" bgcolor="#CCCCCC"><div align="right">Melddatum:</div></td>
<td width="*" bgcolor="#FFFFCC"><div align="left">'+cast(i.melddatum as varchar) +'</div></td>
</tr>
<tr>
<td width="*" bgcolor="#CCCCCC"><div align="right">Evaluatiedatum:</div></td>
<td width="*" bgcolor="#FFFFCC"><div align="left">'+cast(i.evaluatiedatum as varchar) +'</div></td>
</tr>
<tr>
<td width="*" bgcolor="#CCCCCC"><div align="right"></div></td>
<td width="*" bgcolor="#FFFFCC"><div align="left"></div></td>
</tr>
<tr>
<td colspan="2"><div align="right"><br><br><br></div> <div align="left">LET OP: Deze e-mail is verzonden door een automatische mailbox, vragen die u naar deze mailbox stuurt worden niet beantwoord. <br>
Voor vragen of opmerkingen kunt u terecht bij het Niels Beukenex, telefoon: 0900-1793 of via email: <a href="http://links.10026.com/?link=mailto:nbeukenex@.jvh.nl?subject=Vragen en/of info over JVH gaming products BV Klachtenregistratiesysteem">nbeukenex@.jvh.nl</a>. </div></td>
</tr>
</table>
<p> </p>'
FROM inserted i, MAN_MEDEWERKERS m
where i.melder = m.uid
EXEC sp_send_cdosysmail @.From, @.To, @.Subject, @.Body

SET NOCOUNT OFF


When a new record is inserted it will fill in the email field en the afdeling field from another table trough 2 other triggers.
After that the insert email trigger should run...

Now it runs the update email trigger 4 times when inserting a new record.
Can someone help me with this