Shawn Wilsher <[EMAIL PROTECTED]> wrote:
> 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
CREATE TRIGGER ....
BEGIN
insert into temp_table
select * from perm_table
where <the data is currently not in temp table> and
<which record you want copied>;
update temp_table set ...;
END;
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
