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