Avatar of loneieagle2
Flag for United States of America

asked on 

AFTER UPDATE TRIGGER is causing the save operation to fail.

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]
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.

    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',
Microsoft SQL ServerSQL* trigger

Avatar of undefined
Last Comment

8/22/2022 - Mon