I have a use case to update a view, and wanted to make sure I'm not missing anything obvious.
The basics of my schema are that I have a table from a third party that might be updated at any time. A lot of the data in that table is garbage though, and I want cleaner data, so I have a parallel table that has the exact same set of columns, but they all start off null (except for the rowid primary key). I have a view that generates a hybrid view of the data, joining the parallel table with the original table, using ifnull to flatten the information [ifnull(parallel.columnname, original.columnname) as columnname]. This view does exact what I want. Then I realized how handy it would be if I could update the view when I notice things amiss. So I created a single instead of trigger that updated all columns in the parallel table when the view was updated. And it updated all columns, even those I didn't specify in my update statement. This is completely rational, it just wasn't what I was expecting (having not it through, apparently, and having never used triggers before). Next I dropped that trigger and created 20 separate instead of triggers, one per column. This behaves as I'd hoped the original trigger would behave. 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? -- Scott Robison
