> 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]
