communitymanager数据库.txt

这个是一个商务管理系统,包含了进销存,全国各地的定位系统

//如果已经存在名为CommodityManager的数据库，则将其删除

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name=N'CommodityManager')
	DROP DATABASE [CommodityManager]
GO//创建数据库
CREATE DATABASE [CommodityManager]  
ON ('数据库文件逻辑名称'
	NAME = N'CommodityManager_Data', 
    '数据库文件系统路径'
	FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL\data\CommodityManager_Data.MDF' , 
	SIZE = 1, 
	FILEGROWTH = 10%) 
LOG ON ('LOG文件逻辑名称'
	NAME = N'CommodityManager_Log', 
        'LOG文件系统路径'
	FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL\data\CommodityManager_Log.LDF' , 
	SIZE = 1, 
	FILEGROWTH = 10%)
COLLATE Chinese_PRC_CI_AS
GO
'设置数据库的各种属性'
exec sp_dboption N'CommodityManager', N'autoclose', N'true'
GO

exec sp_dboption N'CommodityManager', N'bulkcopy', N'false'
GO

exec sp_dboption N'CommodityManager', N'trunc. log', N'true'
GO

exec sp_dboption N'CommodityManager', N'torn page detection', N'true'
GO

exec sp_dboption N'CommodityManager', N'read only', N'false'
GO

exec sp_dboption N'CommodityManager', N'dbo use', N'false'
GO

exec sp_dboption N'CommodityManager', N'single', N'false'
GO

exec sp_dboption N'CommodityManager', N'autoshrink', N'true'
GO

exec sp_dboption N'CommodityManager', N'ANSI null default', N'false'
GO

exec sp_dboption N'CommodityManager', N'recursive triggers', N'false'
GO

exec sp_dboption N'CommodityManager', N'ANSI nulls', N'false'
GO

exec sp_dboption N'CommodityManager', N'concat null yields null', N'false'
GO

exec sp_dboption N'CommodityManager', N'cursor close on commit', N'false'
GO

exec sp_dboption N'CommodityManager', N'default to local cursor', N'false'
GO

exec sp_dboption N'CommodityManager', N'quoted identifier', N'false'
GO

exec sp_dboption N'CommodityManager', N'ANSI warnings', N'false'
GO

exec sp_dboption N'CommodityManager', N'auto create statistics', N'true'
GO

exec sp_dboption N'CommodityManager', N'auto update statistics', N'true'
GO

//使用CommodityManager数据库
use [CommodityManager]
GO
//创建系统用户信息表
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SysUser]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SysUser]
GO

CREATE TABLE [dbo].[SysUser] (
	ID 		char(2) PRIMARY KEY ,
	name 		varchar(25) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	password	varchar(18) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	type		tinyint,
	personal_id	varchar(18) COLLATE Chinese_PRC_CI_AS ,
	email		varchar(25) COLLATE Chinese_PRC_CI_AS ,
	address		varchar(25) COLLATE Chinese_PRC_CI_AS 
) ON [PRIMARY]
GO
//创建国家名称表
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Nation]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Nation]
GO

CREATE TABLE [dbo].[Nation] (
	ID 		char(2) PRIMARY KEY ,
	name 		varchar(25) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GO
//创建地区名称表
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Area]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Area]
GO

CREATE TABLE [dbo].[Area] (
	ID 		char(2) PRIMARY KEY ,
	name 		varchar(25) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GO
//创建仓库名称表
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Depot]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Depot]
GO

CREATE TABLE [dbo].[Depot] (
	ID 		char(2) PRIMARY KEY ,
	name 		varchar(25) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GO
//创建类别名称表
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sort]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Sort]
GO

CREATE TABLE [dbo].[Sort] (
	ID 		char(2) PRIMARY KEY ,
	name 		varchar(25) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GO
//创建行业名称表
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Trade]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Trade]
GO

CREATE TABLE [dbo].[Trade] (
	ID 		char(2) PRIMARY KEY ,
	name 		varchar(25) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GO
//创建付款方式表
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Payment]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Payment]
GO

CREATE TABLE [dbo].[Payment] (
	ID 		char(2) PRIMARY KEY ,
	name 		varchar(25) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GO
//创建送货方式表
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Deliver]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Deliver]
GO

CREATE TABLE [dbo].[Deliver] (
	ID 		char(2) PRIMARY KEY ,
	name 		varchar(25) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GO
//创建商品信息表
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Commodity]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Commodity]
GO

CREATE TABLE [dbo].[Commodity] (
	ID 		char(2) PRIMARY KEY ,
	name 		varchar(25) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	sort		char(2) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	trade		char(2) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	picture		varchar(100) COLLATE Chinese_PRC_CI_AS 
) ON [PRIMARY]
GO
//创建供应商信息表
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Provider]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Provider]
GO

