troubleshooting Question

AFTER UPDATE TRIGGER is causing the save operation to fail.

Avatar of loneieagle2
loneieagle2Flag for United States of America asked on
Microsoft SQL ServerSQL* trigger
7 Comments1 Solution75 ViewsLast Modified:
I am trying to set up a trigger to send an Email when a field is updated. I did it very simply just to test my Email setup. The trigger works if I make the change in the record, but if another employee makes the change, they get an error that a save operation on Mytable failed. If I disable the trigger the second employee is able to make the change with no problem. Where can I look to see why it is failing? The SQL agent logs show nothing happening at this time.

This is SQL 2016.

Here is my trigger:

CREATE TRIGGER [dbo].[Change_Patient]
   ON  [dbo].[mytable]
   AFTER UPDATE
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

    declare @OldAddress varchar(50)
      declare @NewAddress varchar(50)
      declare @Email varchar(500)
      declare @EBody varchar(max)

      select @OldAddress = DELETED.Street_Name from deleted
      select @NewAddress = inserted.Street_Name from inserted
      --select @OldAddress='OLD'
      --select @NewAddress='New'
      select @Email = 'emp1@mycompany.com; emp2@mycompany.com'
      select @Ebody = 'The field Address was changed from ' + @OldAddress + ' to ' + @NewAddress
      
      EXEC msdb.dbo.sp_send_dbmail @profile_name= 'SQL Mail Profile',
      @recipients = @Email,
      @subject = 'Testing Trigger',
      @body=@Ebody
END
GO
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 7 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros