Delay AFTER UPDATE execution until other updating processes are completed

Question

Please forgive me, I am fairly new to the art of crafting SQL Server triggers.

I've crafted a SQL Server trigger that will execute a PowerShell script (to send a JSON message to entity X) after a particular table has been updated. The script ran successfully as expected alone in DEV. However when instantiated as a trigger it caused an error on the Front End UI after the user submits an update. The users update did not post, and obviously did not instantiate the trigger.

I'm guessing it has something to do with table locks during the posting of the user input via the Web UI, but it's just a guess. Is there something I should consider in the trigger that would not interfere with the front end UI's controls process of updating the table first before my trigger runs?

This is my (rather primitive) trigger for everyone's perusal

USE [Hamburger_Chefs32];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

CREATE TRIGGER [dbo].[WD_SendIngredientsMessageOnScoreOverrideUPD] 
ON dbo.DeliciousBurgers
AFTER UPDATE
AS
BEGIN
    DECLARE @cmd sysname

    SET @cmd = 'powershell -File "E:\Program Files (x86)\TheWhopperCorporation\Burgers\v1.0.0\service\SendIngredients.ps1"'

    EXEC xp_cmdshell @cmd
END
GO

My humble thanks in advance for any help provided.

Update: Had a suggestion not to run the script from within the TRIGGER as it would have to wait for it to finish. Good point. Is there a way to simply execute the script without having to wait for a success (1), or fail (0) from the script? It runs perfectly 100% of the time, but I don't want to suffer a rollback of the UPDATE because of timing and/or dependency on the script.

Could you provide the error message and/or details of the exceptio thrown at the UI? Thanks. — Emilio Lucas Ceroleni, Jan 11 '18 at 03:18
You should never run an external script from a trigger, trigger is a part of the transaction, so first of all your transaction cannot commit until the script finishes, but if your script fails the WHOLE TRANSACTION will be rolled back. So your problem is not that "update does not work -> trigger does not fire", your update is done but it's rolled back because your script fails. You can see it using Profiler, or at least add try..catch and raiserror with some custom message to notify about the error — sepupic, Jan 11 '18 at 09:25
@sepupic The script works 100% of the time however.. Is there a way I can simply have the trigger fire off the script without the need to wait for it (or return flag) succeed or fail? — Peter Santiago, Jan 11 '18 at 18:24
>>>The script works 100% of the time however<<< You wrote that the script is launched by the trigger, the trigger does not fire because update does not work, but the script works every time, so how can it be? If script works this means that the trigger fires. And the trigger is after update, and this means that update was done — sepupic, Jan 11 '18 at 20:06

score 0 · Accepted Answer · answered Jan 12 '18 at 08:36

Change your trigger this way:

CREATE TRIGGER [dbo].[WD_SendIngredientsMessageOnScoreOverrideUPD] 
ON dbo.DeliciousBurgers
AFTER UPDATE
AS
BEGIN
    set xact_abort off; 
    begin try
       DECLARE @cmd sysname

       SET @cmd = 'powershell -File "E:\Program Files (x86)\TheWhopperCorporation\Burgers\v1.0.0\service\SendIngredients.ps1"'

       EXEC xp_cmdshell @cmd
   end try

    begin catch
       print ERROR_MESSAGE()
    end catch

END

This way you'll catch the error.

The most probable error here is

The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.

Unless the user that runs your app is sysadmin, or is granted explicitely this permission, the error will occur.

And the whole transaction is rolled back, that is why "The users update did not post".

Delay AFTER UPDATE execution until other updating processes are completed

1 Answers1