CREATE TABLE [dbo].[Provider] (
	ID 		char(2) PRIMARY KEY ,
	name 		varchar(25) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	nation		char(2) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	area		char(2) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	contact		varchar(25) COLLATE Chinese_PRC_CI_AS ,
	tel		varchar(10) COLLATE Chinese_PRC_CI_AS ,
	email		varchar(25) COLLATE Chinese_PRC_CI_AS 
) ON [PRIMARY]
GO
//创建客户信息表
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Customer]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Customer]
GO

CREATE TABLE [dbo].[Customer] (
	ID 		char(2) PRIMARY KEY ,
	name 		varchar(25) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	nation		char(2) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	area		char(2) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	contact		varchar(25) COLLATE Chinese_PRC_CI_AS ,
	tel		varchar(10) COLLATE Chinese_PRC_CI_AS ,
	email		varchar(25) COLLATE Chinese_PRC_CI_AS 
) ON [PRIMARY]
GO
//创建进货单据表
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[StoreBill]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[StoreBill]
GO

CREATE TABLE [dbo].[StoreBill] (
	time 		datetime ,
	commodity	char(2) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	amount		int NOT NULL,
	sysuser		char(2) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	depot		char(2) COLLATE Chinese_PRC_CI_AS NOT NULL ,		
	provider	char(2) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	deliver		char(2) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	note		varchar(50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	confirmed	bit
) ON [PRIMARY]
GO
//创建销售单据表
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SellBill]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SellBill]
GO

CREATE TABLE [dbo].[SellBill] (
	time 		datetime ,
	commodity	char(2) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	amount		int NOT NULL,
	sysuser		char(2) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	depot		char(2) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	customer	char(2) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	payment		char(2) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	note		varchar(50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	confirmed	bit
) ON [PRIMARY]
GO
//创建库存信息表
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Storage]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Storage]
GO

CREATE TABLE [dbo].[Storage] (
	commodity	char(2) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	depot		char(2) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	amount		smallint
) ON [PRIMARY]
GO



//判断是否已有同名的触发器ReserveSuperManager

IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'ReserveSuperManager')
   DROP TRIGGER ReserveSuperManager
GO
//触发器
Create Trigger ReserveSuperManager
On SysUser
After Delete, Update
As
if not exists(select name from SysUser where ID = '00')
Begin
	Insert InTo SysUser Values('00', 'SuperManager', '999', 1, '000000000000000000', 'null@null', 'null')
End
Else
Begin
	Update SysUser set name = 'SuperManager' where ID = '00'
End
//判断是否已有同名的触发器AutoProcessStore
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'AutoProcessStore')
   DROP TRIGGER AutoProcessStore
GO
//创建触发器
Create Trigger AutoProcessStore
On StoreBill
After UPDATE
As
Declare @commodity as char(2)
Declare	@depot as char(2)
Declare	@amount	as int
Declare	@cur_amount as int
select @commodity = commodity from inserted
select @depot = depot from inserted
select @amount = amount from inserted
if not exists(select amount from Storage where commodity = @commodity and depot = @depot)
Begin
	Insert into Storage values(@commodity, @depot, @amount)
End
Else
Begin
	select @cur_amount = amount from Storage where (commodity = @commodity) and (depot = @depot)
	Update Storage set amount = (@amount+@cur_amount) where commodity = @commodity and depot = @depot 
End

//判断是否已有同名的触发器AutoProcessSell
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'AutoProcessSell')
   DROP TRIGGER AutoProcessSell
GO
//创建触发器
Create Trigger AutoProcessSell
On SellBill
After UPDATE
As
Declare @commodity as char(2)
Declare	@depot as char(2)
Declare	@amount	as int
Declare	@cur_amount as int
select @commodity = commodity from inserted
select @depot = depot from inserted
select @amount = amount from inserted
if exists(select amount from Storage where commodity = @commodity and depot = @depot)
Begin
	select @cur_amount = amount from Storage where commodity = @commodity and depot = @depot
	if (@cur_amount - @amount <= 0)
	begin
		delete from Storage where (commodity = @commodity) and (depot = @depot)
	end 
	else
	begin
		Update Storage set amount = (@cur_amount-@amount) where commodity = @commodity and depot = @depot 
	end
End


//查询是否有同名过程

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[StorageQuery]') and type = 'P')
drop PROCEDURE [dbo].[StorageQuery]
GO
//创建过程
CREATE PROCEDURE StorageQuery 
   @commodity char(25), 
   @depot char(25) 
AS 
SELECT * FROM Storage
   	WHERE  commodity IN (SELECT ID from Commodity where name = @commodity)
		AND depot IN (SELECT ID from Depot where name = @depot)
GO

--创建用户

INSERT INTO SysUser Values('00', 'SuperManager', '999', 1, '000000000000000', 'null@null', 'null')