> I have a view that left-joins two tables, where the first table has got unique > columns. I have created an insert-trigger and an update-trigger on the view. > The insert trigger works fine. > If I do an "UPDATE testview SET xyz='test' WHERE id=1;" the update-trigger > throws an error 'SQL error: column xyz is not unique' (where xyz is the > unique column of the first table). But the same update-statement directly on > the first table works fine! I don't know what the Problem is... > I appreciate any help anyone can give me.
I have found the best way to handle triggers on views is to use column name aliases for the columns in the view. This seems to make the view work much more like a normal table when defining the triggers. The following SQLite shell session demonstrates how you need to define the triggers with a simple view definition, and how much simpler it is with the column name aliases. SQLite version 2.8.11 Enter ".help" for instructions sqlite> .echo on sqlite> .read test.sql read test.sql --create some base tables to test create table t1 (a, b); insert into t1 values (1, 10); create table t2 (a, c); insert into t2 values (1, 100); headers on --create a view the most direct way create view v1 as select * from t1 join t2 using (a); --show the column names select * from v1; t1.a|t1.b|t2.c 1|10|100 --this doesn't work /* create trigger up_v1_c instead of update of t2.c on v1 begin update t2 set c = new.t2.c where a = new.t1.a; end; create trigger in_v1 instead of insert on v1 begin insert into t1 values (new.t1.a, new.t1.b); insert into t2 values (new.t1.a, new.t2.c); end; */ --must quote the column names to get the expected behavior create trigger up_v1_c instead of update of "t2.c" on v1 begin update t2 set c = new."t2.c" where a = new."t1.a"; end; create trigger in_v1 instead of insert on v1 begin insert into t1 values (new."t1.a", new."t1.b"); insert into t2 values (new."t1.a", new."t2.c"); end; --test these triggers insert into v1 values (2, 20, 200); insert into v1 values (3, 30, 300); select * from v1; t1.a|t1.b|t2.c 1|10|100 2|20|200 3|30|300 update v1 set "t2.c" = 222 where "t1.a" = 2; select * from v1; t1.a|t1.b|t2.c 1|10|100 2|20|222 3|30|300 /******* Now a better way ***********/ --create view with column name aliases create view v2 as select t1.a as a, t1.b as b, t2.c as c from t1 join t2 using (a); --show the column names select * from v1; a|b|c 1|10|100 2|20|222 3|30|300 -- now the triggers work as expected create trigger up_v2_c instead of update of c on v2 begin update t2 set c = new.c where a = new.a; end; create trigger in_v2 instead of insert on v2 begin insert into t1 values (new.a, new.b); insert into t2 values (new.a, new.c); end; --test the triggers insert into v2 values (4, 40, 400); insert into v2 values (5, 50, 500); select * from v2; a|b|c 1|10|100 2|20|222 3|30|300 4|40|400 5|50|500 update v2 set c = 444 where a = 4; select * from v2; a|b|c 1|10|100 2|20|222 3|30|300 4|40|444 5|50|500 sqlite> I hope this helps. --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]