-- 10 only where alcoholicDrinkType = 'Wine'
create trigger tr_WineAward_DrinkType
on WineAward
after insert, update
as
	if not exists (select * from MenuItem as mi 
				join INSERTED as i on mi.itemNr = i.itemNr
				where mi.alcoholicDrinkType = 'Wine' and mi.itemNr = i.itemNr)
	begin
		raiserror ('WineAward cannot be designated to items that are not alcoholicDrinkType Wine.', 16, 1)  
		rollback Transaction 
	end
go

-- 11 only where alcoholicDrinkType = 'Wine'
create trigger tr_WineSupply_DrinkType
on WineSupply
after insert, update
as
	if not exists (select * from MenuItem as mi 
				join INSERTED as i on mi.itemNr = i.itemNr
				where mi.alcoholicDrinkType = 'Wine' and mi.itemNr = i.itemNr)
	begin
		raiserror ('WineSupply cannot be designated to items that are not alcoholicDrinkType Wine.', 16, 1)  
		rollback Transaction 
	end
go

-- 12 frequency of WineSupply
create trigger tr_WineSupply_Frequency
on WineSupply
after insert, update
as
if Update (itemNr)
	if((select count(*) from WineSupply where itemNr = (select itemNr from inserted)) > 3)
	begin
		raiserror ('There cannot be more than 3 WineSupply entries with the same itemNr.', 16, 1)  
		rollback Transaction 
	end

-- 13 lineitem = quantity * price
create trigger tr_LineItem_LineTotal 
on LineItem
after insert, update
as
if Update (lineTotal)
	declare @unitPrice money
	declare @quantity int
	declare @total money

	set @unitPrice = (select unitPrice from INSERTED)
	set @quantity = (select quantity from INSERTED)
	set @total = @unitPrice * @quantity

	if not exists (select * from inserted where lineTotal = @total)
	begin
		raiserror ('Value inserted for lineTotal was incorrect.', 16, 1)  
		rollback Transaction 
	end
go