On 4/23/07, Sylko Zschiedrich <[EMAIL PROTECTED]> wrote:
Hi all,
i have a question to "instead of triggers" on views.
Following schema:
<snip>
h>
That's my current implementation. But with this I can't update the View
to 'null' values because the coalesce statement will return the
old.values.
How can I handle that?
Can I "detect" the columns to update?
Actually, you don't need to. the OLD and NEW pseudotables don't
contain just the data affected by the update; it includes the
*complete* row before the update (in OLD) and after the update (in
NEW).
I have attached a sqlite3 script demonstrating this; just run it with
sqlite3 < sqlite-view-update.sql
to see the results.
A final warning: there is a gross inefficiency in the way updates on
views are handled. If your view is big -- i.e. "select count(*) from
myview" reports more than about 100K rows -- your update is going to
take inexplicably long. I'm pondering a patch for that.
--
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
.headers on
.mode columns
-- create a table
create table footbl(key INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
num INTEGER NOT NULL,
str TEXT NOT NULL,
float REAL NOT NULL
);
create table footbl_history(
oldkey int, oldnum int, oldstr text, oldfloat real,
newkey int, newnum int, newstr text, newfloat real
);
-- create a seemingly useless view
create view foo as select key, num, str, float from footbl;
create trigger foo_update instead of update on foo
begin
insert into footbl_history values (
old.key, old.num, old.str, old.float,
new.key, new.num, new.str, new.float);
update footbl set key=new.key, num=new.num, str=new.str, float=new.float
where key=old.key;
end;
insert into footbl (num,str,float) values (1, 'one', 1.0);
insert into footbl (num,str,float) values (2, 'three', 2.0);
insert into footbl (num,str,float) values (3, 'two', 3.0);
-- oops! Oh wait!
update foo set str='two' where num=2;
update foo set str='three' where num=3;
.headers off
select 'View:';
.headers on
select * from foo;
.headers off
select '';
select 'History:';
.headers on
select * from footbl_history;
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
