Functions and A Trigger

Use VenueTracker



--to do
--artist take venue take Sellout

--Function for sellout
--function for Venue take
--function for Artist take

--trigger to make a list of people to notify about upcoming events
Go

--this function has some problems. It duplicates capacity and so gives a false
--amount for shows that have multiple ticket vendors
Alter Function Venue.func_SellOut
(@ticketPrice decimal(5,2),
@Capacity int) --these are user provided parmeters
returns Decimal(9,2) --the return type for the function
As --begin function
Begin--functions require a begin and end statement
Declare @Total Decimal(9,2) --declare a variable
Set @Total= @ticketPrice * @Capacity --set the value
return @Total --return it
End
GO

Select VenueName, s.ShowID, TicketPrice, VenueCapacity, 
Venue.func_SellOut(TicketPrice, VenueCapacity) as Total
From Venue v
inner Join Show s
on s.VenueID = v.VenueID
inner join TicketOutlet t
on t.ShowID=s.ShowID

Select VenueName, s.ShowID, VenueCapacity, 
Sum(Venue.func_SellOut(TicketPrice, VenueCapacity) )as Total
From Venue v
inner Join Show s
on s.VenueID = v.VenueID
inner join TicketOutlet t
on t.ShowID=s.ShowID
Group by VenueName, s.ShowID,  VenueCapacity

Select * From TicketOutlet

Alter Table TicketOutlet
Add TotalTickets int

Select * From TicketOutlet
Select * From Venue
Go
Create Function Venue.func_CurrentSales
(@Totaltickets int, @TicketsRemaining int, @TicketPrice Decimal(5,2))
Returns Decimal(9,2)
As
Begin
Declare @TicketsSold int
Set @TicketsSold=@Totaltickets-@TicketsRemaining
Return @TicketsSold * @TicketPrice

End
--test the function
Select VenueName, s.ShowID, 
Venue.func_CurrentSales(TotalTickets, TicketsRemaining, ticketPrice)
As TotalSale
From Venue v
inner Join Show s
On v.VenueID=s.VenueID
Inner Join TicketOutlet t
on s.ShowID=t.ShowID
Go
--this is a view using the function
--it also uses a sum to combine the revenues
--of more than one ticket outlet
Go
Create View Venue.CurrentSalesSummary
As
Select VenueName, s.ShowID, 
Sum(Venue.func_CurrentSales(TotalTickets, TicketsRemaining, ticketPrice))
As TotalSale
From Venue v
inner Join Show s
On v.VenueID=s.VenueID
Inner Join TicketOutlet t
on s.ShowID=t.ShowID
Group by VenueName, s.ShowID

Select * From Venue.CurrentSalesSummary 
Where VenueName='Key Arena'

go
--this does the same as the view
--but requires the name of a venue
Create Proc Venue.usp_SalesSummary
@VenueName nvarchar(255)
As
Select VenueName, s.ShowID, 
Sum(Venue.func_CurrentSales(TotalTickets, TicketsRemaining, ticketPrice))
As TotalSale
From Venue v
inner Join Show s
On v.VenueID=s.VenueID
Inner Join TicketOutlet t
on s.ShowID=t.ShowID
Where VenueName=@VenueName
Group by VenueName, s.ShowID

Exec Venue.usp_SalesSummary 'Columbia Gorge'

--lets do a trigger
--Trigger will be triggered whenever there is an insert on the show table
--and it will make a table of people to be notified of upcoming events
--check the artistid and see if it matches any artistid in CustomerArtist Table where the ArtistAlert is set to true
--create a table of people to be alterted which will consist of the customer email, the artist name and the showvenue and date

Go
Create trigger tr_NotifyCustomers
on Show --the table the trigger applies to
For Insert  -- the even
As
Declare @ArtistID int
--inserted is a table that exists in the temp database
--it only exists for the duration of the transaction
--about a millesecond
Select @ArtistID=ArtistID from inserted
--check to see if someone wants to be alerted
--about the artist
If Exists
 (Select ArtistID from CustomerArtist
  Where ArtistID=@ArtistID 
  And ArtistAlert=1) 
Begin --if someone does
--check to see if our alert table exists
 If  not Exists
  (Select Name from sys.tables
   Where name='CustomersToAlert')
 Begin --if it doesn't create the table
  Create Table CustomersToAlert
  (
   CustomerEmail nvarchar(255),
   ArtistName nvarchar(255),
   VenueName nvarchar(255),
   ShowDate date,
   ShowTime Time
  )
 End --end if table doesn't exist
 --declare the variable we will need to
 --insert into our table
Declare @CustomerEmail nvarchar(255)
Declare @ArtistName nvarchar(255)
Declare @VenueName nvarchar(255)
Declare @ShowDate date
Declare @ShowTime time

--assign values to each of the variables
Select @ArtistName=ArtistName from Artist
Where ArtistID = @ArtistID

Select @CustomerEmail = CustomerEmail
From Customer c
Inner join CustomerArtist  ca
On c.CustomerID=ca.CustomerID
where ca.ArtistID=@ArtistID
And ca.ArtistAlert=1

Select @VenueName=VenueName
From Venue v
inner Join Inserted i
on v.VenueID=i.VenueID


Select @ShowDate = ShowDate, @ShowTime = ShowTime
From Inserted
--insert into our new alert table

Insert into CustomersToAlert(CustomerEmail, ArtistName, VenueName, ShowDate, ShowTime)
Values (@CustomerEmail, @ArtistName, @VenueName, @ShowDate, @ShowTime)

End
--/////this is the end of the trigger///---

--Test the trigger with an insert
Insert into Show(VenueID, ArtistID, TourName, ShowDate, ShowTime, ShowPosition, ShowDescription)
Values(3,1,'Some Kind of tour','8/12/2012','20:00:00','Headline',null)

Select * from CustomersToAlert

--it only inserted one value--we need to alter the trigger 
--to create a loop and a cursor 
--which will go through all the available records