send an email when a column is updated

fwstealer
fwstealer used Ask the Experts™
on
i need to send an email when a column is updated. i started what i think is a good approach but not sure how to get the right data.


create trigger sendStatusChangeEmail
   on  Drives
   for update
as if update(StatusTypeID)
begin
      declare @profileName nvarchar(128)= 'ts'
      declare @To nvarchar(255) ='to'
      declare @BCC nvarchar(255) ='bcc'
      declare @From nvarchar(255) ='ts'
      declare @Subject nvarchar(128)= 'Drive closed'
      declare @driveId int
      declare @oldStatusTypeID int
      declare @newStatusTypeID int
      declare @Message varchar(2000)

      set @driveId = dbo.Drives.driveID
      set @oldStatusTypeID = dbo.Drives.StatusTypeID

      create table tempStatusType(id int  not null, oldstatusid int null, newstatusid int null) --create in memory
      
      insert tempStatusType(id, newstatusid) -- i need the oldstatusid value as well
      --select driveID, StatusTypeID from update
      --insert into tempStatusType (id, oldstatusid, newstatusid)
      --select @driveId = drvieID, @statusTypeID = u.StatusTypeID from update u
      --select @newStatusTypeID = StatusTypeID from inserted
      
 
      set @Message = 'Drive ID, ' + @driveId + ' was changed from ' + @oldStatusTypeID + ' to ' + @newStatusTypeID + ' and is now closed.'

      begin
            exec  msdb.dbo.sp_send_dbmail
            @profile_name = @profilename,
            @recipients = @To,
            @blind_copy_recipients = @BCC,
            @from_address = @From,
            @subject = @Subject,
            @body = @Message
      end

end

go
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
For updates.  You want to join the deleted and the inserted tables on the key values.  The deleted will show the old values and the inserted the new values.

As far as I am aware there is no 'updated' temporary table created for you...

You could, of course, access the tables seperately.  But as you specified that the trigger would only fire on update why not retrieve all data with one sql call.
I think this code would work for you


        insert into tempStatusType (id, d.StatusTypeID , i.StatusTypeID)
        select i.driveID, i.StatusTypeID , d.StatusTypeID
        from            inserted    i
             inner join deleted   d on i.driveID = d.driveID
Top Expert 2012

Commented:
While it is possible to use a TRIGGER for this purpose, it is a lousy idea that could prove disastrously non-scalable.

A better approach is to add the information to a "Queue" based on the INSERTED and DELETED tables that can then the regularly poled from a SQL Server Agent job and emails sent.  This could also have the added bonus of consolidating updates into a single email.
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

ac,

Are you suggesting that they initiate service broker ?  Thats a pretty advanced setup.

Or by queue, did you just mean a table that you would be using as an ad-hoc queue?

P.S.  While your advice is on-target, your bedside manner was a little harsh this time.  The initial problem was just in how to use the inserted and deleted data accessible in the triggers.  As soon as they tested it, they would see how many e-mails were generated...  Personally , I am not a fan of generating e-mails at all.  I find that people start ignoring e-mails that come automatically.  i find it much better to collect the data an run reports when I have the time to actually analize the data. And I usually do it accross all the server I am managing for the same types of situations. I find that keeping myself in the same frame of mind makes my job go much easier rather than changing tack every three seconds.  But , alas, it does not generate as much frantic looking activity.... So maybe I SHOULD go back to the e-mail chasing management philosophy...
Top Expert 2012

Commented:
Are you suggesting that they initiate service broker ?
Not at all.

Or by queue, did you just mean a table that you would be using as an ad-hoc queue?
Yes, that was what I meant.  I should have been clearer.

While your advice is on-target, your bedside manner was a little harsh this time.
Only this time?  But seriously, I understand and I trust you know it was not directed at you. I realized you were not advocating that approach, but merely solving a problem. We are all adults here and if we are in this profession we should be prepared to take criticism and if we do not agree defend our point of view.

Of course emails are abused, however in this case it is the TRIGGER that is being abused.  TRIGGERs were not intended for that, and this is/was clearly explained in SQL Server's BOL.  If used, they should be a "light-weight object", anything else and you are asking for trouble.  It is precisely this type of abuse that gives TRIGGERs (and in consequence SQL Server) a bad name and why I am so pationate about it.

I don't expect or wish to get any thanks for my advice, but if a future reader sees this and thinks twice before using a TRIGGER in this way, than I have achieved my goal.

Author

Commented:
SJCFL-Admin - like the humor

I really wasn't for the trigger approach to kick off emails as this funcationality should have been addressed in the application as the user clicks a checkbox to change the statustype. but I was instructed to write a trigger to monitor the field state. so yes, the trigger is the abuser and isn't a good approach.

anyways - so is this the right approach? I'm new to the trigger to send email thingy.


set ansi_nulls on
go
set quoted_identifier on
go

create trigger sendStatusChangeEmail
   on  Drives
   for update
as if update(StatusTypeID)
begin
      declare @profileName nvarchar(128)= 'ts'
      declare @To nvarchar(255) ='to'
      declare @BCC nvarchar(255) ='bcc'
      declare @From nvarchar(255) ='ts'
      declare @Subject nvarchar(128)= 'Drive closed'
      declare @driveId int
      declare @oldStatusTypeID int
      declare @newStatusTypeID int
      declare @Message varchar(2000)

      --set @driveId = dbo.Drives.driveID
      --set @oldStatusTypeID = dbo.Drives.StatusTypeID

      create table tempStatusType(id int  not null, oldstatusid int null, newstatusid int null) --create in memory
     
      insert into tempStatusType (id, d.StatusTypeID , i.StatusTypeID)
      select i.driveID, i.StatusTypeID , d.StatusTypeID
      from inserted i
      inner join deleted   d on i.driveID = d.driveID

      --insert tempStatusType(id, newstatusid) -- i need the oldstatusid value as well
      --select driveID, StatusTypeID from update
      --insert into tempStatusType (id, oldstatusid, newstatusid)
      --select @driveId = drvieID, @statusTypeID = u.StatusTypeID from update u
      --select @newStatusTypeID = StatusTypeID from inserted
     
      set @Message = 'Drive ID, ' + @driveId + ' was changed from ' + @oldStatusTypeID + ' to ' + @newStatusTypeID + ' and is now closed.'

      begin
            exec  msdb.dbo.sp_send_dbmail
            @profile_name = @profilename,
            @recipients = @To,
            @blind_copy_recipients = @BCC,
            @from_address = @From,
            @subject = @Subject,
            @body = @Message
      end

end

go
Heres the thing.  

If multiple rows get change, the inserted and deleted tables will contain multiple rows.  So your logic to write the e-mails would need to loop through the multiples.  

If , however, you know the applciation can only change one at a time then the logic is close to working.  But in this case you would not need to have the temporary table. I think it would work if you just selected the values into your temporary fields..  Like the following:

set ansi_nulls on
go
set quoted_identifier on
go

create trigger sendStatusChangeEmail
   on  Drives
   for update
as if update(StatusTypeID)
begin
      declare @profileName nvarchar(128)= 'ts'
      declare @To nvarchar(255) ='to'
      declare @BCC nvarchar(255) ='bcc'
      declare @From nvarchar(255) ='ts'
      declare @Subject nvarchar(128)= 'Drive closed'
      declare @driveId int
      declare @oldStatusTypeID int
      declare @newStatusTypeID int
      declare @Message varchar(2000)

      --set @driveId = dbo.Drives.driveID
      --set @oldStatusTypeID = dbo.Drives.StatusTypeID

