ch 16 - advanced server side code.sql

《SQLServer2000宝典》—包括本书中的所有代码

-----------------------------------------------------------
-- SQL Server 2000 Bible 
-- Hungry Minds 
-- Paul Nielsen

-- Chapter  16 - Adv Server Side Code

-----------------------------------------------------------
-----------------------------------------------------------

-- Server-Side code
----------------------------------------------------
-- AddNew Stored Procedure (already in the database)

USE OBXKites

CREATE PROCEDURE pProduct_AddNew(
  @ProductCategoryName NVARCHAR(50), 
  @Code CHAR(10),   
  @Name NVARCHAR(50),
  @ProductDescription NVARCHAR(100) = NULL
  )
AS
  SET NOCOUNT ON
  DECLARE 
    @ProductCategoryID UNIQUEIDENTIFIER

  SELECT @ProductCategoryID = ProductCategoryID 
    FROM dbo.ProductCategory
      WHERE ProductCategoryName = @ProductCategoryName
  IF @@Error <> 0 RETURN -100

  IF @ProductCategoryID IS NULL
    BEGIN
      RAISERROR ('Product Category: ''%s'' not found', 15,1,@ProductCategoryName)
      RETURN -100
    END

  INSERT dbo.Product (ProductCategoryID, Code, ProductName, ProductDescription)
    VALUES (@ProductCategoryID, @Code, @Name, @ProductDescription )
  IF @@Error <> 0 RETURN -100
  RETURN 0 
go

-- Test

EXEC pProduct_AddNew
  @ProductCategoryName = 'OBX',
  @Code = '999',   
  @Name = 'Test Kit',
  @ProductDescription = 'official kite testing kit for contests.'

EXEC pProduct_Fetch 999

SELECT ProductName, ProductCategoryName
  FROM dbo.Product
    JOIN dbo.ProductCategory
      ON Product.ProductCategoryID 
         = ProductCategory. ProductCategoryID
  WHERE Code = '999'

----------------------------------------------------
-- Fetch Stored Procedure (already in the database)


CREATE PROCEDURE pProduct_Fetch(
  @ProductCode CHAR(15) = NULL,
  @ProductCategory CHAR(15) = NULL ) 
AS
SET NoCount ON

SELECT Code, ProductName, ProductDescription, ActiveDate,
    DiscontinueDate, ProductCategoryName, [RowVersion] --,
--    Product.Created, Product.Modified  
  FROM dbo.Product
    JOIN dbo.ProductCategory
      ON Product.ProductCategoryID 
             = ProductCategory.ProductCategoryID
  WHERE ( Product.Code = @ProductCode 
                OR @ProductCode IS NULL ) 
    AND ( ProductCategory.ProductCategoryName = @ProductCategory
              OR @ProductCategory IS NULL ) 
  IF @@Error <> 0 RETURN -100

RETURN

-- Test

EXEC pProduct_Fetch 

EXEC pProduct_Fetch 
  @ProductCode = '1005'

EXEC pProduct_Fetch 
  @ProductCategory = 'Book'


----------------------------------------------------
-- Update Stored Procedure (already in the database)

CREATE PROCEDURE pProduct_Update_RowVersion (
  @Code CHAR(15), 
  @RowVersion Rowversion,
  @Name VARCHAR(50), 
  @ProductDescription VARCHAR(50), 
  @ActiveDate DateTime,
  @DiscontinueDate DateTime )
AS 
SET NoCount ON

