Джеффри Мак-Манус - Обработка баз данных на Visual Basic®.NET

[OrderDate] [datetime] NULL,

[Amount] [money] NULL

) ON [PRIMARY]

GO

/****** object: Table [dbo].[tblOrderItem] Script Date: 10-Jul-02 12:41:12 PM ******/

CREATE TABLE] (

[ID] [int] IDENTITY (1, 1) NOT NULL, [OrderID] [int] NOT NULL,

[int] NOT NULL, [Quantity] [int] NULL,

[Cost] [money] NULL

) ON [PRIMARY]

GO

/******* object: Table [dbo].[tblRegion] Script Date: 10-Jul -02 12:41:12 PM *******/

CREATE TABLE [dbo]. [tblRegion] (

[ID] [int] IDENTITY (1, 1) NOT NULL,

[State] [char] (2) COLLATE Latin1_General_CI_AI NOT NULL,

[RegionName] [varchar] (25) COLLATE Latin1_General_CI_AI NULL

) ON [PRIMARY]

GO

Далее следует создать ограничения, как показано в листинге 3.5.

ALTER TABLE [dbo].[tblCustomer] WITH NOCHECK ADD

CONSTRAINT [PK_tblCustomer] PRIMARY KEY CLUSTERED (

[ID]

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[tblDepartment ]WITH NOCHECK ADD

CONSTRAINT [tblDepartment_IDPK] PRIMARY KEY CLUSTERED (

[ID]

) ON [PRIMARY]

GO

ALTER TABLE [dbo]. [tblEmployee] WITH NOCHECK ADD

CONSTRAINT [PK_tblEmployee] PRIMARY KEY CLUSTERED (

[ID]

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[tblInventory] WITH NOCHECK ADD

CONSTRAINT [PK_tblInventory] PRIMARY KEY CLUSTERED (

[ID]

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[tblOrder] WITH NOCHECK ADD

CONSTRAINT [PK_tblOrder] PRIMARY KEY CLUSTERED (

[ID]

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[tblOrderltem] WITH NOCHECK ADD

CONSTRAINT [PK_tblOrderItem] PRIMARY KEY CLUSTERED (

[ID]

) ON [PRIMARY]

GO

ALTER TABLE [dbo]. [tblRegion] WITH NOCHECK ADD

CONSTRAINT [PK_tblRegion] PRIMARY KEY CLUSTERED (

[ID]

) ON [PRIMARY]

GO

CREATE UNIQUE INDEX [IX_tblRegion] ON [dbo]. [tblRegion] ([State]) ON [PRIMARY]

GO

ALTER TABLE [dbo].[tblCustomer] ADD

CONSTRAINT [FK_tblCustomer_tblRegion] FOREIGN KEY ([State])

references [dbo].[tblRegion] (

[State]

) ON DELETE CASCADE ON UPDATE CASCADE

GO

ALTER TABLE [dbo].[tblEmployee] ADD

CONSTRAINT [FK_tblEmployee_tblDepartment] FOREIGN KEY ([DepartmentID])

REFERENCES [dbo].[tblDepartment] (

[ID]

) ON DELETE CASCADE ON UPDATE CASCADE

GO

ALTER TABLE [dbo]. [tblOrder] ADD

CONSTRAINT [FK_tblOrder_tblCustomer] FOREIGN KEY ( [CustomerID])

REFERENCES [dbo].[tblCustomer] ([ID]) ON DELETE CASCADE ON UPDATE CASCADE

GO

ALTER TABLE [dbo].[tblOrderItem] ADD

CONSTRAINT [FK_tblOrderItem_tblInventory] FOREIGN KEY ([ItemID])

REFERENCES [dbo].[tblInventory] ([ID])

ON DELETE CASCADE ON UPDATE CASCADE,

constraint [FK_tblOrderItem_tblOrder] foreign key ([OrderID])

REFERENCES [dbo].[tblOrder] ([ID])

ON DELETE CASCADE ON UPDATE CASCADE

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

Наконец, для создания представлений, хранимых процедур и триггеров следует выполнить сценарий из листинга 3.6.

/****** object: View dbo.EmployeeDepartment_view Script Date: 10-Jul-02 12:41:13 PM ******/

CREATE view EmployeeDepartment_view as

select e.ID, FirstName, LastName, DepartmentName

from tblEmployee e, tblDepartment t

where e.DepartmentID = t.ID

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

/****** Object: View dbo.qryEmployee_view Script Date: 10-Jul-02 12:41:13 PM ******/

create view qryEmployee_view as

SELECT ID, FirstName, LastName, DepartmentID from tblEmployee

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

/****** Object: Stored Procedure dbo.DeleteEmployee Script Date: 10-Jul-02 12:41:13 PM ******/

CREATE PROCEDURE dbo.DeleteEmployee (@Original_ID int)

AS

SET NOCOUNT OFF;

DELETE FROM tblEmployee WHERE (ID = @Original_ID)

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

/****** Object: Stored Procedure dbo.GetCustomerFromID Script Date: 10-Jul-02 12:41:13 PM ******/

create procedure GetCustomerFromID @custID int

as

select * from tblCustomer where ID = @custID

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS OFF

GO

/****** Object: Stored Procedure Script Date: 10-Jul-02 12:41:13 PM ******/

CREATE PROCEDURE dbo.InsertEmployee (

@FirstName varchar(50),

@LastName varchar(70),

@DepartmentID int,

@Salary money)

AS

SET NOCOUNT OFF;

if (@Salary = 0 or @Salary is null) begin

-– Do complicated salary calculations

set @Salary = @DepartmentID * 10000

end

INSERT INTO tblEmployee(FirstName, LastName, Salary) VALUES

(@FirstName, @LastName, @DepartmentID, @Salary)

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

/****** Object: Stored Procedure dbo.InsertEmployeeOrg Script Date: 10-Jul-02 12:41:13 PM ******/