Shawn Wilsher wrote: > Hey all, > > I'm working with a partitioned table setup with a permanent table and > a temp table with the same columns and indexes. Every X time we dump > all the records in the temp table over to the permanent one. In order > to make selection queries easier to manage, I've gone and created a > view like so: > CREATE TEMPORARY VIEW table_view AS > SELECT * FROM table_temp > UNION > SELECT * FROM table > > This was all going well, until I realized that updating was going to > be very hard (insertion always goes to the temporary table). That > seemed easy enough to manage if I use an INSTEAD OF trigger on the > view for UPDATE statements. The problem is what I want to do in the > trigger, which is this: > 1) if the data is in the temporary table, update that > 2) if the data is not in the temporary table, copy the data from the > permanent table into the temp one, and then update the temp table > Sadly, my SQL-fu isn't strong enough to know how to do this, and I'm > starting to wonder if it's even possible. If someone could tell me if > I can do it, and then provide a pointer as to how to go about it, I'd > really appreciate it.
Given a row that you have to work with, your current setup is lacking enough info to know which table the row came from in the view. You might define you view like: CREATE TEMPORARY VIEW table_view AS SELECT *, 'T' as which FROM table_temp UNION SELECT *, 'P' as which FROM table Now in your trigger you can check "which" and know if you need to copy the row to table_temp. -Steve _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
