数据库文件.sql

这是一个加油站管理系统

-- 创建数据库

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'GasStation')
	DROP DATABASE [GasStation]
GO

CREATE DATABASE [GasStation]  
ON (
	NAME = N'GasStation_Data', 
	FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL\data\GasStation_Data.MDF' , 
	SIZE = 1, 
	FILEGROWTH = 10%) 
LOG ON (
	NAME = N'GasStation_Log', 
	FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL\data\GasStation_Log.LDF' , 
	SIZE = 1, 
	FILEGROWTH = 10%)
COLLATE Chinese_PRC_CI_AS
GO

exec sp_dboption N'GasStation', N'autoclose', N'true'
GO

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

-- 创建表
use [GasStation]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Worker]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Worker]
GO

CREATE TABLE [dbo].[Worker] (
	number 		smallint PRIMARY KEY,
	name 		varchar(25) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	id 		varchar(18) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	type		tinyint,
	password	varchar(12)
) 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] (
	number 		smallint PRIMARY KEY,
	name	 	varchar(25) COLLATE Chinese_PRC_CI_AS ,
	location 	varchar(25) COLLATE Chinese_PRC_CI_AS ,
	capacity 	float
) ON [PRIMARY]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GasType]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[GasType]
GO

CREATE TABLE [dbo].[GasType] (
	number 		smallint PRIMARY KEY,
	name	 	varchar(25) COLLATE Chinese_PRC_CI_AS ,
	price		float
) 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] (
	gas_type	smallint NOT NULL,
	depot		smallint NOT NULL,
	amount		float
) ON [PRIMARY]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MemberLevel]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[MemberLevel]
GO

CREATE TABLE [dbo].[MemberLevel] (
	number 		smallint PRIMARY KEY,
	name	 	varchar(25) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	agio 		float,
	expenditure	float
) ON [PRIMARY]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Member]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Member]
GO

CREATE TABLE [dbo].[Member] (
	number 		smallint PRIMARY KEY,
	name	 	varchar(25) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	id	 	varchar(18) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	car_number 	varchar(10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	expenditure		float
) ON [PRIMARY]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[StockRecord]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[StockRecord]
GO

CREATE TABLE [dbo].[StockRecord] (
	time 		datetime PRIMARY KEY ,
	gas_type	smallint ,
	depot	 	smallint ,
	amount		float ,
	price		float ,
	worker		smallint
) ON [PRIMARY]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SellRecord]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SellRecord]
GO

CREATE TABLE [dbo].[SellRecord] (
	time 		datetime PRIMARY KEY ,
	gas_type	smallint ,
	depot	 	smallint ,
	amount		float ,
	price		float ,
	worker		smallint ,
	client		smallint ,
	agio		float	
) ON [PRIMARY]
GO

-- 创建触发器
use [GasStation]

IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'ReserveNormalClient')
   DROP TRIGGER ReserveNormalClient
GO

Create Trigger ReserveNormalClient
On MemberLevel
After Delete, Update
As
if not exists(select number from MemberLevel where number = 1)
Begin
	Insert InTo MemberLevel Values(1, '普通会员', 1.0, 0)
End
Else
Begin
	Update MemberLevel set name = '', agio = 1.0, expenditure = 0 where number = 1
End


IF EXISTS (SELECT name FROM sysobjects WHERE name = 'AutoInsertStorage')
   DROP TRIGGER AutoInsertStorage
GO

Create Trigger AutoInsertStorage
On StockRecord
After Insert
As
Declare @gas_type as smallint
Declare	@depot as smallint
Declare	@amount	as float
Declare	@cur_amount as float
select @gas_type = gas_type from inserted
select @depot = depot from inserted
select @amount = amount from inserted
if not exists(select amount from Storage where gas_type = @gas_type and depot = @depot)
Begin
	Insert into Storage values(@gas_type, @depot, @amount)
End
Else
Begin
	select @cur_amount = amount from Storage where gas_type = @gas_type and depot = @depot
	Update Storage set amount = (@amount+@cur_amount) where gas_type = @gas_type and depot = @depot 
End


IF EXISTS (SELECT name FROM sysobjects WHERE name = 'AutoProcessSell')
   DROP TRIGGER AutoProcessSell
GO

Create Trigger AutoProcessSell
On SellRecord
After Insert
As

Declare @gas_type as smallint
Declare	@depot as smallint
Declare	@amount	as float
Declare @client as smallint
Declare	@cur_amount as float
Declare @cur_exp as float
Declare @price as float
Declare @agio as float

select @gas_type = gas_type from inserted
select @depot = depot from inserted
select @amount = amount from inserted

select @cur_amount = amount from Storage where gas_type = @gas_type and depot = @depot
Update Storage set amount = (@cur_amount-@amount) where gas_type = @gas_type and depot = @depot

select @price = price from inserted
select @agio = agio from inserted
select @client = client from inserted
if exists(select number from Member where number = @client)
Begin
	Select @cur_exp = expenditure from Member where number = @client
	Update Member set expenditure = (@cur_exp+@amount*@price*@agio) where number = @client
End
GO

-- 创建用户


INSERT INTO Worker Values(10, 'Seller', '000000000000000', 0, '20026233')
INSERT INTO Worker Values(11, 'Buyer', '111111111111111', 1, '20026233')
INSERT INTO Worker Values(12, 'Accounter', '222222222222222', 2, '20026233')
INSERT INTO Worker Values(13, 'Manager', '333333333333333', 3, '20026233')