Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Location
    Netherlands
    Posts
    98

    Unanswered: 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
    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/de..._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/de..._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
       
    GO

  2. #2
    Join Date
    Apr 2004
    Posts
    64
    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
    Last edited by TechnicalAli; 07-06-04 at 10:35.

  3. #3
    Join Date
    Oct 2003
    Location
    Netherlands
    Posts
    98
    Thanx Man you where really really helpful!
    Last edited by Wimmo; 07-06-04 at 10:37. Reason: Did not read well, how stupid

  4. #4
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    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.

  5. #5
    Join Date
    Oct 2003
    Location
    Netherlands
    Posts
    98
    Do you have an example for me?

  6. #6
    Join Date
    Oct 2003
    Location
    Netherlands
    Posts
    98
    Quote Originally Posted by TechnicalAli
    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

    Code:
    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="mailto:nbeukenex@jvh.nl?subject=Vragen en/of info over JVH gaming products BV Klachtenregistratiesysteem">nbeukenex@jvh.nl</a>. </div></td>
      </tr>
    </table>
    <p>&nbsp;</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

    Code:
    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://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="mailto:nbeukenex@jvh.nl?subject=Vragen en/of info over JVH gaming products BV Klachtenregistratiesysteem">nbeukenex@jvh.nl</a>. </div></td>
      </tr>
    </table>
    <p>&nbsp;</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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •