DROP VIEW IF EXISTS item
DROP TABLE IF EXISTS item
DROP TABLE IF EXISTS histprice
DROP TABLE IF EXISTS itemWithinfo

-- old no history TABLE
CREATE TABLE item (
	itemno int identity(1,1) primary key,
	itemname varchar(25),
	price decimal(7,2)
)

INSERT INTO item values('pommes frites',17),('small burger',25),('checken nuggets',28),('hot wings',27)

-- new system with history
CREATE TABLE itemWithinfo (
	itemno int identity(1,1) primary key,
	itemname varchar(25)
)

CREATE TABLE histprice (
	itemno int foreign key references itemWithinfo,
	price decimal(7,2),
	fromdate datetime
)

SET IDENTITY_INSERT itemWithinfo on

INSERT INTO itemWithinfo (itemno, itemname)
SELECT itemno, itemname FROM item where itemname is not null

INSERT INTO histprice
SELECT itemno, price, GETDATE() FROM item

SET IDENTITY_INSERT itemWithinfo OFF
select * from itemWithinfo
select * from item

drop table item
GO

CREATE VIEW item AS
	SELECT iwi.itemno, iwi.itemname, hp.price
	FROM histprice hp, itemWithinfo iwi
	WHERE iwi.itemno = hp.itemno
GO

CREATE TRIGGER insertItem ON item
INSTEAD OF INSERT AS
BEGIN
	INSERT INTO itemWithInfo(itemname) SELECT itemname FROM inserted
	INSERT INTO histprice SELECT @@IDENTITY, price, GETDATE() FROM inserted
END
GO

alter TRIGGER updateItem ON item
INSTEAD OF UPDATE AS
BEGIN
	UPDATE itemWithInfo SET itemname = (SELECT itemname FROM inserted) WHERE itemWithInfo.itemno = (SELECT itemno from deleted)
	--UPDATE histprice SET price = (select price from inserted) WHERE histprice.itemno = (SELECT itemno from deleted)
	INSERT INTO histprice SELECT @@IDENTITY, price, GETDATE() FROM inserted
END
GO

CREATE TRIGGER deleteItem ON item
INSTEAD OF DELETE AS
BEGIN
	DELETE histprice WHERE itemno = (SELECT itemno FROM deleted)
	DELETE itemWithinfo WHERE itemno = (SELECT itemno FROM deleted)
END
GO

-- queries used by the old system
SELECT price from item where itemno = 2
INSERT INTO item(itemname,price) values ('big burger',45)
update item set price = 42 where itemno = 1
delete from item where itemno = 3

select * from itemWithInfo join histprice on histprice.itemno = itemWithinfo.itemno
select * from histprice
select * from item

select price from item where itemno = 2
insert into item(itemname,price) values('big burger',45)
update item set price = 50 where itemno = 4
delete from item where itemno = 3