Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE TABLE dba.InPlaceTest
- (
- ID INTEGER NOT NULL PRIMARY KEY DEFAULT autoincrement,
- Val DOUBLE,
- GroupID INTEGER NOT NULL,
- TYPE VARCHAR(5) NOT NULL,
- seq INTEGER NOT NULL,
- UNIQUE(GroupID, TYPE, seq)
- );
- INSERT INTO dba.InPlaceTest(GroupID, TYPE, seq, Val) VALUES(15673, 'STD', 1, 20);
- INSERT INTO dba.InPlaceTest(GroupID, TYPE, seq, Val) VALUES(15673, 'STD', 2, 40);
- INSERT INTO dba.InPlaceTest(GroupID, TYPE, seq, Val) VALUES(15673, 'STD', 3, 60);
- INSERT INTO dba.InPlaceTest(GroupID, TYPE, seq, Val) VALUES(18635, 'STD', 1, 10);
- INSERT INTO dba.InPlaceTest(GroupID, TYPE, seq, Val) VALUES(18635, 'STD', 2, 30);
- INSERT INTO dba.InPlaceTest(GroupID, TYPE, seq, Val) VALUES(18635, 'STD', 3, 50);
- CREATE VIEW dba.InPlaceTestView AS
- SELECT GroupId, seq, GroupId || '-' || seq AS row_id,
- SUM(IF seq=1 THEN Val ELSE 0 endif) AS Seq1,
- SUM(IF seq=2 THEN Val ELSE 0 endif) AS Seq2,
- SUM(IF seq=3 THEN Val ELSE 0 endif) AS Seq3
- FROM dba.InPlaceTest
- GROUP BY GroupId, seq;
- CREATE OR REPLACE TRIGGER InPlaceTestView_Update instead OF UPDATE ON dba.InPlaceTestView
- REFERENCING OLD AS old_name NEW AS new_name FOR each ROW
- BEGIN
- raiserror 99999 'row: GroupID=' || new_name.GroupID || ', seq=' || new_name.seq || ', row_id=' || new_name.row_id
- || ', seq1=' || new_name.seq1 || ', seq2=' || new_name.seq2 || ', seq3=' || new_name.seq3;
- END;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement