0

On a SQL Server database, I would like to run an external (Python) script whenever a new row is inserted into a table. The Python script needs to process the data from this row. Is a DML Trigger AFTER INSERT a save method to use here? I saw several warnings/discouragements in other questions (see, e.g., How to run a script on every insert or Trigger script when SQL Data changes). From what I understand so far, the script may fail when the INSERT is not yet commited because then the script cannot see/load the row? However, as I understand the example in https://www.mssqltips.com/sqlservertip/5909/sql-server-trigger-example/, during the execution of the trigger there exists a virtual table named inserted that holds the data being affected by the trigger execution. So technically, I should be able to pass the row that the Python script needs by retrieving it directly from this inserted table?

I am new to triggers which is why I am asking - so thank you for any clarifaction on best practices here! :)

fnjo
  • 43
  • 5

1 Answers1

0

After some testing, I found that the following trigger seems to successfully pass the row from the inserted table to an external Python (SQL Server Machine-Learning-Services) script:

CREATE TRIGGER index_new_row
ON dbo.triggertesttable
AFTER INSERT
AS

DECLARE @new_row nvarchar(max) = (SELECT * FROM inserted FOR JSON AUTO);
EXEC sp_execute_external_script  @language =N'Python',
@script=N'
import pandas as pd
OutputDataSet = pd.read_json(new_row)
',
@params = N'@new_row nvarchar(max)',
@new_row = @new_row
GO

When testing this with an insert on dbo.triggertesttable, this demo Python script works like a select statement on the inserted table, so it returns all rows that were inserted.

fnjo
  • 43
  • 5