leasedbsql.sql~rf31c6cb4.tmp

个人版音像管理系统,可以扩展,内含数据库,支持多数据库使用

use master 
if exists(select * from sysdatabases where name='LeaseDB')
  drop database leaseDB
GO

create database leaseDB
on primary
(
	name='leaseDB_mdf',
	filename='d:\leaseDB.mdf'
)
log on
(
	name='leaseDB_ldf',
	filename='d:\leaseDB.ldf'
)
GO

use leaseDB
if exists(select * from sysObjects where name='DiscInfo')
  drop table discInfo

create table discInfo
(
	DiscID int primary key identity(1,1),
	DiscName varchar(30) not null,
	Author varchar(20),
	Director varchar(20),
	SimpleInfo varchar(200),
	TID int not null,
	DiscState int not null check(DIscState = 0 or DiscState = 1) default(1)
)
GO

if exists(select * from sysobjects where name='DiscType')
  drop table DiscType

create table DiscType
(
	TypeID int primary key identity(1,1),
	TypeName varchar(20) not null unique
)
GO

if exists(select * from sysobjects where name='MemberInfo')
  drop table MemberInfo

create table MemberInfo
(
	MemberID varchar(10) primary key,
	MemberName varchar(20) not null,
	MemberPhone varchar(15),
	MemberAddress varchar(50),
	MemberState int not null check(MemberState = 0 or MemberState = 1) default(1),
	LID int not null
)
GO

if exists(select * from sysObjects where name='MemberLevel')
  drop table MemberLevel

create table MemberLevel
(
	LevelID int primary key identity(1,1),
	LevelName varchar(20) not null unique,
	DiscCount int not null check(DiscCount = 3 or DiscCount = 5)
)
GO

if exists(select * from sysObjects where name = 'LeaseInfo')
  drop table LeaseInfo

create table LeaseInfo
(
	LeaseID int primary key identity(1,1),
	MID varchar(10) not null,
	DID int not null,
	LeaseDate datetime not null default(getdate()),
	ReturnDate datetime not null default(getdate())
)
GO

alter table DiscInfo
add constraint FK_TID foreign key(TID) references DiscType(TypeID)

alter table MemberInfo
add constraint FK_LID foreign key(LID) references MemberLevel(LevelID)

alter table LeaseInfo
add constraint FK_DID foreign key(DID) references DiscInfo(DiscID)

alter table LeaseInfo
add constraint FK_MID foreign key(MID) references MemberInfo(MemberID)

alter table LeaseInfo
add constraint CK_RE_LE check(returnDate > LeaseDate)







--存储过程
--leaseInfo 表
if exists(select * from sysObjects where name = 'proc_insert_LeaseInfo')
  drop proc proc_insert_LeaseInfo
GO

create proc proc_insert_LeaseInfo
@MemberName varchar(20),
@DiscName varchar(30),
@MID int,
@DID int
as
	select @MID=MemberID from MemberInfo where MemberName = @MemberName
	select @DID=DiscID from DiscInfo where DiscName = @DiscName
	insert into LeaseInfo values(@MID,@DID,default,null)
GO


if exists (select * from sysObjects where name= 'proc_select_LeaseInfo')
 drop proc proc_select_LeaseInfo
GO
create proc proc_select_LeaseInfo
@where varchar(50)
as
	select (select MemberName from MemberInfo where MemberID = MID) as MemberName,
	  (select DiscName from DiscInfo where DiscID = DID) as DiscName,
	  LeaseDate,ReturnDate 
	from LeaseInfo 
	where MID in (select MemberID from MemberInfo where MemberName like '%'+@where+'%') or MID like '%'+@where+'%'
		or DID in (select DiscID from DiscInfo where DiscName like '%'+@where+'%') or DID like '%'+@where+'%'
GO

if exists(select * from sysobjects where name = 'proc_update_LeaseInfo')
  drop proc proc_update_LeaseInfo
GO
create proc proc_update_LeaseInfo
@LeaseID int
as
	update LeaseInfo set ReturnDate = default where LeaseID = @LeaseID
GO

--MemberInfo表
if exists(select * from sysObjects where name='proc_insert_MemberInfo')
  drop proc proc_insert_MemberInfo
Go
create proc proc_insert_MemberInfo
@MemberID varchar(10),
@MemberName varchar(20),
@MemberPhone varchar(15),
@MemberAddress varchar(50),
@LID int,
@LevelName varchar(20)
as 
	select @LID = LevelID from MemberLevel where LevelName = @LevelName
	insert into MemberInfo values(@MemberID,@MemberName,@MemberPhone,@MemberAddress,default,@LID)
GO

if exists(select * from sysobjects where name='proc_select_MemberInfo')
  drop proc proc_select_MemberInfo
GO
create proc proc_select_MemberInfo
@where varchar(50)
as
	select MemberId,MemberName,MemberPhone,MemberAddress,
	(select LevelName from MemberLevel where LevelID = LID) as MemberLV,
	(select DiscCount from MemberLevel where LevelID = LID) as MaxCount,
	(select count(MID) as DiscCount from LeaseInfo where ReturnDate = null) 
	from MemberInfo 
	where MemberID like '%'+@where+'%' or MemberName like '%'+@where+'%'
		or MemberPhone like '%'+@where+'%' or MemberAddress like '%'+@where+'%'
		or LID in (select LevelID from MemberLevel where LevelName like '%'+@where+'%')
GO

if exists(select * from sysobjects where name='proc_update_MemberInfo')
  drop proc proc_update_MemberInfo
GO
create proc proc_update_MemberInfo
@MemberID varchar(10),
@MemberName varchar(20),
@MemberPhone varchar(15),
@MemberAddress varchar(50),
@MemberState int,
@LevelName varchar(20)
as
	update MemberInfo 
	set memberName = @MemberName, memberPhone = @MemberPhone,
		memberAddress = @MemberAddress,memberState = @MemberState,
		LID = (select LevelID from MemberLevel where LevelName = @LevelName)
	where MemberID = @MemberID
Go

--DiscInfo表
if exists(select * from sysobjects where name='proc_insert_DiscInfo')
  drop proc proc_insert_DiscInfo
GO
create proc proc_insert_DiscInfo
@DiscName varchar(30),
@Author varchar(20),
@Director varchar(20),
@SimpleInfo varchar(200),
@TID int,
@TypeName varchar(20)
as
	select @TID = TypeID from DiscType where TypeName = @TypeName

	insert into DiscInfo 
	values (@DiscName,@Author,@Director,@SimpleInfo,@TID,default)
GO

if exists(select * from sysobjects where name='proc_select_DiscInfo')
  drop proc proc_select_DiscInfo
GO
create proc proc_select_DiscInfo
@where varchar(50)
as 
	select DiscID,DiscName,Author,Director,SimpleInfo,
			(select TypeName from DiscType where TypeID = TID) as DiscTP,
			DiscState 
	from DiscInfo 
	where convert(char(10),DiscID) like '%'+@where+'%' or DiscID like '%'+@where+'%'
		or Author like '%'+@where+'%' or Director like '%'+@where+'%'
		or SimpleInfo like '%'+@where+'%' 
		or TID in (select TypeID from DiscType where TypeName like '%'+@where+'%')
GO

if exists(select * from sysObjects where name='proc_update_DiscInfo')
  drop proc proc_update_DiscInfo
GO
create proc proc_update_DiscInfo
@DiscName varchar(30),
@Author varchar(20),
@Director varchar(20),
@SimpleInfo varchar(200),
@TypeName varchar(20),
@DiscState int,
@DiscID int
as
	Update DiscInfo 
	set discName = @DiscName,Author =  @Author,
		Director = @Director,SimpleInfo =  @SimpleInfo,
		TID = (select TypeID from DiscType where TypeName = @TypeName),
		DiscState = @DiscState 
	where DiscID = @DiscID
GO

--触发器
--LeaseInfo表
if exists(select * from sysobjects where name = 'trig_insert_LeaseInfo')
  drop trigger trig_insert_LeaseInfo
GO
create trigger trig_insert_LeaseInfo
on LeaseInfo
for insert
as
declare @DiscID int
	select @DiscID = DID from inserted
	update DiscInfo set DiscState = 0 where DiscID = @DiscID
if @@error <> 0 
	rollback transaction

if exists(select * from sysObjects where name = 'trig_update_LeaseInfo')
  drop trigger trig_update_LeaseInfo
GO
create trigger trig_update_LeaseInfo
on LeaseInfo
for update
as 
declare @DiscID int,@ReturnDate datetime
	select @DiscID = DID,@ReturnDate = ReturnDate from updated
if(@ReturnDate != null)
	update DiscInfo set DiscState = 1 where DiscID = @DiscID
else
	rollback transaction
if @@error <> 0 
	rollback transaction
GO