When an 'item is created' trigger for SQL in Logic App is used, how does the SQL connector query the change?
The SQL connector uses a polling mechanism to query a table for changes using a TIMESTAMP / ROWVERSION column. This data type is specifically designed for this kind of processing in SQL. The polling query essentially selects all rows where the rowversion is greater than the last polled value. The behavior is reliable since the column is controlled by SQL Server and the performance is extremely fast in the case where there is no new data. When there is new data, the performance is comparable to a simple row query.
Because we rely on this datatype and the behavior of the SQL Server implementation of rowversion, we do not allow setting OnUpdate triggers for tables that do not have a column of this data type.
[Text from a colleague working on the SQL connector, shared here with authorization for use by other customers and Cloud Solution Architects for their design of services and applications using Logic App with the SQL connector.]