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]
-- 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 @Email = 'email@example.com; firstname.lastname@example.org'
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',