Modified Stored procedure and triggers

--stored procedures

Use MagazineSubscription
Go

Alter proc usp_GetSubscriptionSummary
@Month int =3
As
Select MONTH(SubscriptionStart) as [Month]
,COUNT (SubscriptionID) as [Subscriptions]
,SUM(SubscriptionPrice) as Total
From Subscription s
Inner Join MagazineDetail md
On md.MagDetID=s.MagDetID
Where MONTH(SubscriptionStart)=@month
Group by MONTH(SubscriptionStart)

go
exec usp_GetSubscriptionSummary 2

usp_GetSubscriptionSummary @Month=3


--stored procedure to enter a new magazine subscription
--new customer --add the customer
--will need the new customer ID
--Get the magazine
--what the subscription terms are for that magazine-magdetID
--insert the subscription information (date function)
--Both inserts should happen or none -- transaction and error trapping
Go
Alter proc usp_NewSubscription
--parameters provided by the user
@LastName varchar(30),
@FirstName varchar(25),
@CustAddress varchar(100),
@CustCity varchar(50),
@CustState Char(2),
@CustZipcode char(11),
@CustPhone char(10),
@magDetID int
As
Declare @StartDate datetime
set @StartDate = GETDATE()
Declare @EndDate Datetime
Declare @CustID int --added a variable for customer
Begin Tran
Begin try
--check to see if the customer exists already
if not exists
(Select CustID From Customer
Where CustLastName=@LastName
And CustFirstName=@FirstName
And CustCity=@CustCity
And CustAddress=@CustAddress
And CustPhone=@CustPhone)
Begin --if customer doesn't exist
Insert into Customer (CustLastName,
CustFirstName,
CustAddress,
CustCity,
CustState,
CustZipcode,
CustPhone)
Values(
@LastName,
@FirstName,
@CustAddress,
@CustCity,
@CustState ,
@CustZipcode,
@CustPhone)


Set @CustID=SCOPE_IDENTITY()
End
Else --if customer does exist
Begin
Select @CustID=CustID from Customer
Where CustLastName=@LastName
And CustFirstName=@FirstName
And CustCity=@CustCity
And CustAddress=@CustAddress
And CustPhone=@CustPhone
End

Declare @SubscriptionType int
Select @SubscriptionType=SubscriptTypeID
from MagazineDetail
Where MagDetID=@MagDetID


Insert into Subscription(
CustID,
MagDetID,
SubscriptionStart,
SubscriptionEnd)
Values(
@CustID,
@magDetID,
@StartDate,
dbo.func_SubscriptionEndDate(@subscriptionType, @StartDate))
commit tran
End try
Begin Catch
print error_message()
RollBack Tran
End Catch

exec usp_NewSubscription
@LastName ='London',
@FirstName ='Jack',
@CustAddress='100 artic way',
@CustCity='Seattle' ,
@CustState='WA' ,
@CustZipcode='98000',
@CustPhone='2065551962' ,
@magDetID=4

Select * from Customer
Select * from Subscription

Select [name] from sys.procedures

go
--this trigger captures the original values for
--updates and deletes and writes them
--to a table for later review
Create trigger tr_DeleteMagazine on Magazine
After delete, update
AS
if not exists
(Select [name] from sys.Tables
where [name]='ScrapPile')
Begin
Create table ScrapPile
(
MagID int,
MagName varchar(100),
MagType char(10)
)
End
Insert into ScrapPile(MagID, MagName, MagType)
(Select MagID, MagName, MagType from Deleted)

Select * from Magazine

Update Magazine
Set MagType ='Quarterly'
Where Magid=1

Insert into Magazine(MagName, MagType)
Values('Ray gun', 'Weekly')

Delete from Magazine
Where magid=12

Select * from ScrapPile
Go
--this trigger checks the start date
--of a new subscription to make
--sure not less than the current date
Alter trigger tr_SubscriptionStartDate on Subscription
instead of insert
As
Declare @start DateTime
Select @start =SubscriptionStart from inserted
If @Start < GetDate()
Begin
Set @Start=GetDate()
End

Insert into Subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
(Select CustID, MagDetID, @Start, SubscriptionEnd
From Inserted)

INsert into Subscription(
CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values(2, 2,'1/1/1903','1/1/2012')

Select * from Subscription
go
Disable trigger dbo.tr_SubscriptionStartDate on Subscription
Go
Enable trigger dbo.tr_SubscriptionStartDate on Subscription