存储过程和触发器.txt

这是一个SQL的物资管理系统 这是一个SQL的物资管理系统

AddGdzcDj(固定资产登记)
{
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER procedure [dbo].[AddGdzcDj]
@gdzcid int,
@wzid int,
@buydate varchar(50),
@buydepartment  varchar(50),
@buyman  varchar(50),
@storeplace  varchar(50),
@department  varchar(50),
@username  varchar(50),

@name  varchar(50),
@model varchar(50),
@spec varchar(50),
@card varchar(50),
@unitprice varchar(50),
@storedate varchar(50),
@repair varchar(50),
@state varchar(50),
@customerid int,
@customer varchar(50),
@address varchar(50),
@linkman varchar(50),
@phone varchar(50),
@str	  varchar(50)  output
as
set @str='Add Successfully!'
if((select count(*) from tb_Gdzc where gdzcid=@GdzcID)=0)
begin
	if((select count(*) from tb_GdzcDetails where gdzcid=@GdzcID)=0)
	begin	
		insert into tb_Gdzc(GdzcID,WzID,BuyDate,BuyDepartment,BuyMan,StorePlace,Department,userName)
		values(@gdzcid,@wzid,@buydate,@buydepartment,@buyman,@storeplace,@department,@username)
		
		insert into	tb_GdzcDetails(GdzcID,WzID,Name,Model,Spec,Card,UnitPrice,StoreDate,Repair,State,CustomerID,Customer,Address,LinkMan,Phone)
		values(@gdzcid,@wzid,@name,@model,@spec,@card,@unitprice,@storedate,@repair,@state,@customerid,@customer,@address,@linkman,@phone)
	end 
end 
else
	set @str='the UserName was existed!'


}


AddOut(新增登记出库)
{
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER procedure [dbo].[AddOut]
@OutID int,
@OutDate datetime,
@OutType varchar(50),
@OutPlace  varchar(50),
@OutMan  varchar(20),
@Sign  varchar(20),
@CustomerID  int,
@CustomerName  varchar(20),
@Address  varchar(50),
@LinkMan  varchar(50),
@Phone  varchar(50),
@UseDepartment  varchar(50),
@UserName  varchar(50),
@Remark varchar(200),

@WzID int,
@Name varchar(50),
@Model varchar(50),
@Spec varchar(50),
@Card varchar(50),
@Unit varchar(50),
@UnitPrice money,
@Amount int,
@Gathering money,
@str	varchar(50)  output
as
set @str='Add Successfully!'
if((select count(*) from tb_Out where OutID=@OutID)=0)
begin
	if((select count(*) from tb_OutDetails where OutID=@OutID)=0)
	begin	
		insert into tb_Out(OutID,WzID,OutDate,OutType,OutPlace,OutMan,Sign,CustomerID,CustomerName,Address,LinkMan,Phone,UseDepartment,UserName,Remark)
		values(@OutID,@WzID,@OutDate,@OutType,@OutPlace,@OutMan,@Sign,@CustomerID,@CustomerName,@Address,@LinkMan,@Phone,@UseDepartment,@UserName,@Remark)
		
		insert into	tb_OutDetails(OutID,WzID,Name,Model,Spec,Card,Unit,UnitPrice,Amount,Gathering)
		values(@OutID,@WzID,@Name,@Model,@Spec,@Card,@Unit,@UnitPrice,@Amount,@Gathering)
	end 
end 
else
	set @str='the OutID was existed!'


}


