SQL Trigger Not Fully Executing When Table Update is Made via Budibase UI

[Wulkan93]

I have an MS SQL Server database with a trigger that updates multiple related tables when a record in the Object table is updated. The trigger works perfectly when I execute an UPDATE statement directly in SQL Server Management Studio (SSMS), but when the same update is performed via Budibase, only part of the trigger's logic executes.

Specifically, the Object and ResultView tables update as expected, but the final updates to the FactTable do not occur when the update is initiated through Budibase.

Trigger code:

CREATE TRIGGER trg_InsertResultViewOnObjectTypeUpdate
ON Object
AFTER UPDATE
AS
BEGIN

    UPDATE FactTable
    SET ObjectTypeID = i.ObjectTypeID
    FROM FactTable ft
    INNER JOIN INSERTED i ON ft.ObjectID = i.ID
    WHERE i.ObjectTypeID IS NOT NULL;

    INSERT INTO ResultView (
        ProjectID,
        ObjectID,
        ControlTypeID,
        ObjectTypeID
    )
    SELECT DISTINCT
        ft.ProjectID,
        ft.ObjectID,
        otc.ControlTypeID,
        ft.ObjectTypeID
    FROM INSERTED i
    INNER JOIN Object obj ON i.ID = obj.ID
    INNER JOIN ObjectTypeControl otc ON obj.ObjectTypeID = otc.ObjectTypeID
    INNER JOIN FactTable ft ON obj.ID = ft.ObjectID
    WHERE obj.ObjectTypeID IS NOT NULL
    AND NOT EXISTS (
        SELECT 1 
        FROM ResultView rv
        WHERE rv.ObjectID = ft.ObjectID
        AND rv.ControlTypeID = otc.ControlTypeID
    );

    UPDATE FactTable
    SET ResultViewID = rv.ID
    FROM FactTable ft
    INNER JOIN ResultView rv ON ft.ObjectID = rv.ObjectID
    WHERE ft.ResultViewID IS NULL;

END;
GO

Expected Behavior
When an update is made to the Object table, example:

UPDATE Object 
SET ObjectTypeID = 3 
WHERE ID = 2;

The trigger should:

• Update the FactTable with the new ObjectTypeID.
• Insert a new row into the ResultView table if a relevant entry does not exist.
• Update the FactTable with the correct ResultViewID.
  This process works perfectly when executed directly in MS SQL Server.

Actual Behavior in Budibase:
When the same update is performed via Budibase's UI:

• Object table updates correctly.
• ResultView gets updated.
• But any updates to FactTable do not occur.

Questions:

• Could Budibase be using a different transaction model that prevents the trigger from executing fully?
• Are there any known issues with SQL Server triggers when working with Budibase?

Any insights or suggestions would be greatly appreciated!

[Wulkan93]

To clarify. When doing the update through budibase interface, see picture below:

I updated the ObjectType-value in top right corner to "Security Camera" (ObjectTypeID-value "3").

The the SQL trigger activates, but only half of the function seem to happen. The ObjectID does not get through to the other tables below.
Here's a picture of the table for FactTable that shows that the values for ResultViewID and ObjectTypeID does not appear after this update:

But when I do a query-update directly to the database:

The values does appear in FactTable:

And the updates is visible in the budibase interface as well, note that Object now has a value in the lower table:

This is that same table in the database after the update has been done directly with the sql-query:

[Wulkan93]

I made a Query in budibase that I setup to a button in the UI to test if my SQL trigger activated.

Query:
UPDATE Object
SET ObjectTypeID = 3
WHERE ID = 2;

The SQL trigger did activate when I pushed the button.

My finding:
Updating the table 'Object' directly in a table-block in the budibase UI handles the UPDATE differently than a query does.

Question that I need answered:
How can I make it so that the UPDATE that I do in the table as earlier described activate the SQL trigger in the way that it does when I run a direct query?

Would very much appreciate any and all help/answers in this matter in order to crack the case :)

[Wulkan93]

I managed to solve this issue.