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