AddStore(新增登记入库)
{
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER procedure [dbo].[AddStore]
@StoreID int,
@StoreDate datetime,
@StoreType varchar(50),
@StorePlace  varchar(50),
@Sign  varchar(20),
@BuyDate  datetime,
@Department  varchar(50),
@BuyMan  varchar(20),
@Ratify  varchar(20),
@Remark varchar(200),

@WzID int,
@Name varchar(50),
@Model varchar(50),
@Spec varchar(50),
@Card varchar(50),
@UnitPrice money,
@Unit char(10),
@Amount int,
@Pay money,
@CustomerID int,
@CustomerName varchar(50),
@Address varchar(50),
@Phone varchar(50),
@LinkMan varchar(50),
@str	  varchar(50)  output
as
set @str='Add Successfully!'
if((select count(*) from tb_Store where StoreID=@StoreID)=0)
begin
	if((select count(*) from tb_StoreDetails where StoreID=@StoreID)=0)
	begin	
		insert into tb_Store(StoreID,StoreDate,StoreType,StorePlace,Sign,BuyDate,Department,BuyMan,Ratify,Remark)
		values(@StoreID,@StoreDate,@StoreType,@StorePlace,@Sign,@BuyDate,@Department,@BuyMan,@Ratify,@Remark)
		
		insert into	tb_StoreDetails(StoreID,WzID,Name,Model,Spec,Card,UnitPrice,Unit,Amount,Pay,CustomerID,CustomerName,Address,Phone,LinkMan)
		values(@StoreID,@WzID,@Name,@Model,@Spec,@Card,@UnitPrice,@Unit,@Amount,@Pay,@CustomerID,@CustomerName,@Address,@Phone,@LinkMan)
	end 
end 
else
	set @str='the StoreID was existed!'




}


AddUser(新增用户)
{
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER procedure [dbo].[AddUser]
@username varchar(50),
@password varchar(50),
@type	  varchar(50),
@str	  varchar(50)  output
as
set @str='Add Successfully!'
if((select count(*) from tb_UserInfo where @username=UserName and @type=Type)=0)
	insert into tb_UserInfo(UserName,Password,Type) values(@username,@password,@type)
else
	set @str='the UserName was existed!'


}

Log_Operation(插入日志表)
{
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER procedure [dbo].[Log_Operation]
@ID varchar(50),
@Op varchar(50)
as
insert into Log_OP(OP,OP_Time)
values('用户帐号为'+@ID+'的用户'+@Op,getdate())



}
QueryGdzc(固定资产查询)
{
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER procedure [dbo].[QueryGdzc]
@storeplace  varchar(50),
@department  varchar(50),
@username	varchar(50),
@buyman	varchar(50),
@customer	varchar(50)
as
declare @QueryStr varchar(1000)
set @QueryStr='select * from  tb_Gdzc,tb_GdzcDetails where tb_Gdzc.GdzcID=tb_GdzcDetails.GdzcID '
if(@storeplace!='')
	set @QueryStr=@QueryStr+' and StorePlace ='''+@storeplace+''''
if(@department!='')
	set @QueryStr=@QueryStr+' and Department ='''+@department+''''
if(@username!='')
	set @QueryStr=@QueryStr+' and username ='''+@username+''''
if(@buyman!='')
	set @QueryStr=@QueryStr+' and BuyMan ='''+@buyman+''''
if(@customer!='')
	set @QueryStr=@QueryStr+' and Customer ='''+@customer+''''

exec(@QueryStr)

}
QueryOut(登记出库查询)
{
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go




ALTER procedure [dbo].[QueryOut]
@OutID  varchar(50),
@OutPlace  varchar(50),
@UseDepartment	varchar(50),
@OutMan	varchar(50),
@Sign	varchar(50),
@CustomerName	varchar(50)
as
declare @QueryStr varchar(1000)
set @QueryStr='select * from  tb_Out,tb_OutDetails where tb_Out.OutID=tb_OutDetails.OutID '
if(@OutID!='')
	set @QueryStr=@QueryStr+' and tb_Out.OutID ='''+@OutID+''''

if(@OutPlace!='')
	set @QueryStr=@QueryStr+' and OutPlace ='''+@OutPlace+''''

if(@UseDepartment!='')
	set @QueryStr=@QueryStr+' and UseDepartment ='''+@UseDepartment+''''

if(@OutMan!='')
	set @QueryStr=@QueryStr+' and OutMan ='''+@OutMan+''''

if(@Sign!='')
	set @QueryStr=@QueryStr+' and Sign ='''+@Sign+''''

if(@CustomerName!='')
	set @QueryStr=@QueryStr+' and CustomerName ='''+@CustomerName+''''

exec(@QueryStr)




}
QueryStore(登记入库查询)
{
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go



ALTER procedure [dbo].[QueryStore]
@StoreID  varchar(50),
@StorePlace  varchar(50),
@BuyDepartment	varchar(50),
@BuyMan	varchar(50),
@Sign	varchar(50)
as
declare @QueryStr varchar(1000)
set @QueryStr='select * from  tb_Store,tb_StoreDetails where tb_Store.StoreID=tb_StoreDetails.StoreID '
if(@StoreID!='')
	set @QueryStr=@QueryStr+' and tb_Store.StoreID ='''+@StoreID+''''

if(@StorePlace!='')
	set @QueryStr=@QueryStr+' and StorePlace ='''+@StorePlace+''''

if(@BuyDepartment!='')
	set @QueryStr=@QueryStr+' and BuyDepartment ='''+@BuyDepartment+''''

if(@BuyMan!='')
	set @QueryStr=@QueryStr+' and BuyMan ='''+@BuyMan+''''

if(@Sign!='')
	set @QueryStr=@QueryStr+' and Sign ='''+@Sign+''''

exec(@QueryStr)




}


UpdatePassword(修改密码)
{
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go



ALTER procedure [dbo].[UpdatePassword]
@username varchar(50),
@password varchar(50),
@newpassword varchar(50),
@str	varchar(50)  output
as
set @str='Modify Successfully!'
if((select count(*) from tb_UserInfo where @username=UserName and @password=Password)=0)
	set @str='Wrong password!'
else
	update tb_UserInfo set Password=@newpassword where @username=UserName



}
UserLogin(用户登录)
{
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER procedure [dbo].[UserLogin]
@username varchar(50),
@password varchar(50),
@type	  varchar(50),
@str	  varchar(50)  output
as
set @str='login failed!'
if((select count(*) from tb_UserInfo where @username=UserName and @password=Password and @type=Type)>0)
	set @str='login successfully!'

}

Add_User(tb_UserInfo表的触发器)
{
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER TRIGGER [Add_User] 
ON [dbo].[tb_UserInfo] 
FOR insert 
AS 
declare @UserName varchar(200)
select  @UserName=UserName from inserted
insert into Log_OP(OP,OP_Time) values ('增加了用户名为'+@UserName+'的用户',getdate())

}
User_ChangePassWord(tb_UserInfo表的触发器)
{
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER TRIGGER [User_ChangePassWord] 
ON [dbo].[tb_UserInfo] 
FOR update 
AS 
declare @UserName varchar(200)
select  @UserName=UserName from inserted
declare @Old varchar(200)
select  @Old=Password from deleted
declare @New varchar(200)
select  @New=Password from inserted
insert into Log_OP(OP,OP_Time) values ('用户名为'+@UserName+'的用户将原密码'+@Old+'改为'+@New,getdate())

}

Addtb_Store(tb_Store表的触发器)
{
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER TRIGGER [Addtb_Store]
on [dbo].[tb_Store]
FOR insert 
as
declare @StoreID varchar(200)
select  @StoreID=StoreID from inserted
insert into Log_OP(OP,OP_Time) values ('增加了StoreID为'+@StoreID+'的登记入库单',getdate())

}

Addtb_Out(tb_Out表的触发器)
{
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER TRIGGER [Addtb_Out]
on [dbo].[tb_Out]
FOR insert 
as
declare @OutID varchar(200)
select  @OutID=OutID from inserted
insert into Log_OP(OP,OP_Time) values ('增加了OutID为'+@OutID+'的登记出库单',getdate())


}