SQL – CASE WHEN on INSERT Only

By David Crowther

Question:

How do you use the values in one field to auto update values in a different field?

Answer:

In a previous FAQ we explore how to do this using a Database Trigger, which updates values in Field2 based on the values inserted or changed in Field1.

https://www.cadlinecommunity.co.uk/hc/en-us/articles/9857536785437-SQL-CASE-WHEN-Update 

However, that Trigger would be activated on any UPDATE to any RECORD in the table, and it would also then run the Update of values into ALL RECORDS in the table, not just on any newly inserted records.

Note – this can become problematic and slow if the source table has many records in it.

However, with this one line of SQL, we can get the Trigger to only run on newly inserted records.

This line should be inserted after the END part of the Update When list of values. And should use your primary key field name:

If we now start with a table with blank values in the Progress2 field for all current records….

…. and we now make a change to the Planning Apps table, for example using the webGIS MapThat, once the user creates a new record and chooses a value for the Progress field e.g. Passed….

… the new record will be inserted into the source table, and the trigger will auto update the Progress2 field with the value of ‘P’ based on the triggers CASE WHEN statement. But this time we can see that only the newly inserted record is updated. All current table records are not affected.

Triggers are a great way to auto update fields in your source tables. And in this case, we have now updated the trigger to only run the UPDATE on any newly inserted or updated records!