Send an email when a Geoform adds a new record

[SharonZastre]

I have created a Geoform on ArcGIS Online based on a feature service from data stored in a versioned SQL database.  Ideally, I would like to have an email sent when the user clicks on the "Submit" button, with or without the geoform details.  There seems to be many requests online for this functionality but it is not implemented yet.  (Side question...any plans for this to be implemented).  So, in the meantime, I'm trying to find a work around that does not involve the GeoEvent Processor extension.  I have tried to create a Trigger on the SQL database but the sp_send_dbmail procedure seems to only work on the msdb system database, not user defined databases.  I also tried to enable Editor Tracking on the feature class in the GIS but I don't see a way to automatically send an email when a new record is added/inserted.

Am I completely overlooking something?  Any other options out there that do not involve GeoEvent Processor or hosting the geoform app locally?  Thanks for any suggestions.

Sharon

[ThomasColson]

You are correct that send mail SP's only work out of the master DB. However, you can put your trigger on the user database, and when some event occurs (e.g. add a new record), it will call the SP in the master DB. One problem you may be having is one of permissions, the service account (PTL? AGSVR?) needs to have permission to "send mail". The following, which performs a test insert every hour and lets me know if it fails, is not really related to a trigger, but if you reverse engineer it, you can achieve the desired result.

BEGIN TRY
DECLARE @id as integer
EXEC dbo.next_rowid 'dbo', 'WILD_BEAR_MON_PT', @id OUTPUT;
SET QUOTED_IDENTIFIER ON
INSERT INTO [dbo].[WILD_BEAR_MON_PT]
(OBJECTID,
LOC_NAME,
shape)
VALUES(@id, 'ZZZ_HEALTH_CHECK_TEST_DELETE', geography::STPointFromText('POINT(-83.499752 35.687597)', 4269))
DELETE FROM WILD_BEAR_MON_PT WHERE LOC_NAME = 'ZZZ_HEALTH_CHECK_TEST_DELETE'
END TRY
BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;


    SELECT
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();
    DECLARE @ErrorText NVARCHAR(4000);
  DECLARE @ErrorDisclaimer NVARCHAR(4000);
  SET @ErrorText = 'Dammit Jim! The Engines can''t take this! Somethings wrong with the Bears Database! '
  SET @ErrorDisclaimer = 'The Bears Database is having some sort of problem. Editing is disabled and read-access may not be working.  '
  SET @ErrorMessage = @ErrorText + @ErrorMessage + @ErrorDisclaimer


EXEC msdb.dbo.sp_send_dbmail
    @profile_name='Captain Kirk',
    @recipients='sasquatch@bigfoot.com',
    @subject='Error in the Bears Database',
    @body = @ErrorMessage;
END CATCH;