XML FeedsPlease 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
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
HAHAHA, wow, that is indeed awesome. It's like Reporting Services, but without the scalability. 
Would be a nice piece of code for an interview/exam.....how many problems do you see in this code? 
Almost makes you hope someone causes an update every second for a week. In fact, Brent has a good idea for the OP. Tell them to start up a SSRS subscription with sp_start_job in the trigger, generate the report to pdf, read the report in the trigger with MORE xp_ junk and then email that! Wow, I'm surprised I could even make that up 
I don't recall seeing it in person, but questions on how to do this come up from time to time in forums. Sometimes the OP will be convinced it's a bad idea, sometimes not. 
If you absolutely needed an email, it would be better to have the trigger code create a log entry on another table, and then have a job that runs every so often which checks for new records, sends a single email notifying of all changes since the last email, and then flags those log records as emailed. This way the data update operation could work instantly, you'd still get your emails and best of all, you have a complete log of all changes that isn't stored in your inbox. =) 
And you know what was the answer I've got after I dare to propose using log table and job approach?
Yes this is the work of the devil.
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.