Please leave me a comment if you have written or have seen a trigger that is written in such a way that it will send an email when a value changes in a table.

I am looking at the following question: Email trigger when data is changed

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) DECLARE @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 = 'fake@fake.fake', @message = @body, @subject = @LocatieNaam end

And I am just shaking my head, for one it doesn’t take into account muliple rows being updated, see Best Practice: Coding SQL Server triggers for multi-row operations for more on that topic

The second bad thing is of course that you want your trigger to complete as fast as possible, you don’t want it to email a bunch of people. What if the email blows up?

Ideally you would write a query inside the trigger that dumps the desired results into another table, then you would have a job that checks that table every minute or so and does the emailing.

Now I admit, I have written a trigger in the past that emailed when something got inserted, this was a table that would interact with Great Pains Plains. Once we decided to insert nine thousand rows as a stress test from a batch, and yes we brought down the whole exchange server, the email went out to I believe 20 people, this was the time of 3 MB inboxes ðŸ™‚

Lastly this person is on SQL Server 2008 and is still using xp_sendmail and not sp_send_dbmail

Enough ranting, leave me a comment if you do send emails from withing triggers. If you do so, did you ever have any problems with it?

*** If you have a SQL related question try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum