Using Stream Analytics with SQL Triggers

Question

I'm attempting to move data from a source into SQL Server using Azure Stream Analytics. I want to execute a stored proc to get the data into SQL Server properly, so I've implemented a trigger that executes my stored procs on the SQL target table.

However, Stream Analytics seems to be using a BULK INSERT into my SQL Server table, so some of my inserts are failing to execute the trigger because the Stream Analytics BULK INSERT seems to be missing the FIRE_TRIGGERS option.

Is there a way to get data into SQL Server using Azure Stream Analytics using a stored proc?

Answer 1

Stream Analytics always specifies FIRE_TRIGGERS and KEEP_IDENTITY options during SqlBulkCopy.

There are few users already using INSTEAD OF triggers. Here is a quick sample that might work --

CREATE TRIGGER myTrigger ON mySqlTable
INSTEAD OF INSERT
AS
    BEGIN
        SET NOCOUNT ON

        INSERT INTO mySqlTable
            SELECT inserted.col1 FROM inserted  -- trigger logic here

    END
GO