Triggers

--stored procedures
--parameterized view

use Community_Assist
go
Create proc usp_HRViewProc
@EmployeeKey int
As
Select PersonLastName,
PersonfirstName,
PersonEmail,
EmployeeHireDate,
EmployeeAnnualSalary
From Person p
inner Join Employee e
on p.PersonKey=e.PersonKey
Where EmployeeKey = @EmployeeKey

exec usp_HRViewProc @EmployeeKey=2
Go
--Create hash password function
Alter function fx_hashPassword2
(@seed int, @password nvarchar(50))
returns varbinary(500)
As
Begin
Declare @newPassword nvarchar(70)
set @newPassword = 
cast(@seed as nvarchar(20)) + @Password
Declare @hashed varbinary(500)
set @hashed = hashbytes('sha2_512', @newPassword)
return @hashed
End


Select * from businessrule
go
--new person stored procedure
Alter proc usp_NewPerson
@lastName nvarchar(255),
@FirstName nvarchar(255)=null,
@Email nvarchar(255),
@password nvarchar(50),
@AptNumber nvarchar(255)=null,
@Street nvarchar(255),
@City nvarchar(255)='Seattle',
@State nvarchar(255)='WA',
@Zip nvarchar(255),
@HomePhone nvarchar(255)
As
--test to see if person exists
if exists
 (Select PersonLastName, PersonEmail From Person
 Where PersonLastName=@LastName
 And PersonEmail=@Email)
 Begin--begin if
 Print 'Already registered'
 return
 End--end if

--create the password hash
Declare @seed int
Set @seed = dbo.fx_GetSeed()
declare @hashedPass varbinary(500)
set @hashedPass = dbo.fx_HashPassword(@seed,@password)

Begin tran --start a transaction
Begin try -- begin try
--insert into person
Insert into Person(  
PersonLastName, PersonFirstName,
 PersonEmail, PersonPassWord, 
 PersonEntryDate, PersonPassWordSeed)
 Values(@LastName, @FirstName,
 @Email, @hashedPass,GetDate(),@seed)

 --get the key of the person just inserted
 Declare @key int = ident_current('Person')
 --insert into PersonAddress
 Insert into PersonAddress(
 PersonAddressApt,
 PersonAddressStreet, 
 PersonAddressCity, PersonAddressState, 
 PersonAddressZip, PersonKey)
 Values( @AptNumber,@street,@city,@state,@Zip,@key)

 Insert into Contact([ContactNumber],
 [ContactTypeKey], [PersonKey])
 Values (@HomePhone, 1, @Key)

 Commit Tran --commit the transaction if no error
 End Try
 Begin Catch
 Rollback Tran
 Print Error_Message()
 End Catch


 exec usp_NewPerson
 @lastName = 'Conger', 
 @FirstName='Steve', 
 @Email='spconger@gmail.com', 
 @password='congerPass', 
 @Street='101 nowhere bld', 
 @Zip='98122', 
 @HomePhone='2065551201'

 Select * from PersonAddress
 Select * from Contact

 Select * from Person
 go
 --stored procedure update Address
 Create proc usp_UpdateAddress
 @PersonAddressApt nvarchar(255) = null, 
 @PersonAddressStreet nvarchar(255), 
 @PersonAddressCity nvarchar(255)= 'Seattle', 
 @PersonAddressState nvarchar(255)='Wa', 
 @PersonAddressZip nvarchar(255), 
 @PersonKey int
As
Begin tran
Begin Try
 Update PersonAddress 
 Set PersonAddressApt=@PersonAddressApt,
 PersonAddressStreet=@PersonAddressStreet,
 PersonAddressCity =@PersonAddressCity,
 PersonAddressState=@PersonAddressState,
 PersonAddressZip=@PersonAddressZip
 Where PersonKey = @PersonKey
Commit Tran
End try
Begin Catch
 Rollback tran
 print error_message()
End catch


Select * from PersonAddress

Exec usp_UpdateAddress
@PersonAddressApt = '304', 
@PersonAddressStreet='100 South Mann Street', 
@PersonAddressZip='98001',
@PersonKey=1;


--trigger
--like a stored proc but triggered by an event 
--insert update delete
--triggers have no parameters
-- for an event, instead of an event

Create Trigger Tr_PersonDelete on Person
instead of Delete
As
if not exists
  (Select name from sys.Tables 
    where name='PersonDelete')
Begin
CREATE TABLE [dbo].[PersonDelete](
 [PersonKey] int NOT NULL,
 [PersonLastName] nvarchar(255) NOT NULL,
 [PersonFirstName] nvarchar(255) NULL,
 [PersonEmail] nvarchar(255) NULL,
 [PersonPassWord] varbinary(500) NULL,
 [PersonEntryDate] datetime NOT NULL,
 [PersonPassWordSeed] int NULL
 )

End
Insert into PersonDelete (PersonKey,PersonLastName,
PersonFirstName,PersonEmail,PersonPassWord,
PersonEntryDate, PersonPassWordSeed)
Select PersonKey,PersonLastName,
PersonFirstName,PersonEmail,PersonPassWord,
PersonEntryDate, PersonPassWordSeed
From Deleted --temp table in temp database that stores 
--deletions and updates

Select * from Person

Insert into Person (PersonLastName, PersonFirstName, 
PersonEmail,  PersonEntryDate)
Values('NotHereLong','Deleted','Deleted@gmail.com', GetDate())

Delete from Person where Personkey=132

Select * from PersonDelete
--check to see if request is large than max request
Create trigger tr_CheckMax on GrantRequest
for Insert
as
if not exists
  (Select name from sys.Tables
    Where name ='OverMax')
Begin
Create Table OverMax
(
     RequestDate date,
  PersonKey int,
  GrantypeKey int,
  Explanation nvarchar(255) null,
  RequestAmount money

)

End

Declare @GrantMax money
Declare @GrantType int
Declare @RequestAmount money

Select @GrantType=GrantTypeKey from inserted
Select @GrantMax = GrantTypeMaximum from GrantType
   Where GranttypeKey=@GrantType
Select @RequestAmount = GrantRequestAmount from inserted

if @RequestAmount > @GrantMax
Begin
Insert into OverMax(RequestDate, PersonKey, GranTypeKey,Explanation, RequestAmount)
Select GrantRequestDate, PersonKey, GrantTypeKey, GrantRequestExplanation, GrantRequestAmount
From inserted
End

Select * from GrantType


insert into GrantRequest( 
GrantRequestDate, PersonKey, GrantTypeKey, 
GrantRequestExplanation, GrantRequestAmount)
Values (GetDate(),1,1,'Always hungry',300.00)

Select * from GrantRequest

Select * from OverMax