# Executing a table trigger

**URL:** <https://community.cdata.com/t/executing-a-table-trigger/755>\
**Category:** Editions\
**Tags:** article, odbc, ssms\
**Created:** [June 7, 2024, 9:41pm UTC](https://community.cdata.com/t/executing-a-table-trigger/755 "2024-06-07T21:41:22Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![Naomi\_Y](https://avatars.discourse-cdn.com/v4/letter/n/ea666f/32.png) [@Naomi\_Y](https://community.cdata.com/u/Naomi_Y)\
**Post date:** [June 7, 2024, 9:41pm UTC](https://community.cdata.com/t/executing-a-table-trigger/755/1 "2024-06-07T21:41:22Z")

</div>

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.

 ![bb707ecc-5274-427b-b7e8-491c7821be6b.png](https://us1.discourse-cdn.com/cdata/original/1X/14d65797126d91611f720b52d7ca2bf0250e9cfa.png)

 ![b1a40001-3116-4c27-a9ca-c991dc5e7ec6.png](https://us1.discourse-cdn.com/cdata/original/2X/b/bf8d5f380dac04e9cbbc59cb14fd2e33530acd26.png)

After doing so, you should be able to successfully call this trigger.