On 3/5/2015 9:56 PM, Scott Robison wrote:
> Finally, my question: Is there some sort of syntax that I'm missing that
> would "simplify" my schema with a single update trigger, or is this the
> proper way to "update" individual columns of a view?
Imagine that the "clean" table has null in column X of row R, and the
"dirty" table has the value 42 there. You run this statement:
update CombinedView SET X=42 where rowid=R;
Is it important to you that Clean.X be updated to 42, or is it OK if it
remains null?
If the latter is OK, then you can have a single trigger doing something
like this:
insert or replace into Clean(X, Y, Z)
select
case when new.X = ifnull(c.X, d.X) then c.X else new.X end,
case when new.Y = ifnull(c.Y, d.Y) then c.Y else new.Y end,
case when new.Z = ifnull(c.Z, d.Z) then c.Z else new.Z end
from Clean c join Dirty d on (c.rowid=d.rowid and c.rowid=new.rowid);
If you do need to translate a "no-op" update into setting a value in
Clean, then I don't see a way around one trigger per column. I can't
think of a way for a whole-table trigger to distinguish between a column
not touched, and a column explicitly set to the value it already had.
--
Igor Tandetnik
