Email trigger when data is changed

i created a trigger that send an email when field Aantal is changed but the problem is that it won't get the deleted and inserted data to place in the body of the email. And i was told that is because deleted and inserted contains multiple rows. please suggest solution to "get the row containing the changed data".

im using SQL Server 2008R2

CODE:

      --DE TRIGGERT--
ALTER TRIGGER [dbo].[RVABestellingenAantalWijzigenTrigger]
ON [dbo].[RVA_Bestellingen]  
AFTER UPDATE
AS
--Vars
DECLARE @body varchar(500)
DECLARE @BestellingID int
DECLARE @CategorieID int
DECLARE @SubCategorieID int
DECLARE @AantalOrigineel int
DECLARE @AantalNieuw int
DECLARE @LocatieNaam varchar(255)
DECLA开发者_开发技巧RE @ComponentNaam varchar(255)
DECLARE @CategorieNaam varchar(255)
DECLARE @SubCategorieNaam varchar(255)
DECLARE @Datum datetime

if update(Aantal) /*and (SELECT Datum FROM inserted) = cast(floor(cast(dateadd(day,1,getdate()) as float)) as datetime)  */ and   (convert(varchar,getdate(),108)>'11:00')
begin

    --Zetten aantallen
    SET @AantalOrigineel            = (SELECT Aantal FROM deleted)
    SET @AantalNieuw                = (SELECT Aantal FROM inserted) 
    SET @BestellingID               = (SELECT BestellingID FROM inserted)
    SET @CategorieID                = (SELECT CategorieID FROM inserted)    
    SET @SubCategorieID             = (SELECT SubCategorieID FROM inserted) 

    --Zetten locatienaam en componentnaam
    SELECT @LocatieNaam = ('RVA Aanpassingen Locatie: '+LocatieNaam), @ComponentNaam=OfficieleNaam, @Datum=Datum
    FROM RVA_Bestellingen r
    LEFT OUTER JOIN Locaties l on l.LocatieID = r.LocatieID
    LEFT OUTER JOIN Componenten c on c.ComponentID = r.ComponentID
    WHERE r.BestellingID = @BestellingID    


    SELECT @CategorieNaam = Categorie
    FROM RVA_HoofdCategorie
    WHERE HoofdCategorieID = @CategorieID   

    SELECT @SubCategorieNaam = Categorie
    FROM dbo.RVA_SubCategorie
    WHERE 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 as varchar(50) ) + char(10)+char(13)
                        + 'Nieuw aantal: '
                        + CAST(@AantalNieuw as 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 that triggers fire per batch and not per row right? This is in general a bad idea. dump the data into another table, write a job that checks that table every minute and have that send out an email

You want the trigger to be as fast as possible and not be sending emails

See also: Best Practice: Coding SQL Server triggers for multi-row operations

Your other option (which is also a bad idea) is looping over the deleted and inserted tables in the trigger and sending an email for each row

you should also be using sp_send_dbmail not xp_sendmail since you are on 2008, xp_sendmail is deprecated

Just as an FYI

something like this

SET @BestellingID               = (SELECT BestellingID FROM inserted)
SET @CategorieID                = (SELECT CategorieID FROM inserted)    
SET @SubCategorieID             = (SELECT SubCategorieID FROM inserted)

can be done with 1 select

  SELECT    @BestellingID = BestellingID,
        @CategorieID = CategorieID,
        @SubCategorieID = SubCategorieID 
FROM inserted

No need to execute 3 queries...this won't help you here since you have to code for multi-rows but is just to show you that you can assign multiple variables with 1 select

There's no way to get only the changed data as you ask, since you can have multiple rows modified.

For instance, if you execute the following:

UPDATE MyTable
SET Col2 = Col2 + 1

Every row will have Col2 increased by one. Which row would you want for your example?

You can use a TOP 1 clause in your selects that assign the values to the variables at the top, but the only way to do what you want is to use a WHILE loop or a cursor to send an email per row, which will be a LOT of emails potentially.

Also this is a pretty poor practice in general as all your transactions will be holding on this email procedure to fire for every update/delete/insert.