网上商城项目数据库表
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
网上商城项目数据库表
*****************************************************
' 添加商品记录同步添加库存记录的触发器
'*****************************************************
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'AddGoodsInfo' AND type = 'TR') DROP TRIGGER AddGoodsInfo
GO
CREATE TRIGGER AddGoodsInfo ON [dbo].[GoodsInfo]
FOR INSERT
AS
INSERT INTO GoodsSave select GoodsID,GoodsName,0,0,getdate(),getdate() from inserted
GO
' 删除商品记录同步删除库存记录的触发器
'*****************************************************
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'DelGoodsInfo' AND type = 'TR') DROP TRIGGER DelGoodsInfo
GO
CREATE TRIGGER DelGoodsInfo ON [dbo].[GoodsInfo]
FOR DELETE
AS
DELETE from GoodsSave where GoodsID = (SELECT GoodsID FROM deleted)
GO
'*****************************************************
' 重新同步商品在库存表中的对应记录的存储过程
'*****************************************************
CREATE PROCEDURE BindGoodsToSave AS
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'GoodsSave' AND type = 'U') BEGIN
DROP TABLE GoodsSave
create table GoodsSave (
GoodsSaveID integer identity,
GoodsID integer null,
GoodsName nvarchar(100) null,
SaveNum integer null default 0,
SaveAddNum integer null default 0,
SaveTime datetime null default getdate(),
SaveAddTime datetime null default getdate(),
constraint PK_GOODSSAVE primary key (GoodsSaveID)
)
END
ELSE
BEGIN
create table GoodsSave (
GoodsSaveID integer identity,
GoodsID integer null,
GoodsName nvarchar(100) null,
SaveNum integer null default 0,
SaveAddNum integer null default 0,
SaveTime datetime null default getdate(),
SaveAddTime datetime null default getdate(),
constraint PK_GOODSSAVE primary key (GoodsSaveID)
)
END
INSERT INTO GoodsSave(GoodsID,GoodsName) select GoodsID,GoodsName from GoodsInfo order by GoodsID
'*****************************************************
' 按日期和月份统计注册人数的存储过程
'*****************************************************
CREATE procedure GetRegUsers
@id int
AS
declare @table table(ID int IDENTITY(1,1),RegDate nvarchar(10),RegNum int) declare @tempId int --临时表记录ID
declare @tempRegDate nvarchar(10) --临时表日期
set nocount on
if (@id = 0)
begin
insert into @table(RegDate)
select distinct convert(char(10),UserCreateTime,120) from Users order by convert(char(10),UserCreateTime,120) desc
declare _cursor cursor for select Id,RegDate from @table
open _cursor
fetch next from _cursor into @tempId,@tempRegDate
while @@fetch_status = 0
begin
update @table set RegNum=(select count(UserID) from Users where
convert(char(10),UserCreateTime,120) = @tempRegDate)
where ID = @tempId
fetch next from _cursor into @tempId,@tempRegDate
end
close _cursor
deallocate _cursor
end
else
begin
insert into @table(RegDate)
select distinct convert(char(7),UserCreateTime,120) from Users order by convert(char(7),UserCreateTime,120) desc
declare _cursor cursor for select Id,RegDate from @table
open _cursor
fetch next from _cursor into @tempId,@tempRegDate
while @@fetch_status = 0
begin
update @table set RegNum=(select count(UserID) from Users where
convert(char(7),UserCreateTime,120) = @tempRegDate)
where ID = @tempId
fetch next from _cursor into @tempId,@tempRegDate
end
close _cursor
deallocate _cursor