• Blog posts

    Bio

    By David Crowther

    SQL___CASE_WHEN_on_INSERT_Only_-_1.png

    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.

    SQL___CASE_WHEN_on_INSERT_Only_-_2.png

    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.

    SQL___CASE_WHEN_on_INSERT_Only_-_3.png

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

    SQL___CASE_WHEN_on_INSERT_Only_-_4.png

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

    SQL___CASE_WHEN_on_INSERT_Only_-_5.png

    …. 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….

    SQL___CASE_WHEN_on_INSERT_Only_-_6.png

    … 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.

    SQL___CASE_WHEN_on_INSERT_Only_-_7.png

    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!

Comments

0 comments

Please sign in to leave a comment.