----  the need for these go away if you guarantee only one row gets returned each time
----      create table tempStatusType(id int  not null, oldstatusid int null, newstatusid int null) --create in memory
----      insert into tempStatusType (id, d.StatusTypeID , i.StatusTypeID)
      select @driveId=i.driveID, @newStatusTypeID=i.StatusTypeID , @oldStatusTypeID=d.StatusTypeID
      from inserted i
      inner join deleted   d on i.driveID = d.driveID

      --insert tempStatusType(id, newstatusid) -- i need the oldstatusid value as well
      --select driveID, StatusTypeID from update
      --insert into tempStatusType (id, oldstatusid, newstatusid)
      --select @driveId = drvieID, @statusTypeID = u.StatusTypeID from update u
      --select @newStatusTypeID = StatusTypeID from inserted
     
      set @Message = 'Drive ID, ' + @driveId + ' was changed from ' + @oldStatusTypeID + ' to ' + @newStatusTypeID + ' and is now closed.'

      begin
            exec  msdb.dbo.sp_send_dbmail
            @profile_name = @profilename,
            @recipients = @To,
            @blind_copy_recipients = @BCC,
            @from_address = @From,
            @subject = @Subject,
            @body = @Message
      end

end

go

Author

Commented:
yes - sure that rows will be changing on the fly all the time
Now the bad news.  I do agree with ac.  And you will also after this has run for a while and people start complaining about too many e-mails.  

And your manager forgets that this is exactly the way he asked for it and instead says why didn't you do it differently.  

Plus , if someone decided to skip the application to change it and uses native sql to modify a whole group at a time, they will fail if you set the trigger up to handle only one at a time.  

But you do not really want cursor processing in a trigger when its not necessary...  Too high an overhead if the functionality is not being used normally...

So you see, really what I objected too about the comment from ac was that he was so harsh in his phrasing without explaining why.  We have all been burnt on these types of things and just try at all costs to avoid it.

If you set up a second table that contains the message you would put out, you would not have to worry about single or multiple changes. or wether the mail system were down to stop users from making the changes....  

All you need then is a simple sql job to pull from the message queue and send out the messages.  If it were me, I would add the identity field as the first attribute in the table and probably the store the name of the trigger and maybe a message code.  That way you ahve the message table set up to distribute e-mail messages from all your future triggers or processes.  and as decisions change, you can modify the timing and or technique of message distribution without affecting the trigger.... You could initially set the job up to do a select top 1 and handle one at a time and have the job run frequently until you have time to get the looping down pat....

and all this was what I think ac meant. but it takes a lot of time and also being burnt to really appreciate the alternate technique when you are first starting.  So if you need to do it one at a time for now. Do so, but rember ac when you change it later.  And thank him for putting the thought in you mind,so you know what you will change it to later :-)

insert into PermanentMessageTable ( profile_name,
            recipients ,
            blind_copy_recipients,
            from_address ,
            subject,
            @body )
select   @profile_name,
            @To ,
            @BCC,
            @From ,
            @Subject,
            'Drive ID, ' + @driveId + ' was changed from ' + @oldStatusTypeID + ' to ' + @newStatusTypeID + ' and is now closed.'

                  from inserted i
      inner join deleted   d on i.driveID = d.driveID

Author

Commented:
getting more confused so I tried the following and you'll see where the row being updated is not reflected in the temp table

--create table Drives(id int identity not null primary key, StatusTypeID int null)
--go  
--create table tempDrives(id int  not null,  StatusTypeID int null, oldStatusTypeID int null)
--go


--create trigger trInsertCopy
--on Drives
--for insert
--as
--if @@ROWCOUNT =0
--return
-- insert tempDrives (id,StatusTypeID)
-- select id, StatusTypeID
-- from inserted
--go
 
 
--insert Drives(StatusTypeID) values(45)
--insert Drives(StatusTypeID) values(45)


--select id, StatusTypeID from dbo.Drives
--select id, StatusTypeID from dbo.tempDrives


--create trigger trUpdateStatusTypeID
--on Drives
--for update
--as
--if UPDATE(StatusTypeID)
--begin
--      declare @Id int
--      declare @oldStatusTypeID int
--      declare @newStatusTypeID int
--      select @Id=i.id, @newStatusTypeID=i.StatusTypeID , @oldStatusTypeID=d.StatusTypeID
--      from inserted i
--      inner join deleted   d on i.id = d.id      
--      begin
--      print 'hi'
--      end
--end
--go


--select id, StatusTypeID from dbo.Drives
--select id, StatusTypeID, oldStatusTypeID from dbo.tempDrives


--update Drives set [StatusTypeID] = 44 where StatusTypeID = 45 -- prints hi and 2 rows affected


