Assignment 12 code

Here is the code for assignment 12
Use CommunityAssist

create schema HumanResources

Create view HumanResources.vw_EmployeeInfo
As
Select
SSNumber,
LastName as [Last Name],
FirstName as [First Name],
Street,
Apartment,
ContactInfo,
HireDate,
Dependents
From Person p
inner Join PersonAddress pa
on p.PersonKey=pa.PersonKey
inner Join PersonContact pc
on p.PersonKey=pc.PersonKey
inner join Employee e
on p.PersonKey=e.PersonKey

select * from HumanResources.vw_EmployeeInfo

Create login Sue with password='p@ssw0rd1'

Create user SueUser for login Sue with default_schema=HumanResources

Grant Select on Schema::HumanResources to SueUser

Select * from vw_EmployeeInfo

Triggers and Xml

Here is the code we did in class:

Create Trigger tr_InvalidStartDate on Subscription
Instead of Insert
As
declare @ThisDate Datetime
Set @thisDate=GetDate()
Declare @Start Datetime
Select @start=SubscriptionStart from Inserted
If @Start < @ThisDate
Begin
Print 'Invalid Start Date'
End
Else
Begin
Insert into Subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
(Select CustID, MagDetID, SubscriptionStart, SubscriptionEnd from Inserted)
End

Insert into Subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (1, 2, '2/29/2010','2/28/2011')

Select * from Subscription

Drop Trigger tr_InvalidStartDate

Use CommunityAssist

Alter xml schema collection GrantsNotesSchema
As
'<?xml version="1.0" encoding="utf-8"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="grantnotes">
<xs:complexType>
<xs:sequence>
<xs:element name="circumstance" type="xs:string" />
<xs:element name="actions">
<xs:complexType>
<xs:sequence>
<xs:element maxOccurs="unbounded" name="action" type="xs:string" />
</xs:sequence>
</xs:complexType>
</xs:element>
</:sequence>
</xs:complexType>
</xs:element>
</xs:schema>'

Alter table ServiceGrant
Add DetailNotes xml(GrantsNotesSchema)

Insert into ServiceGrant(GrantAmount, GrantDate, PersonKey, ServiceKey, EmployeeKey, DetailNotes)
Values (200.00,'3/1/2010',1, 1, 1, '<?xml version="1.0" encoding="utf-8"?>
<grantnotes>
<circumstance>
Unable to make the rent this month due to medical bills. Need a one time grant to
make this months rent payment. this is the second month in a row
that the client has needed this assistance
</circumstance>
<actions>
<action>provide $1200 toward rent</action>
<action>Explore possiblility of getting on State Insurance fund for future medical bills</action>
<action>Meet again in one months time</action>
</actions>
<grantnotes>')

Select * from ServiceGrant

Select * from Person for xml raw('person'), root('persons'), elements