none
RDA DB Sending an email from trigger

    问题

  • Greetings once again folks!

    I have successfully implemented RDA into my application, everything has been going well!

    I wanted to create a trigger that sends a mail when there is an "Insert/Select/Update" on any of the tables in the database.

    I set this up fine and when I do a select statement directly on the SQL server an e-mail is generated. When syncing however I get an error during the push.

    I read in the following post:

    http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/758a656c-5a72-478a-8053-19fb944a7c93 

    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'

    END

    So to cut a long story short. If you comment out the SP, RDA Works.

    If you don't RDA but make an insert, you get a mail.

    If you RDA with the trigger sending a mial, push fails.

    Any insight would be helpful.

    d3

    2012年3月2日 14:48

答案

  • Hi D347hm4n,

    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.


    Regards, Amber zhang

    2012年3月7日 6:41

全部回复

  • And you have set nocount on, and do not see any additional result sets from the sproc?

    Please mark as answer, if this was it. Visit my SQL Server Compact blog

    2012年3月3日 0:44
  • Yes I have set no count:

    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'

    END


    Is the full procedure.

    d3

    2012年3月5日 9:03
  • Hi D347hm4n,

    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.


    Regards, Amber zhang

    2012年3月7日 6:41