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

    Unanswered: Problems with insert trigger

    HI,

    I have this trigger which execute an sp for emailing.
    This should happen when a user add a new record, now what happens is that when a user insert a new record it sends an email with the record before.

    example.
    I added record 100, i get an email with record 99.

    the trigger is this
    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 Klachtenregistratiesysteem, uw klacht is in het systeem opgeslagen onder volgnummer: <span class="style1">'+ cast(i.volgnummer as varchar)+'<br><br><br></span>
    	klik <a href="http://web01/Support/KRS_KRFID/ShowKRS_KRFIDRecord2.aspx?KRS_KRFID='+ cast(i.volgnummer as varchar)+'">hier</a> voor meer details<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 Editor, telefoon: -1793 of via email: <a href="mailto:Test@test.com?subject=Vragen en/of info over CompanyKlachtenregistratiesysteem">Test@test.com</a>. </div></td>
      </tr>
    </table>
    <p>&nbsp;</p>'
    FROM KRS_KRFID i, MAN_MEDEWERKERS m
    WHERE  MELDDATUM  < GETDATE()and m.uid = i.melder
    EXEC sp_send_cdosysmail @From, @To, @Subject, @Body
    
    SET NOCOUNT OFF
    Can someone tell me what is wrong here!!
    Cheers Wim

  2. #2
    Join Date
    Aug 2002
    Location
    Prague
    Posts
    77
    I guess you should use 'inserted' virtual table to see which record was inserted into the table and base your mail on that. Check 'inserted', 'deleted' virtual tables in SQL Server documentation on triggers. mojza

  3. #3
    Join Date
    Oct 2003
    Location
    Netherlands
    Posts
    98
    Hi Mojza,

    I tried that but i doesn't send anything.
    Have to find a workaround!

Posting Permissions

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