Create a SQL Trigger for an On-Demand GP Agent Task

Here is an example of how to fire an “On-Demand” GP Agent task with a SQL trigger added to the Dynamics GP System database (usually DYNAMICS).

Requires GP Agent 14.0m48 or higher for GP 2015R2 or GP Agent 16.0m015 or higher for GP 2016.
create trigger dbo.gpAgentSampleTrigger
on ACTIVITY
for insert, delete
as

/*
This is an example of using having an On-Demand task run with GP Agent when the last user logs out of Dynamics GP. In this case, we want to run inventory reconcile as long as nobody is logged into the system. This trigger will tell GP Agent that it can run the On-Demand task “IVRECONCLE” as soon as the last user has exited GP. If a user logs into GP, this trigger will set the task to not run. Keep in mind that this is just an example, and you might want to have additional restrictions based on how long the task takes, because once the task has started, if a user logs into GP, the “IVRECONCILE” will continue to run until finished.  This trigger will fire the GP Agent Trigger WHENEVER all users are logged out, so you should consider adding restrictions either in this trigger code or on the Schedule for the GP Agent Task to limit when this will run.

THE SOFTWARE IS PROVIDED “AS IS”, WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

*/
declare @iRowCount int
/* if a user logs out and this is the last user to be deleted, we can run our task */
if exists(select USERID from deleted)
begin
select @iRowCount = (select count(*) from ACTIVITY)
if @iRowCount = 0
update TPSTSET set Task_Status = 10 where TASKID = ‘IVRECONCILE’
end
/* if a user logs in, make sure the task does not run */
if exists(select USERID from inserted)
begin
select @iRowCount = (select count(*) from ACTIVITY)
/* if this is the last user to be deleted, we can run our task */
if @iRowCount <> 0
update TPSTSET set Task_Status = 0 where TASKID = ‘IVRECONCILE’
end