Delete a sql table row using Azure SQL Trigger

Question

I am new to SQL Trigger, i am trying to delete a row from a table ,I have created below trigger using python script , it gives me below error

function.json

{
  "scriptFile": "__init__.py",
  "bindings": [

    {
      "authLevel": "anonymous",
      "type": "httpTrigger",
      "direction": "in",
      "name": "req",
      "methods": [
          "get"
      ]
  },
  {
    "type": "http",
    "direction": "out",
    "name": "$return"
},
  {
    "name": "deleteTrigger",
    "type": "sqlTrigger",
    "direction": "in",
    "tableName": "dbo.s_sample",
    "connectionStringSetting": "some text"
  }   
    
  ]
}

_init.py file

import pyodbc
import uuid
import os
import json
import logging

import azure.functions as func


def main(req: func.HttpRequest,deleteTrigger: func.SqlRowList) -> func.HttpResponse:
    logging.info('Python HTTP trigger function processed a request.')
#  Connect to the LabVantage database
labvantage_cnxn = pyodbc.connect(driver = '{SQL Server}', server='some text', database='some text')
labvantage_cursor = labvantage_cnxn.cursor()
print('connected to db')

#script to delete a record from s_sample table
s_sampleid = req.params.get('s_sampleid')


#list of current records
query1 = "SELECT TOP 10 s_sampleid,sampledesc,submitterid,submitteddt,notes,collectiondt,samplepointid,locationid,activeflag,samplesubtypeid,u_well,u_purpose,u_labcomments,u_materialtype,u_vendorid,u_condition,u_chevlegacyid,u_custcomments,u_samplehazards,u_depthunits,u_ageto,u_agefrom,u_currentsize,u_originalsize,u_ispressurized,u_formation,u_depthtop,u_depthbottom,u_containercomments from [dbo].[s_sample] order by createdt desc"
labvantage_cursor.execute(query1)


if not s_sampleid:
    try:
        req_body = req.get_json()
    except ValueError:
        pass
    else:
        s_sampleid = req.params.get('s_sampleid')

if s_sampleid:
    labvantage_cursor.execute("Delete from [dbo].[s_sample] where s_sampleid = ?",s_sampleid)
    labvantage_cursor.commit()
    logging.info('delete query executed')
     
#list of remaning records
query = "SELECT TOP 10 s_sampleid,sampledesc,submitterid,submitteddt,notes,collectiondt,samplepointid,locationid,activeflag,samplesubtypeid,u_well,u_purpose,u_labcomments,u_materialtype,u_vendorid,u_condition,u_chevlegacyid,u_custcomments,u_samplehazards,u_depthunits,u_ageto,u_agefrom,u_currentsize,u_originalsize,u_ispressurized,u_formation,u_depthtop,u_depthbottom,u_containercomments from [dbo].[s_sample]"
labvantage_cursor.execute(query)

# Get the column names from the cursor object
columns = [column[0] for column in labvantage_cursor.description]
print('Column names:')
print(columns)

# Fetch all of the rows from the cursor object
rows = labvantage_cursor.fetchall()
print('Row names:')
print(rows)

#creating dictionaries and removing stale data from the output file
json_object = dict()
rock_sample_record = dict()
open("C:\\Users\\rdkh\\Source\\IP_Sprint\\data.json", 'w').close()

# Loop through the rows
for row in rows:

    # Create a dictionary (object) for the row with the correct column names as keys
    rock_sample_record = dict(zip(columns, row))
    print(rock_sample_record)
    #to add the latest data to data.json file
    json_object = json.dumps(rock_sample_record,default=str)
    with open("C:\\Users\\rdkh\\Source\\IP_Sprint\\data.json", "a") as outfile:
        outfile.write(json_object)

print('Output file generated')    
   
    

I am getting below error "The 'HttpDeleteTrigger' function is in error: Multiple trigger bindings defined. A function can only have a single trigger binding." this is because i am defining 2 triggers in function.json. Could you please suggest how to use it correctly?

Answer 1

The explanation of error is correct, in a function you can only have a single trigger binding.

Since you want to create a trigger that responds to the deletion of a row in the dbo.s_sample table in Azure SQL db, you should remove the httpTrigger from your function.json file, as you only need the sqlTrigger.

Make sure that the "name" property of the sqlTrigger matches the parameter name in your Python script (deleteTrigger). This parameter will receive the changes from the SQL trigger.

Additionally, ensure that you adjust your Python script in the init.py file to handle the deleteTrigger parameter correctly, as you're currently using req.params.get('s_sampleid') to get the value for s_sampleid. The value should be retrieved from the deleteTrigger parameter, as it will contain the changes made to the table.

After removing the httpTrigger and updating your script accordingly, you should be able to properly respond to deletions in the dbo.s_sample table using an Azure SQL Database trigger.

Function.json bindings should look something like this:

  "bindings": [
    {
      "type": "http",
      "direction": "out",
      "name": "$return"
    },
    {
      "name": "deleteTrigger",
      "type": "sqlTrigger",
      "direction": "in",
      "tableName": "dbo.s_sample",
      "connectionStringSetting": "some text"
    }
  ]