View difference between Paste ID: RWJ4JKGc and uTruzD3e
SHOW: | | - or go back to the newest paste.
1
create table dba.InPlaceTest
2
(
3
	ID integer not null primary key default autoincrement,
4
	Val double,
5
	GroupID integer not null,
6
	Type varchar(5) not null,
7
	seq integer not null,
8
	unique(GroupID, Type, seq)
9
);
10
insert into dba.InPlaceTest(GroupID, Type, seq, Val) values(15673, 'STD', 1, 20);
11
insert into dba.InPlaceTest(GroupID, Type, seq, Val) values(15673, 'STD', 2, 40);
12
insert into dba.InPlaceTest(GroupID, Type, seq, Val) values(15673, 'STD', 3, 60);
13
insert into dba.InPlaceTest(GroupID, Type, seq, Val) values(18635, 'STD', 1, 10);
14
insert into dba.InPlaceTest(GroupID, Type, seq, Val) values(18635, 'STD', 2, 30);
15
insert into dba.InPlaceTest(GroupID, Type, seq, Val) values(18635, 'STD', 3, 50);
16
17-
create view dba.InPlaceTestView as
17+
drop trigger InPlaceTestView_Update;
18-
select GroupId, seq, GroupId || '-' || seq as row_id,
18+
19
create or replace view dba.InPlaceTestView as
20
select GroupId, type, GroupId || '-' || type as row_id,
21
	sum(if seq=1 then Val else 0 endif) as Seq1,
22
	sum(if seq=2 then Val else 0 endif) as Seq2,
23-
group by GroupId, seq;
23+
24
from dba.InPlaceTest
25
group by GroupId, type;
26
27
create or replace trigger InPlaceTestView_Update instead of update on dba.InPlaceTestView
28-
	raiserror 99999 'row: GroupID=' || new_name.GroupID || ', seq=' || new_name.seq || ', row_id=' || new_name.row_id
28+
29
begin
30
	raiserror 99999 'row: GroupID=' || new_name.GroupID || ', type=' || new_name.type || ', row_id=' || new_name.row_id
31
		|| ', seq1=' || new_name.seq1 || ', seq2=' || new_name.seq2 || ', seq3=' || new_name.seq3;
32
end;