With SQL Gateway, we can expose our ODBC data sources as if they were MySQL or SQL Server databases. This allows us to create a Linked Server for any ODBC data source and work with our data as if it were a SQL Server instance, easily executing SQL statements to retrieve data.
In SQL, we can also create triggers. Triggers are associated with a table and allow us to specify SQL actions that are automatically executed when a certain event occurs on that table.
You can see an example of an UPDATE statement within the TRIGGER below. For context, the TRIGGER is associated with a SQL Server table (dbo.Test_Account_History) and whenever an UPDATE occurs on that table, an UPDATE call is triggered for one of our Linked Server tables (account__c). Therefore, if we UPDATE the attribute of newvalue within dbo.Test_Account_History, that will trigger an UPDATE for the Linked Server table of account__c.
ALTER TRIGGER [dbo].[trigger]
ON [dbo].[Test_Account_History]
AFTER UPDATE
AS BEGIN
SET NOCOUNT ON;
IF UPDATE(newvalue)
BEGIN
UPDATE [SFTRIGGER].[CData Salesforce Sys].[Salesforce].[account__c]
SET [Name] = 'TestTest1234'
WHERE [Id] = 'a0u8Z00000NeCoLQAV'
END
END
GO
When we execute an UPDATE statement on the dbo.Test_Account_History table, this results in an error, as the TRIGGER is unable to properly complete. We receive an error of “The transaction action 0 is not supported”. Since the TRIGGER is accessing the Linked Server, this requires the transaction to be upgraded to a distributed transaction (DTC), which is not supported with SQL Gateway.
A potential work around for this is to modify the trigger like below:
ALTER TRIGGER [dbo].[trigger]
ON [dbo].[Test_Account_History]
AFTER UPDATE
AS BEGIN
SET NOCOUNT ON;
IF UPDATE(newvalue)
EXECUTE ('UPDATE account__c SET [Name] = ''TestTest1234567'' WHERE Id= ''a0u8Z00000NeCoLQAV''') AT [SFTRIGGER]
END
As you can see, the UPDATE statement is being executed with the syntax of “EXECUTE(UPDATE statement) AT [LinkedServer]”.
You will also need to set “Enable Promotion of Distributed Transactions” to false. This property can be found by right-clicking on the Linked Server and heading to Properties > Server Options, as seen below.
After doing so, you should be able to successfully call this trigger.
