2393 - Base de Datos Avanzado I
Actividad Virtual 08
Objetivo
Analiza y explora ejecuciones de funciones definidas por el usuario y triggers.
Descarga y ejecuta el script de la Base de Datos AdventureWorks2014 del siguiente enlace:
https://msftdbprodsamples.codeplex.com/releases/view/125550
I. Analiza la ejecución de los scripts de las DFU’s.
1. Ejecute el script y describa su resultado.
USE AdventureWorks2014;
GO
IF OBJECT_ID(N'dbo.ufnGetContactInformation', N'TF') IS NOT NULL
DROP FUNCTION dbo.ufnGetContactInformation;
GO
CREATE FUNCTION dbo.ufnGetContactInformation(@ContactID int)
RETURNS @retContactInformation TABLE
(
-- Columns returned by the function
ContactID int PRIMARY KEY NOT NULL,
FirstName nvarchar(50) NULL,
LastName nvarchar(50) NULL,
JobTitle nvarchar(50) NULL,
ContactType nvarchar(50) NULL
)
AS
-- Returns the first name, last name, job title, and contact type for the
specified contact.
BEGIN
DECLARE
@FirstName nvarchar(50),
@LastName nvarchar(50),
@JobTitle nvarchar(50),
@ContactType nvarchar(50);
-- Get common contact information
SELECT
@ContactID = BusinessEntityID,
@FirstName = FirstName,
@LastName = LastName
FROM Person.Person
WHERE BusinessEntityID = @ContactID;
-- Get contact job title
SELECT @JobTitle =
CASE
-- Check for employee
WHEN EXISTS(SELECT * FROM Person.Person AS p
WHERE p.BusinessEntityID = @ContactID AND
p.PersonType = 'EM')
THEN (SELECT JobTitle
FROM HumanResources.Employee AS e
WHERE e.BusinessEntityID = @ContactID)
-- Check for vendor
WHEN EXISTS(SELECT * FROM Person.Person AS p
WHERE p.BusinessEntityID = @ContactID AND
p.PersonType = 'VC')
THEN (SELECT ct.Name
FROM Person.ContactType AS ct
INNER JOIN Person.BusinessEntityContact AS bec
ON bec.ContactTypeID = ct.ContactTypeID
WHERE bec.PersonID = @ContactID)
-- Check for store
WHEN EXISTS(SELECT * FROM Person.Person AS p
WHERE p.BusinessEntityID = @ContactID AND
p.PersonType = 'SC')
THEN (SELECT ct.Name
FROM Person.ContactType AS ct
INNER JOIN Person.BusinessEntityContact AS bec
ON bec.ContactTypeID = ct.ContactTypeID
WHERE bec.PersonID = @ContactID)
ELSE NULL
END;
-- Get contact type
SET @ContactType =
CASE
-- Check for employee
WHEN EXISTS(SELECT * FROM Person.Person AS p
WHERE p.BusinessEntityID = @ContactID AND
p.PersonType = 'EM')
THEN 'Employee'
-- Check for vendor
WHEN EXISTS(SELECT * FROM Person.Person AS p
WHERE p.BusinessEntityID = @ContactID AND
p.PersonType = 'VC')
THEN 'Vendor Contact'
-- Check for store
WHEN EXISTS(SELECT * FROM Person.Person AS p
WHERE p.BusinessEntityID = @ContactID AND
p.PersonType = 'SC')
THEN 'Store Contact'
-- Check for individual consumer
WHEN EXISTS(SELECT * FROM Person.Person AS p
WHERE p.BusinessEntityID = @ContactID AND
p.PersonType = 'IN')
THEN 'Consumer'
-- Check for general contact
WHEN EXISTS(SELECT * FROM Person.Person AS p
WHERE p.BusinessEntityID = @ContactID AND
p.PersonType = 'GC')
THEN 'General Contact'
END;
-- Return the information to the caller
IF @ContactID IS NOT NULL
BEGIN
INSERT @retContactInformation
SELECT @ContactID, @FirstName, @LastName, @JobTitle,
@ContactType;
END;
RETURN;
END;
GO
2. Usando la función.
SELECT ContactID, FirstName, LastName, JobTitle, ContactType
FROM dbo.ufnGetContactInformation(1209);
GO
SELECT ContactID, FirstName, LastName, JobTitle, ContactType
FROM dbo.ufnGetContactInformation(5);
GO
3. Ejecute el script y describa el resultado.
IF OBJECT_ID (N'dbo.ufnGetInventoryStock', N'FN') IS NOT NULL
DROP FUNCTION ufnGetInventoryStock;
GO
CREATE FUNCTION dbo.ufnGetInventoryStock(@ProductID int)
RETURNS int
AS
-- Returns the stock level for the product.
BEGIN
DECLARE @ret int;
SELECT @ret = SUM(p.Quantity)
FROM Production.ProductInventory p
WHERE p.ProductID = @ProductID
AND p.LocationID = '6';
IF (@ret IS NULL)
SET @ret = 0;
RETURN @ret;
END;
GO
4. Utiliza la función.
SELECT ProductModelID, Name, dbo.ufnGetInventoryStock(ProductID)AS
CurrentSupply
FROM Production.Product
WHERE ProductModelID BETWEEN 75 and 80;
GO
II. Analiza la ejecución de los scripts de los Triggers.
5. Ejecute el script y describa el resultado.
IF OBJECT_ID ('Sales.reminder1', 'TR') IS NOT NULL
DROP TRIGGER Sales.reminder1;
GO
CREATE TRIGGER reminder1
ON Sales.Customer
AFTER INSERT, UPDATE
AS RAISERROR ('Notify Customer Relations', 16, 10);
GO
6. Ejecute el script y describe el resultado.
IF OBJECT_ID ('Sales.reminder2','TR') IS NOT NULL
DROP TRIGGER Sales.reminder2;
GO
CREATE TRIGGER reminder2
ON Sales.Customer
AFTER INSERT, UPDATE, DELETE
AS
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'AdventureWorks2008R2 Administrator',
@recipients = 'danw@Adventure-Works.com',
@body = 'Don''t forget to print a report for the sales force.',
@subject = 'Reminder';
GO
7. Ejecute el script y describe el resultado.
IF OBJECT_ID ('Purchasing.LowCredit','TR') IS NOT NULL
DROP TRIGGER Purchasing.LowCredit;
GO
-- This trigger prevents a row from being inserted in the
Purchasing.PurchaseOrderHeader table
-- when the credit rating of the specified vendor is set to 5 (below
average).
CREATE TRIGGER Purchasing.LowCredit ON Purchasing.PurchaseOrderHeader
AFTER INSERT
AS
IF EXISTS (SELECT *
FROM Purchasing.PurchaseOrderHeader p
JOIN inserted AS i
ON p.PurchaseOrderID = i.PurchaseOrderID
JOIN Purchasing.Vendor AS v
ON v.BusinessEntityID = p.VendorID
WHERE v.CreditRating = 5
)
BEGIN
RAISERROR ('A vendor''s credit rating is too low to accept new purchase
orders.', 16, 1);
ROLLBACK TRANSACTION;
RETURN
END;
GO
-- This statement attempts to insert a row into the PurchaseOrderHeader
table
-- for a vendor that has a below average credit rating.
-- The AFTER INSERT trigger is fired and the INSERT transaction is rolled
back.
INSERT INTO Purchasing.PurchaseOrderHeader (RevisionNumber, Status,
EmployeeID,
VendorID, ShipMethodID, OrderDate, ShipDate, SubTotal, TaxAmt, Freight)
VALUES(
2
,3
,261
,1652
,4
,GETDATE()
,GETDATE()
,44594.55
,3567.564
,1114.8638);
GO