--select id, StatusTypeID from dbo.Drives -- StatusTypeID = 44
--select id, StatusTypeID, oldStatusTypeID from dbo.tempDrives --StatusTypeID = 45 not to 44 and oldstatustypeid = null
--so the update trigger is not working - not updating the data
--create trigger trUpdateStatusTypeID
--on Drives
--for update
--as
--if UPDATE(StatusTypeID)
--begin
--      declare @Id int
--      declare @oldStatusTypeID int
--      declare @newStatusTypeID int
==================================================================
Insert into tempdrives (id ,  StatusTypeID , oldStatusTypeID )

=================================================================
--      select @Id=i.id, @newStatusTypeID=i.StatusTypeID , @oldStatusTypeID=d.StatusTypeID
--      from inserted i
--      inner join deleted   d on i.id = d.id      
--      begin
--      print 'hi'
--      end
--end
--go

Author

Commented:
might be onto something - granted i didn't read your post but think this might be something but need to figure out how to loop and I'm not using the tempDrives table.


--create table Drives(id int identity not null primary key, StatusTypeID int null)
--go  
 
 
--insert Drives(StatusTypeID) values(45)
--insert Drives(StatusTypeID) values(45)


--select id, StatusTypeID from dbo.Drives


--create trigger [dbo].[trUpdateStatusTypeID]
--on [dbo].[Drives]
--for update
--as
--if UPDATE(StatusTypeID)
--if(select COUNT(*) from inserted) = 1
--begin
--      declare @Id int
--      declare @oldStatusTypeID int
--      declare @newStatusTypeID int
      
--      select @Id = Id, @oldStatusTypeID=d.StatusTypeID from deleted d
      
--      select @newStatusTypeID = StatusTypeID from inserted
     
--              if @newStatusTypeID = 44
--                  begin
--                        print 'changed one row in the Drives Table with value of 44 so send out 1 email'
--                  end
--              else
--                  print 'not sending email as value isnt 44'
--end
--else
--print 'multiple rows were changed so need to loop and send out individual emails - how to loop??'
--go


--select id, StatusTypeID from dbo.Drives




--update Drives set [StatusTypeID] = 44 where id = 1
--update Drives set [StatusTypeID] = 45 where id = 1
--update Drives set [StatusTypeID] = 38 where id = 1
If you know multiples were changed at one go, are you sure you do not want to just send out one e-mail listing the changes as detail lines in the message?

Author

Commented:
i thought about that but there is a new requirement to send out a single email when a row changes - why i suggested making this a feature in the app cause the sql monitoring event doesn't have a clue who changed what but in the app it knows the userid.
Hmmm...  If this is information known by the app at at point in time that is important enough to be written to an e-mail for error tracking, than someone down the line is going to be wanting to backtrack it (my opinion)

Personally, I would want the information in the same place I was storing the new status toknow who set that new status.  because somewhere down the line if there are consisitant problems, someone is going to ask if they are coming from the same source...  Or a phantom source...

Is the design so locked that this attribute can't be easily added?
Sorry, I digressed...

Looping is done with a While construct...

am working on a sample now...
I ended up liking the straightforward cursor logic best for your looping process through the multiple delete rows...


DECLARE DeletedCursor CURSOR
    FOR SELECT Id, @StatusTypeID
 FROM Deleted;
 
OPEN DeletedCursor  

FETCH NEXT FROM DeletedCursor
 into @Id , @oldStatusTypeID

WHILE @@FETCH_STATUS = 0  
  BEGIN    

     ....  perform e-mail logic here ....

     FETCH NEXT FROM DeletedCursor into @Id , @oldStatusTypeID    
  END  
  CLOSE DeletedCursor
  DEALLOCATE DeletedCursor
The above was as straight forward pass through the deleted cursor.  You can do a select against the inserted to get the new value that matches based on the drive.  Or you could try to match them in a single joined call by changing the cursor to

     select i.id, i.StatusTypeID , d.StatusTypeID
      from inserted i
      inner join deleted   d on i.id = d.id  

and the FETCH INTO  (2 places ) to

     INTO  @Id, @newStatusTypeID , @oldStatusTypeID

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial