ContextThe triggers in five database connectors on Workato have been updated to help customers better understand the requirements for database triggers to work properly.
Before the change
All columns in the database tables were allowed as the Unique key and Sort column fields. This caused confusion for some users as some fields and field types are not suitable for these columns. To prevent any ambiguity, we have made this change so the mapping experience with database triggers is simpler and smoother!
The update will only affect the picklists in the trigger of the database connectors of all new or updated recipes. These picklists will now only allow a subset of columns to be chosen in the Unique key and Sort column fields based on the datatype of the column.
Please note that existing recipes or running recipes will continue to work as expected. If you are using any of the database connectors, please read on for more information.
The five connectors are:
- SQL server
Changes to the picklists
The Unique key and Sort column fields are required fields in the new row and new/updated row triggers. They are shown in the Workato UI as picklists, where users can choose the columns they wish to use, as below:
The Unique key picklist will now show a subset of your table's columns — columns that are Primary keys of the table or have a Unique datatype. These fields should be automatically incremental and sortable.
Here are the accepted datatypes for each app, you may click on the app names for more information available in our documentation.
- Redshift: Primary key or Unique column
- PostgreSQL: Primary key or Unique column
- MySQL: Primary key or Unique column
- Oracle: Primary key, Unique column or Identity column
- SQL server: Primary key or Unique column
Here are the accepted datatypes for the sort column field in each app. Note that sort column fields will only appear when you use a new/updated row trigger. Click on the app names for more information available in our documentation.
- Redshift: Timestamp, timestamptz
- PostgreSQL: Timestamp, Timestamp with time zone
- MySQL: Datetime, Timestamp
- Oracle: Timestamp, Timestamp with time zone, Timestamp with local time zone
- SQL Server: Datetime2 only
What if I have running recipes with the affected triggers?
Existing recipes or running recipes will continue to work as expected, no matter the type of columns you have selected as your Unique key or Sort column. The change is completely backward compatible. However, if you face any issues with your database triggers as a result of these changes, please do not hesitate to submit a ticket for one of our support representatives to assist you.
What if I want to edit an existing recipe?
If you edit an existing recipe that has an incorrect column data type, please note that you will not be able to re-select that column if you edit the two affected fields. They will continue to work when you run the recipe if you do not edit them. Once you click on either of the picklists, only the columns with acceptable datatypes will be shown.
If you would like to continue using the fields you have previously chosen, please edit the column type in the database to one of the accepted types.
What if I want to clone an existing recipe?
If you clone an existing recipe with the columns that have incorrect data types, they will be cloned successfully and continue to work as expected with the fields you previously selected. However, if you click on the picklists after cloning, the columns with incorrect data types will no longer be available for selection.