yzcehuvb

1DROP VIEW IF EXISTS item
2DROP TABLE IF EXISTS item
3DROP TABLE IF EXISTS histprice
4DROP TABLE IF EXISTS itemWithinfo
5
6-- old no history TABLE
7CREATE TABLE item (
8	itemno int identity(1,1) primary key,
9	itemname varchar(25),
10	price decimal(7,2)
11)
12
13INSERT INTO item values('pommes frites',17),('small burger',25),('checken nuggets',28),('hot wings',27)
14
15-- new system with history
16CREATE TABLE itemWithinfo (
17	itemno int identity(1,1) primary key,
18	itemname varchar(25)
19)
20
21CREATE TABLE histprice (
22	itemno int foreign key references itemWithinfo,
23	price decimal(7,2),
24	fromdate datetime
25)
26
27SET IDENTITY_INSERT itemWithinfo on
28
29INSERT INTO itemWithinfo (itemno, itemname)
30SELECT itemno, itemname FROM item where itemname is not null
31
32INSERT INTO histprice
33SELECT itemno, price, GETDATE() FROM item
34
35SET IDENTITY_INSERT itemWithinfo OFF
36select * from itemWithinfo
37select * from item
38
39drop table item
40GO
41
42CREATE VIEW item AS
43	SELECT iwi.itemno, iwi.itemname, hp.price
44	FROM histprice hp, itemWithinfo iwi
45	WHERE iwi.itemno = hp.itemno
46GO
47
48CREATE TRIGGER insertItem ON item
49INSTEAD OF INSERT AS
50BEGIN
51	INSERT INTO itemWithInfo(itemname) SELECT itemname FROM inserted
52	INSERT INTO histprice SELECT @@IDENTITY, price, GETDATE() FROM inserted
53END
54GO
55
56alter TRIGGER updateItem ON item 
57INSTEAD OF UPDATE AS
58BEGIN
59	UPDATE itemWithInfo SET itemname = (SELECT itemname FROM inserted) WHERE itemWithInfo.itemno = (SELECT itemno from deleted)
60	--UPDATE histprice SET price = (select price from inserted) WHERE histprice.itemno = (SELECT itemno from deleted)
61	INSERT INTO histprice SELECT @@IDENTITY, price, GETDATE() FROM inserted
62END
63GO
64
65CREATE TRIGGER deleteItem ON item 
66INSTEAD OF DELETE AS
67BEGIN
68	DELETE histprice WHERE itemno = (SELECT itemno FROM deleted)
69	DELETE itemWithinfo WHERE itemno = (SELECT itemno FROM deleted)
70END
71GO
72
73-- queries used by the old system
74SELECT price from item where itemno = 2
75INSERT INTO item(itemname,price) values ('big burger',45)
76update item set price = 42 where itemno = 1
77delete from item where itemno = 3  
78
79select * from itemWithInfo join histprice on histprice.itemno = itemWithinfo.itemno
80select * from histprice
81select * from item
82
83select price from item where itemno = 2
84insert into item(itemname,price) values('big burger',45)
85update item set price = 50 where itemno = 4
86delete from item where itemno = 3