View, Stored Procedures, Triggers

Here is the code for the Views, Stored Procedures and Triggers

Use CommunityAssist
Go
--this is a view. A view is used to provide
--a particular "view" of the data
--usually taylored to a particular set of users
--views are behave like tables
--they are really filtered queries. No
--data is actually stored in the view itself
Alter view vw_Employees
As
Select LastName [Last Name],
FirstName [First Name],
Street,
City,
[State],
Zip [zip code]
From Person p
inner join PersonAddress pa
on p.PersonKey=pa.PersonKey
Inner join Employee e
on e.PersonKey=p.PersonKey

--using the view like a table
Select * from vw_Employees

--the aliases in a view become the 
--actual column names
Select [Last Name], [First Name], Street
From vw_Employees

go
--a store procedure is an executable
--that consist of one or more sql statements
--they can be used to make dangerous
--commands like updates safe
Create proc usp_UpdateAddress
--parameters provided by the user
--probablly through a form
@PersonAddressKey int ,
@street nvarchar(255) ,
@City nvarchar(255),
@Zip nvarchar(10) 
AS --beginning of the procedure proper
--check to see the the address exists
if exists
 (Select PersonAddressKey 
 from PersonAddress
 Where PersonAddressKey = @PersonAddressKey)
Begin --if it does
--update the address with the new values
Update PersonAddress
Set Street = @Street,
City=@City,
Zip = @Zip
Where PersonAddressKey=@PersonAddressKey
End --end if true
Else --if false
--print a message
print 'Address doesnt exist'

Select * From Personaddress

--calling the stored procedure and 
--providing the parameter values
--the keyword exec is optional
exec usp_UpdateAddress
@PersonAddressKey = 70,
@Street='10 South Mann Street',
@City='Tacoma',
@Zip='98000'

Go
--triggers are procedures that execute 
--on an event
--the following executes on inserts into
--the Donation table
Alter trigger tr_DonationAmount on Donation
for Insert 
As
--check to see if the receipt table exists
--if not create it
If not exists
(Select name from sys.tables 
 where name='receipt')
Begin
Create Table receipt
(

DonationDate DateTime, 
DonationAmount money, 
PersonKey int, 
EmployeeKey int
)
End
--declare a variable
Declare @DonationAmt money
--assign a value to the variable from the inserted
--table. the Inserted table is a temporary table
--created to store insertions for the length of
--the transaction
Select @DonationAmt = DonationAmount from inserted
--check on the size of the new donation
if (@DonationAmt >=200)
Begin --if greater than 199
--insert it into the receipt table
Insert into receipt(
DonationDate, 
DonationAmount, 
PersonKey, 
EmployeeKey)
--the values to be inserted are selected
--from the inserted table
Select 
DonationDate, 
DonationAmount, 
PersonKey, 
EmployeeKey
From Inserted
End

--using the key
Insert into Donation(DonationDate, DonationAmount, 
PersonKey, EmployeeKey)
Values(GETDATE(),250,1, 1)

Select * From Receipt
Select * From Donation