Re: [sqlite] triggers : NEW keyword with multiple tables

[Sébastien Escudier]

> You haven't given the view explicit column names

How ?

> try this instead:
>
> CREATE VIEW my_view AS SELECT table1.type as table1_type, table2.type
> as table2_type FROM

Yes this works on latest version. But I wonder why the other syntax is
not accepted anymore. Because I'll have to rewrite my database views
when I upgrade sqlite.




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] triggers : NEW keyword with multiple tables

[Sébastien Escudier]

> Try NEW."table1.type" and NEW."table2.type"

This works with my old 3.6 version but not on 3.7.8
I still get : Error: no such column: NEW.table1.type


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] triggers : NEW keyword with multiple tables

[Igor Tandetnik]

On 10/25/2011 10:59 AM, Sébastien Escudier wrote:


Hello,

I used to do something like this on older sqlite versions :
(this does not really makes sense here, but this is a simplified)
CREATE VIEW my_view AS SELECT table1.type, table2.type FROM table1 INNER
JOIN table2 ON table1.id = table2.id;

CREATE TRIGGER my_trigger INSTEAD OF INSERT ON my_view
BEGIN
INSERT INTO table1(type) VALUES(NEW.table1.type);
INSERT INTO table2(type) VALUES(NEW.table2.type);
END;


Try NEW."table1.type" and NEW."table2.type"
--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] triggers : NEW keyword with multiple tables

[Doug Currie]

On Oct 25, 2011, at 10:59 AM, Sébastien Escudier wrote:

> CREATE TRIGGER my_trigger INSTEAD OF INSERT ON my_view
> BEGIN
> INSERT INTO table1(type) VALUES(NEW.table1.type);
> INSERT INTO table2(type) VALUES(NEW.table2.type);
> END;
> 
> ...
> 
> Why this syntax does not work anymore ?

You haven't given the view explicit column names, and the ones SQLite3 invents 
are arbitrary; try this instead:

CREATE VIEW my_view AS SELECT table1.type as table1_type, table2.type as 
table2_type FROM 

…

CREATE TRIGGER my_trigger INSTEAD OF INSERT ON my_view
BEGIN
INSERT INTO table1(type) VALUES(NEW.table1_type);
INSERT INTO table2(type) VALUES(NEW.table2_type);
END;


e
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] triggers : NEW keyword with multiple tables

[Sébastien Escudier]

Hello,

I used to do something like this on older sqlite versions :
(this does not really makes sense here, but this is a simplified)
CREATE VIEW my_view AS SELECT table1.type, table2.type FROM table1 INNER
JOIN table2 ON table1.id = table2.id;

CREATE TRIGGER my_trigger INSTEAD OF INSERT ON my_view
BEGIN
INSERT INTO table1(type) VALUES(NEW.table1.type);
INSERT INTO table2(type) VALUES(NEW.table2.type);
END;


But the latest version gives this error :
no such column: NEW.table1.type

If I replace NEW.table1.type and NEW.table2.type with NEW.type it gives
no error but it does not work correctly because it always takes the
value of table1.type.

Why this syntax does not work anymore ?

Regards 
Sebastien


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users