UPDATE dbo.Product
  SET 
    ProductName = @Name,
    ProductDescription = @ProductDescription,
    ActiveDate = @ActiveDate,
    DiscontinueDate = @DiscontinueDate
  WHERE Code = @Code 
    AND [RowVersion] = @RowVersion 
  
  IF @@ROWCOUNT = 0 
    BEGIN
    IF EXISTS ( SELECT * FROM Product WHERE Code = @Code)
      BEGIN
        RAISERROR ('Product failed to update because 
           another transaction updated the row since your
           last read.', 16,1)
        RETURN -100
      END 
    ELSE 
      BEGIN
        RAISERROR ('Product failed to update because 
           the row has been deleted', 16,1)
        RETURN -100
      END
    END 
RETURN




EXEC pProduct_Fetch 1001


-- 
EXEC pProduct_Update_Rowversion 
  1001, 
  0x0000000000000077, -- replace with your rowversion value from the previous fetch procedure
  'updatetest', 
  'new description', 
  '1/1/2002', 
  NULL


-- Minimal Update 


CREATE PROCEDURE pProduct_Update_Minimal (
  @Code CHAR(15), 
  @Name VARCHAR(50) = NULL, 
  @ProductDescription VARCHAR(50) = NULL, 
  @ActiveDate DateTime = NULL,
  @DiscontinueDate DateTime = NULL )

AS 
SET NoCount ON

IF EXISTS (SELECT * FROM dbo.Product WHERE Code = @Code)
  BEGIN 
    BEGIN TRANSACTION
    IF @Name IS NOT NULL
      BEGIN
        UPDATE dbo.Product
          SET 
            ProductName = @Name
          WHERE Code = @Code
        IF @@Error <> 0 
          BEGIN
            ROLLBACK
            RETURN -100
          END
      END 

    IF @ProductDescription IS NOT NULL
      BEGIN
        UPDATE dbo.Product
          SET 
            ProductDescription = @ProductDescription
          WHERE Code = @Code 
        IF @@Error <> 0 
          BEGIN
            ROLLBACK
            RETURN -100
          END
      END 
 
    IF @ActiveDate IS NOT NULL
      BEGIN
        UPDATE dbo.Product
          SET 
            ActiveDate = @ActiveDate
          WHERE Code = @Code 
        IF @@Error <> 0 
          BEGIN
            ROLLBACK
            RETURN -100
          END
      END 

    IF @DiscontinueDate IS NOT NULL
      BEGIN
        UPDATE dbo.Product
          SET 
            DiscontinueDate = @DiscontinueDate
          WHERE Code = @Code
        IF @@Error <> 0 
          BEGIN
            ROLLBACK
            RETURN -100
          END
      END
    COMMIT TRANSACTION
  END
ELSE 
  BEGIN
    RAISERROR 
      ('Product failed to update because the row has 
          been deleted', 16,1)
    RETURN -100
  END
RETURN 

-- Test

EXEC pProduct_Update_Minimal 
  @Code = '1001', 
  @ProductDescription = 'a minimal update'

EXEC pProduct_Fetch 1001


----------------------------------------------------
-- Delete Stored Procedure (already in the database)

CREATE PROCEDURE pProduct_Delete(
  @ProductCode INT
)
AS
  SET NOCOUNT ON
  DECLARE @ProductID UniqueIdentifier

  SELECT @ProductID = ProductID 
    FROM Product
    WHERE Code = @ProductCode
  If @@RowCount = 0 
    BEGIN
      RAISERROR  
        ('Unable to delete Product Code %i - does not exist.', 16,1, @ProductCode)
      RETURN
    END
  ELSE
    DELETE dbo.Product
      WHERE ProductID = @ProductID
RETURN

-- Test
EXEC pProduct_Delete 99



--------------------------------------------------------
-- Complex Business Rules Validation

-- ensure the guide's qual date is good and the revoke date is null
-- for the given guideID and TourID

USE CHA2
go
CREATE TRIGGER LeadQualied ON Event_mm_Guide
AFTER INSERT, UPDATE
AS 
SET NoCount ON
IF EXISTS(
  SELECT *  
    FROM Inserted
      JOIN dbo.Event 
        ON Inserted.EventID = Event.EventID
      LEFT JOIN dbo.Tour_mm_Guide
        ON Tour_mm_Guide.TourID = Event.TourID
        AND Inserted.GuideID = Tour_mm_Guide.GuideID
    WHERE
       Inserted.IsLead = 1  
       AND 
          (QualDate > Event.DateBegin 
        OR   
           RevokeDate IS NOT NULL
        OR 
           QualDate IS NULL )
        )
  BEGIN 
    RAISERROR('Lead Guide is not Qualified.',16,1)
    ROLLBACK TRANSACTION
  END
go


-- test 

INSERT Event_mm_Guide (EventID, GuideID, IsLead)
  VALUES (10, 1, 1)

INSERT Event_mm_Guide (EventID, GuideID, IsLead)
  VALUES (10, 2, 1)





---------------------------------------------------------
-- Custom Referential Integrity

CREATE TRIGGER RICheck ON Tour
AFTER INSERT, UPDATE
AS 
SET NoCount ON
IF Exists(SELECT * 
            FROM Inserted 
              LEFT OUTER JOIN BaseCamp
                ON Inserted.BaseCampID 
                   = BaseCamp.BaseCampID
            WHERE BaseCamp.BaseCampID IS NULL)
  BEGIN
    RAISERROR
      ('Inappropriate Foreign Key: Tour.BaseCampID', 16, 1)
    ROLLBACK TRANSACTION
    RETURN
  END

UPDATE Tour 
  SET BaseCampID = 99
  WHERE TourID = 1


-- note this trigger does not apply to any of the sample databases:
CREATE TRIGGER AllocationCheck ON Allocation
AFTER INSERT, UPDATE
AS
SET NoCount ON
-- Check For invalid Inventory Item
IF Exists(SELECT * 
            FROM Inserted I
              LEFT OUTER JOIN Inventory
                ON I.SourceID = Inventory.InventoryID
              LEFT OUTER JOIN PurchaseOrderDetail
                ON I.SourceID = PurchaseOrderDetail.PODID
            WHERE Inventory.InventoryID IS NULL 
              AND PurchaseOrderDetail.PODID IS NULL) 
  BEGIN
    RAISERROR
      ('Invalid product allocation source', 16, 1)
    ROLLBACK TRANSACTION
    RETURN
  END


ALTER TABLE Allocation
  ADD CONSTRAINT AllocationSourceExclusive CHECK 
    (PurchaseOrderID IS NULL AND InventoryID IS NOT NULL)
      OR 
    (PurchaseOrderID IS NOT NULL AND InventoryID IS NULL)



----------------------------------------------------------
----------------------------------------------------------
-- Row-Level Custom Security 

USE OBXKites

-- the security table and the constraints

DROP TABLE dbo.Security

CREATE TABLE dbo.Security (
  SecurityID UniqueIdentifier NOT NULL
    Primary Key NonClustered DEFAULT NewID(),
  ContactID UniqueIdentifier NOT NULL 
    REFERENCES Contact ON DELETE CASCADE,  
  LocationID UniqueIdentifier NOT NULL 
    REFERENCES Location ON DELETE CASCADE,
  SecurityLevel INT NOT NULL DEFAULT 0 
  )
go
 
-- Constraints

CREATE TRIGGER ContactID_RI ON dbo.Security
AFTER INSERT, UPDATE
AS 
SET NoCount ON
IF EXISTS(SELECT * 
            FROM Inserted 
              LEFT OUTER JOIN dbo.Contact
                ON Inserted.ContactID = Contact.ContactID
            WHERE Contact.ContactID IS NULL
              OR Contact.IsEmployee = 0 )
  BEGIN
    RAISERROR
      ('Foreign Key Constraint: Security.ContactID', 16, 1)
    ROLLBACK TRANSACTION
    RETURN
  END
go
ALTER TABLE dbo.Security
  ADD CONSTRAINT ValidSecurityCode CHECK 
    (SecurityLevel IN (0,1,2,3))
go
ALTER TABLE dbo.Security 
  ADD CONSTRAINT ContactLocation UNIQUE
    (ContactID, LocationID) 
go
-- Security table sprocs

--------------------------------------
-- Security Fetch 

USE OBXKites

CREATE PROCEDURE pSecurity_Fetch(
  @LocationCode CHAR(15) = NULL,
  @ContactCode CHAR(15) = NULL ) 
AS 
SET NoCount ON
  SELECT Contact.ContactCode, Location.LocationCode, SecurityLevel
    FROM dbo.Security
      JOIN dbo.Contact
        ON Security.ContactID = Contact.ContactID
      JOIN dbo.Location
        ON Security.LocationID = Location.LocationID
          WHERE (Location.LocationCode = @LocationCode
                        OR @LocationCode IS NULL)
            AND (Contact.ContactCode = @ContactCode
                        OR @ContactCode IS NULL)
  IF @@ERROR <> 0 RETURN -100
  RETURN
go

---------
CREATE PROCEDURE pSecurity_Assign(
  @ContactCode VARCHAR(15),
  @LocationCode VARCHAR(15), 
  @SecurityLevel INT
  )
AS
  SET NOCOUNT ON
  DECLARE 
    @ContactID UNIQUEIDENTIFIER,
    @LocationID UNIQUEIDENTIFIER

  -- Get ContactID
  SELECT @ContactID = ContactID 
    FROM dbo.Contact
    WHERE ContactCode = @ContactCode
  IF @@ERROR <> 0 RETURN -100
  IF @ContactID IS NULL
    BEGIN
      RAISERROR ('Contact: ''%s'' not found', 15,1,@ContactCode)
      RETURN -100
    END

  -- Get LocationID
  SELECT @LocationID = LocationID 
    FROM dbo.Location
    WHERE LocationCode = @LocationCode
  IF @@ERROR <> 0 RETURN -100
  IF @LocationID IS NULL
    BEGIN
      RAISERROR ('Location: ''%s'' not found', 15,1,@LocationCode)
      RETURN -100
    END
  
  -- Insert
  INSERT dbo.Security (ContactID,LocationID, SecurityLevel)
    VALUES (@ContactID, @LocationID, @SecurityLevel) 
  IF @@ERROR <> 0 RETURN -100
  RETURN

go

-------------
-- Test Security insert

SELECT ContactCode 
  FROM dbo.Contact 
  WHERE IsEmployee = 1

SELECT LocationCode 
  FROM dbo.Location

EXEC pSecurity_Assign 
  @ContactCode = 118, 
  @LocationCode = CH,
  @SecurityLevel = 3

EXEC pSecurity_Assign 
  @ContactCode = 118, 
  @LocationCode = Clt,
  @SecurityLevel = 2

EXEC pSecurity_Assign 
  @ContactCode = 118, 
  @LocationCode = Elc,
  @SecurityLevel = 1

EXEC pSecurity_Assign 
  @ContactCode = 120, 
  @LocationCode = W,
  @SecurityLevel = 2  

SELECT * FROM Contact WHERE ContactCode = 118

EXEC pSecurity_Fetch @LocationCode = 'W'

EXEC pSecurity_Fetch @ContactCode = '118'

SELECT * 
  FROM Sdbo.ecurity

-- Test Constrants
-- unique
EXEC pSecurity_Assign 
  @ContactCode = 120, 
  @LocationCode = W,
  @SecurityLevel = 2 

-- invalid security code
EXEC pSecurity_Assign 
  @ContactCode = 118, 
  @LocationCode = W,
  @SecurityLevel = 5 

-- non employee
Select ContactCode 
  FROM dbo.Contact 
  WHERE IsEmployee = 0 

EXEC pSecurity_Assign 
  @ContactCode = 102, 
  @LocationCode = W,
  @SecurityLevel = 3 

-- invalid contact
EXEC pSecurity_Assign 
  @ContactCode = 999, 
  @LocationCode = W,
  @SecurityLevel = 3 

-- invalid location
EXEC pSecurity_Assign 
  @ContactCode = 118, 
  @LocationCode = RDBMS,
  @SecurityLevel = 3 

--------------------------------------------------------
-- ALter SPROC so it updates currect security assignment
ALTER PROCEDURE pSecurity_Assign(
  @ContactCode CHAR(15),
  @LocationCode CHAR(15), 
  @SecurityLevel INT
  )
AS
  SET NOCOUNT ON
  DECLARE 
    @ContactID UNIQUEIDENTIFIER,
    @LocationID UNIQUEIDENTIFIER
  -- Get ContactID
  SELECT @ContactID = ContactID 
    FROM dbo.Contact
    WHERE ContactCode = @ContactCode
  IF @ContactID IS NULL
    BEGIN
      RAISERROR 
        ('Contact: ''%s'' not found', 15,1,@ContactCode)
      RETURN -100
    END
  -- Get LocationID
  SELECT @LocationID = LocationID 
    FROM dbo.Location
    WHERE LocationCode = @LocationCode
  IF @LocationID IS NULL
    BEGIN
      RAISERROR 
       ('Location: ''%s'' not found', 15,1,@LocationCode)
      RETURN -100
    END
  -- IS Update or Insert? 
  IF EXISTS(SELECT * 
             FROM dbo.Security 
             WHERE ContactID = @ContactID 
               AND LocationID = @LocationID)
  -- Update
    BEGIN
      UPDATE dbo.Security