Why make this change?

When there is a DML trigger configured on a table, we cannot use OUTPUT clause to return data. When an insert DML trigger is configured, OUTPUT clause cannot be used to return data because of the insert operation. When an update DML trigger is configured, OUTPUT clause cannot be used to return data because of the update operation. In such cases, we need to use a subsequent select statement to get the data after the mutation.

Details

What would handling of different kind of mutations look like when a trigger is enabled on a table?

1. DELETE: We don't need to return any data for deletions, so nothing will change for deletions.

2. UPDATE: For updates, we will cease to use the output clause. Instead, we will do a subsequent select query on the table to get the data. Since PK would already be provided to us we can easily do a select query. Not to mention, we can do this only when we determine that there is indeed a trigger enabled on the table. If not, we can continue with the same query that we generate in present day.

3. INSERT: When it comes to insertions, we can perform insertions on two kind of tables:

   1. Tables with non-auto generated primary keys: When we have tables with non-autogen PK, we can insert values of non-autogen PK fields into a temporary table (we might not know value of every PK before hand because PK can have a default value as well) and then perform an inner join for the subsequent select on the actual table and the temporary table based on the values of these PK fields.
   2. Tables with auto-gen primary keys: Tables with an autogenerated PK column fall into this category.. For tables with autogen PK, we would not be aware of the PK beforehand and so we cannot directly perform the subsequent select query. To get the autogen primary key, we can use SCOPE_IDENTITY() method provided by Sql Server.. Once we get the autogen PK, in addition to having an inner join for non-autogen PKs as discussed in the previous approach, we can add an additional WHERE predicate where autogen_PK_field = SCOPE_IDENTITY(). Let us discuss more about this approach and the concerns:

Delving deeper into Approach#3:
We can use OUTPUT clause coupled with INTO clause to store the values for the primary keys in a temporary table. And then do a subsequent select query on the table to get the data using those primary keys. In the temporary table, we insert only those columns from the PK which are not autogenerated. Why? Because we cannot insert a value for an autogenerated column even in a temporary table. Hence in the select query, we will add an additional WHERE predicate (alongwith the join) for the autogenerated column in the PK. It is to be noted that MsSql supports only one IDENTITY/autogenerated column per table.
We can choose to go with this approach only if we determine that there are any triggers enabled on the table.

Two assumptions which I am making here that I want to put ahead (btw, I don't think those are valid use cases) are:

1. What if primary key column has a datatype of timestamp? This concern arise because we cannot insert value for timestamp column (which is what the approach includes - inserting value via INTO clause). Well, rowversion/timestamp is intended for versioning and tracking changes within a table, rather than serving as a primary key. Also, by design, primary key values should not be modified after they have been assigned to a record. So having a PK with timestamp datatype does not seem to be a valid use case to me.

2. What if the trigger updates the values of the primary keys and the subsequent select query we are going to perform returns no data because the values of the PK has been changed in the table? This concern arises because technically, it is possible for triggers to update the value of PK columns. However, this is generally not recommended and can lead to undesirable consequences. Changing the value of a primary key can result in data integrity issues, as it may cause conflicts or break referential integrity constraints if other tables are referencing the primary key. It can also lead to confusion and difficulty in identifying records uniquely.

What is this change?

1. Added properties IsUpdateDMLTriggerEnabled and IsInsertDMLTriggerEnabled to SourceDefinition class which if true will indicate that there is a corresponding DML trigger enabled on the table.
2. Added method SqlMetadataProvider.PopulateTriggerMetadataForTable whose overridden implementation in MsSqlMetadataProvider will populate the properties mentioned in (1) with appropriate metadata values.
3. Refactored how queries are built in the SqlInsertStructure, SqlUpsertQueryStructure, SqlUpdateStructure so that when trigger is configured for the mutation operation, a subsequent select query is used instead of the OUTPUT clause to return data.
4. In case of insertions in table with auto-gen PK, we get the latest value of the auto-gen column using SCOPE_IDENTITY() function provided by sql server. Reference: https://learn.microsoft.com/en-us/sql/t-sql/functions/scope-identity-transact-sql?view=sql-server-ver16

How was this tested?

• Integration Tests - Added mutations tests to Rest/GQL performing inserts/updates/upserts on tables with insert/update DML trigger enabled. Tests are added for tables with autogen/non-autogen PK as the queries differ in the two cases.