sql - Email trigger when data is changed -

i created trigger send email when field aantal changed problem won't deleted , inserted data place in body of email. , told because deleted , inserted contains multiple rows. please suggest solution "get row containing changed data".

im using sql server 2008r2

code:

      --de triggert-- alter trigger [dbo].[rvabestellingenaantalwijzigentrigger] on [dbo].[rva_bestellingen]   after update --vars declare @body varchar(500) declare @bestellingid int declare @categorieid int declare @subcategorieid int declare @aantalorigineel int declare @aantalnieuw int declare @locatienaam varchar(255) declare @componentnaam varchar(255) declare @categorienaam varchar(255) declare @subcategorienaam varchar(255) declare @datum datetime  if update(aantal) /*and (select datum inserted) = cast(floor(cast(dateadd(day,1,getdate()) float)) datetime)  */ ,   (convert(varchar,getdate(),108)>'11:00') begin      --zetten aantallen     set @aantalorigineel            = (select aantal deleted)     set @aantalnieuw                = (select aantal inserted)      set @bestellingid               = (select bestellingid inserted)     set @categorieid                = (select categorieid inserted)         set @subcategorieid             = (select subcategorieid inserted)       --zetten locatienaam en componentnaam     select @locatienaam = ('rva aanpassingen locatie: '+locatienaam), @componentnaam=officielenaam, @datum=datum     rva_bestellingen r     left outer join locaties l on l.locatieid = r.locatieid     left outer join componenten c on c.componentid = r.componentid     r.bestellingid = @bestellingid           select @categorienaam = categorie     rva_hoofdcategorie     hoofdcategorieid = @categorieid         select @subcategorienaam = categorie     dbo.rva_subcategorie     subcategorieid = @subcategorieid            --zet boyd     set @body = (                     select                          'hoofdcategorie: ' + @categorienaam+ char(10)+char(13)                         +'subcategorie: ' + @subcategorienaam+ char(10)+char(13)                         + 'componentnaam: '                         + @componentnaam + char(10)+char(13)                         + 'origineel aantal: '                         + cast(@aantalorigineel varchar(50) ) + char(10)+char(13)                         + 'nieuw aantal: '                         + cast(@aantalnieuw varchar(50) ) + char(10)+char(13)                         + 'leverdatum: ' +                         + convert(varchar(50),@datum,105)                                        )      --mailen naar adeline      exec master..xp_sendmail              @recipients = 'test@test.nl',              @message    = @body,              @subject    = @locatienaam end 

you understand triggers fire per batch , not per row right? in general bad idea. dump data table, write job checks table every minute , have send out email

you want trigger fast possible , not sending emails

see also: best practice: coding sql server triggers multi-row operations

your other option (which bad idea) looping on deleted , inserted tables in trigger , sending email each row

you should using sp_send_dbmail not xp_sendmail since on 2008, xp_sendmail deprecated

just fyi

something this

set @bestellingid               = (select bestellingid inserted) set @categorieid                = (select categorieid inserted)     set @subcategorieid             = (select subcategorieid inserted) 

can done 1 select

  select    @bestellingid = bestellingid,         @categorieid = categorieid,         @subcategorieid = subcategorieid  inserted 

no need execute 3 queries...this won't here since have code multi-rows show you can assign multiple variables 1 select