Send email based on each specific field that is updated

Question

SQL Server 2017 Trigger on Table I have the following trigger running, and it works. However, I want to send a different email section based on specific fields that get updated. I basically want to use the SQL Server Send Mail code to send to a different user for a different field. I was wondering if there was some way to combine the statement? I hope I'm asking this correctly! My SQL is as the AT&T commercial...JUST OK! So if you can help please provide the code if this in fact can be done. For example:

Send/do this if firstname updated:

      EXEC msdb.dbo.sp_send_dbmail
      @profile_name = 'Echo System',
      @recipients = 'sample1.com',
      @subject = 'Send email for sample1',
      @body = 'Please start a background check'

Send/do this if last name updated

      EXEC msdb.dbo.sp_send_dbmail
      @profile_name = 'Echo System',
      @recipients = 'sample2.com',
      @subject = 'Send email for sample2',
      @body = 'Please start a background check'

Send/do this is nationality updated.

      EXEC msdb.dbo.sp_send_dbmail
      @profile_name = 'Echo System',
      @recipients = 'sample3.com',
      @subject = 'Send email for sample3',
      @body = 'Please start a background check'

ALTER trigger [dbo].[updatePerson] on
[dbo].[person]
for update
as
 
      declare @personId int;
      declare @firstname varchar(50);
      declare @lastname varchar(50);
      declare @nationality varchar(100);
      declare @activity varchar(100);
 
      select @personId = s.personId from inserted s;
      select @firstname = s.firstname from inserted s;
      select @lastname = s.lastname from inserted s;
      select @nationality = s.nationality from inserted s;
 
      if update(firstname)
                  set @activity = 'Updated person firstname'
      if update(lastname)
                  set @activity = 'Updated person lastname'
      if update(nationality)
                  set @activity = 'Updated person nationality'
 
      
 
      EXEC msdb.dbo.sp_send_dbmail
      @profile_name = 'Echo System',
      @recipients = 'sampletriggernamen@gmail.com',
      @subject = ' follow the sampleaction you need to take. http://test.aspx',
      @body = 'Please start a background check'

Answer 1

The following code shows how to handle multiple rows by looping through the Inserted pseudo-table. And how to detect changes in values.

As far as sending a different email per change type, you already had an IF statement to assign your @activity so just expand that to set the values to be emailed.

But as has been commented above, sending emails in a trigger is a very bad idea. Instead you should insert the data to be emailed into a queue table and then have another service processing the queue.

ALTER trigger [dbo].[updatePerson] on
[dbo].[person]
for update
as
begin
    set nocount on;
     
    declare @personId int, @firstname varchar(50), @lastname varchar(50), @nationality varchar(100), @activity varchar(100)
      , @firstNameModified bit, @lastNameModified bit, @nationalityModified bit
      , @profile_name sysname, @recipients varchar(max), @subject nvarchar(255), @body nvarchar(max);

    select
        I.personId
        , convert(bit, case when coalesce(I.firstname,'') <> coalesce(D.firstname,'') then 1 else 0 end) firstnameModified
        , I.firstname
        , convert(bit, case when coalesce(I.lastname,'') <> coalesce(D.lastname,'') then 1 else 0 end) lastnameModified
        , I.lastname
        , convert(bit, case when coalesce(I.nationality,'') <> coalesce(D.nationality,'') then 1 else 0 end) nationalityModified
        , I.nationality
    into #updatePerson_temp
    from Inserted I
    -- Because its an 'update' trigger we can inner join
    inner join Deleted D on D.personId = I.personId
    where coalesce(I.firstname,'') <> coalesce(D.firstname,'')
    or coalesce(I.lastname,'') <> coalesce(D.lastname,'')
    or coalesce(I.nationality,'') <> coalesce(D.nationality,'');

    while exists (select 1 from #updatePerson_temp) begin
        -- Get first record to handle
        select top 1 @personId = personId
            , @firstnameModified = firstnameModified
            , @firstname = firstname
            , @lastnameModified = firstnameModified
            , @lastname = lastname
            , @nationalityModified = nationalityModified
            , @nationality = nationality
        from #updatePerson_temp;

        -- Following assumes only one change per record, modify to suit
        if @firstnameModified = 1 begin
            select @activity = 'Updated person firstname'
                , @profile_name = 'Echo System'
                , @recipients = 'sample1.com'
                , @subject = 'Send Email for Sample1'
                , @body = 'Please start a background check';
        end; else if @lastnameModified = 1 begin
            select @activity = 'Updated person lastname'
                , @profile_name = 'Echo System'
                , @recipients = 'sample2.com'
                , @subject = 'Send Email for Sample2'
                , @body = 'Please start a background check';
        end; else if @nationalityModified = 1 begin
            select @activity = 'Updated person nationality'
                , @profile_name = 'Echo System'
                , @recipients = 'sample3.com'
                , @subject = 'Send Email for Sample2'
                , @body = 'Please start a background check';
        end;

        -- Instead of sending the email here, queue it for later
        exec msdb.dbo.sp_send_dbmail
            @profile_name = @profile_name
            , @recipients = @recipients
            , @subject = @subject
            , @body = @body;

        -- Delete handled record
        delete from #updatePerson_temp where personId = @personId;
    end;
end;