SQL – CASE WHEN on Multiple Fields

By David Crowther

Question:

How do you use the values in existing fields to auto update values in several different fields?

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, you may wish to run more than one field update, so should you create multiple triggers or can this be done in one trigger?

While you can have multiple triggers against one table, we would always suggest where possible limiting the number of triggers and running multiple updates inside one trigger – as this can save time and issues with triggers possibly clashing with each other.

In our Planning Apps table, we are now going to auto update fields Extra1 and Extra2 based on the values that the user enters in the Requester and Place field.

So, let’s update the original trigger that updated our one field (Progress2), by adding 2 more update statements in.

In SQL, choose to Modify your Trigger and then in the SQL window simply copy the first UPDATE WHEN lines and paste them twice below, then:

• Change the field names
• Change the values to Search for (Extra1, Extra2)
• Change the values to Update to (Requestor, Place)

Note – in the above SQL we have also added an extra line to only update the record being inserted. We explored this in another FAQ.

Having edited the trigger details simply run the trigger again to Alter the trigger.

If we now insert a new record into the Planning Apps table, for example using the webGIS MapThat, once the user creates a new record and chooses a value for the Requester and Place field….

… the new record will be inserted into the source table, and the trigger will auto update the Progress2, Requestor and Place fields based on the triggers CASE WHEN statements.

Our one Trigger now updates 3 fields in our source table!