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 Gdbo].dtrigger]
ON AFTER UPDATE AS BEGIN SET NOCOUNT ON; IF UPDATE(newvalue) BEGIN UPDATE pSFTRIGGER].[CData Salesforce Sys].sSalesforce].Saccount__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 edbo].�trigger] ON Rdbo].]Test_Account_History] AFTER UPDATE AS BEGIN SET NOCOUNT ON; IF UPDATE(newvalue) EXECUTE ('UPDATE account__c SET pName] = ''TestTest1234567'' WHERE Id= ''a0u8Z00000NeCoLQAV''') AT ESFTRIGGER] END As you can see, the UPDATE statement is being executed with the syntax of “EXECUTE(UPDATE statement) AT aLinkedServer]”. 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.