SQLite Forum

Serious problems with STORED columns
Login
The `x as (expression) stored` is syntactic sugar.  One can replace the following:

```
create table y
(
  x real not null,
  y as (BloodyLongCalcualtion(x)) STORED
);
```

with

```
create table y
(
  x real not null,
  y
);
create trigger y_y_disallow_insert before insert on y
begin
 select raise(ABORT, 'Cannot insert into computed column y')
  where y.y is not null;
end;
create trigger y_y_recompute_insert after insert on y
begin
  update y
     set y = BloodyLongCalculation(new.x)
   where rowid == new.rowid;
end;
create trigger y_y_disallow_update before update of y on y
begin
  select raise(ABORT, 'Cannot update computed column y')
    when old.x IS new.x
     and old.y IS NOT new.y;
end;
create trigger y_y_update after update of x on y
begin
  update y
     set y = BloodyLongCalculation(new.x)
   where old.x IS NOT new.x
     and rowid == new.rowid;
end;
```

As you can see, using a computed column is a crapload less typing to achieve the same ends.  About the only difference is that the computed value must be deterministic whereas the trigger does not have to be.