That in a similar environment, he did the same thing and got the same error. So I setup my trigger to insert a new row into a new table, this table has a trigger enabled that executes the following stored procedure:

EXEC msdb.dbo.sp_send_dbmail

profile_name='SQL Admin',
@recipients='some@mail.com',
@subject='Changes',
@body='There has been an Insert/Delete/Update in the database table'

Answers

Regarding to the description, you could create a trigger for UPDATE, INSERT, DELETE that would send an e-mail to a user notifying them of the listed transactions using sp_send_dbmail. You can refer to David’s code in this
thread with the same scenario as yours.

Meanwhile it may be better to INSERT the info to an email queue table and schedule a stored procedure with SQL Server Agent to do the emailing based on the email queue table when you send email from trigger. For more information, please refer to
here.

If you followed the thread as you mentioned it should work. If that doesn't work, try using profiler and see what commands are actually being sent.

ALTER TRIGGER [dbo].[Email_Trigger]
ON [ehc_DurhamHIS].[dbo].[_Table_Changes]
AFTER INSERT,DELETE,UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for trigger here

EXEC msdb.dbo.sp_send_dbmail

profile_name='SQL Admin', @recipients='some@mail.com', @subject='Changes', @body='There has been an Insert/Delete/Update in the database table'

Regarding to the description, you could create a trigger for UPDATE, INSERT, DELETE that would send an e-mail to a user notifying them of the listed transactions using sp_send_dbmail. You can refer to David’s code in this
thread with the same scenario as yours.

Meanwhile it may be better to INSERT the info to an email queue table and schedule a stored procedure with SQL Server Agent to do the emailing based on the email queue table when you send email from trigger. For more information, please refer to
here.

If you followed the thread as you mentioned it should work. If that doesn't work, try using profiler and see what commands are actually being sent.