Module 6: Implementing Data Integrity by Using Triggers and XML Schemas

1
Module 6 Implementing Data Integrity by Using
Triggers and XML Schemas
2
Overview

• Implementing Triggers
• Implementing XML Schemas

3
Lesson 1 Implementing Triggers

• What Are Triggers?
• How an INSERT Trigger Works
• How a DELETE Trigger Works
• How an UPDATE Trigger Works
• How an INSTEAD OF Trigger Works
• How Nested Triggers Work
• Considerations for Recursive Triggers
• Practice Creating Triggers

4
What Are Triggers?

• Special stored procedures that execute when
  INSERT, UPDATE, or DELETE statements modify a
  table
• Two categories
• AFTER triggers execute after an INSERT, UPDATE,
  or DELETE statement
• INSTEAD OF triggers execute instead of an INSERT,
  UPDATE, or DELETE statement
• Trigger and the initiating statement are part of
  a single transaction

5
How an INSERT Trigger Works
INSERT statement executed
1
INSERT statement logged
2
AFTER INSERT trigger statements executed
3
CREATE TRIGGER insrtWorkOrder ON
Production.WorkOrderAFTER INSERT ASBEGIN
SET NOCOUNT ON INSERT INTO Production.Transa
ctionHistory( ProductID,ReferenceOrderID,
TransactionType ,TransactionDate,Quantity
,ActualCost) SELECT inserted.ProductID,inse
rted.WorkOrderID ,'W',GETDATE(),inserted.Or
derQty,0 FROM insertedEnd
6
How a DELETE Trigger Works
DELETE statement executed
1
DELETE statement logged
2
AFTER DELETE trigger statements executed
3
CREATE TRIGGER delCategory ON
CategoriesAFTER DELETE ASBEGIN UPDATE P SET
Discontinued 1 FROM Products P INNER JOIN
deleted as d ON P.CategoryID
d.CategoryID END
7
How an UPDATE Trigger Works
UPDATE statement executed
1
UPDATE statement logged
2
AFTER UPDATE trigger statements executed
3
CREATE TRIGGER updtProductReview ON
Production.ProductReviewAFTER UPDATE NOT FOR
REPLICATION AS BEGIN UPDATE
Production.ProductReview SET
Production.ProductReview.ModifiedDate
GETDATE() FROM inserted WHERE
inserted.ProductReviewID
Production.ProductReview.ProductReviewIDEN
D
8
How an INSTEAD OF Trigger Works
UPDATE, INSERT, or DELETE statement executed
1
Executed statement does not occur
2
INSTEAD OF trigger statements executed
3
CREATE TRIGGER delEmployee ON
HumanResources.Employee INSTEAD OF DELETE
NOT FOR REPLICATION AS BEGIN SET NOCOUNT
ON DECLARE _at_DeleteCount int SELECT
_at_DeleteCount COUNT() FROM deleted IF
_at_DeleteCount gt 0 BEGIN ENDEND
9
How Nested Triggers Work
INSERT, UPDATE, or DELETE statement
1
3
and so on
2
Trigger executes INSERT, UPDATE, or DELETE on
another table
10
Considerations for Recursive Triggers

• Disabled by default. To enable
• Considerations
• Can exceed the 32-level nesting limit without
  careful design and thorough testing
• Can be difficult to control the order of table
  updates
• Can be replaced with nonrecursive logic

ALTER DATABASE AdventureWorks SET
RECURSIVE_TRIGGERS ON
11
Practice Creating Triggers

• In this practice, you will
• Drop existing triggers
• Create an UPDATE trigger
• Create an INSTEAD OF trigger

12
Lesson 2 Implementing XML Schemas

• What Are XML Schemas?
• What Is an XML Schema Collection?
• What Is Typed XML?
• Practice Using Typed XML

13
What Are XML Schemas?

• Defines the elements and attributes that are
  valid in an XML document
• Uses the XML Schema syntax defined by the W3C

14
What Is an XML Schema Collection?

• Named collection of one or more XML schemas
• Associated with columns or variables of xml data
  type to provide typed XML capability
• Created by using CREATE XML SCHEMA COLLECTION
  statement

15
What Is Typed XML?

• Columns or variables of the xml data type that
  are associated with an XML schema collection
• SQL Server validates the contained XML against
  XML schemas in the associated XML schema
  collection

16
Practice Using Typed XML

• In this practice, you will
• Create an XML schema collection
• Create a typed XML column
• Insert valid and invalid data into a typed XML
  column

17
Lab Implementing Data Integrity by Using
Triggers and XML Schemas

• Exercise 1 Creating Triggers
• Exercise 2 Implementing XML Schemas