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.

Answer 1

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".