SQLite Forum

Switching data between two columns

When you perform an UPDATE you are really retrieving an "old" payload and then generating a "new" payload to replace it and writing the "new" payload.

So think of it as follows:

struct record { ... };
record old;
record new;
while (there is more to do):
 new = old;
 new.c3 = old.c4;
 new.c4 = old.c3;

References on the LHS of the **=** in SET refer to the new payload, and references on the RHS refer to the old payload.  Thus within an UPDATE trigger you have the "magical" tables OLD and NEW representing the OLD and NEW values respectively.

An INSERT is "half an update" there are no OLD values, only NEW values, hence only the NEW "magical" table is available to insert triggers.

Similarly a DELETE is "half an update" in that there is no NEW values, only OLD values, hence only the OLD "magical" table is available inside delete triggers.