Advertisement
Guest User

Untitled

a guest
Dec 9th, 2013
76
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  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. DROP TRIGGER InPlaceTestView_Update;
  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.     SUM(IF seq=3 THEN Val ELSE 0 endif) AS Seq3
  24. FROM dba.InPlaceTest
  25. GROUP BY GroupId, TYPE;
  26.  
  27. CREATE OR REPLACE TRIGGER InPlaceTestView_Update instead OF UPDATE ON dba.InPlaceTestView
  28.     REFERENCING OLD AS old_name NEW AS new_name FOR each ROW
  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;
Advertisement
Advertisement
Advertisement
RAW Paste Data Copied
Advertisement