没写过存储过程。请教老师:
[进货单明细]
[编号] [varchar](10) NOT NULL,
[进货单号] [varchar](10) NOT NULL,
[货号] [varchar](10) NOT NULL,
[货名] [varchar](50) NOT NULL,
[数量] [decimal](26, 4) NOT NULL,
[单位] [char](2) NOT NULL,
[进价] [decimal](26, 4) NOT NULL,
[金额] [decimal](26, 4) NOT NULL
[销售单明细]
[编号] [varchar](10) NOT NULL,
[销售单号] [varchar](10) NOT NULL,
[货号] [varchar](10) NOT NULL,
[货名] [varchar](50) NOT NULL,
[数量] [decimal](26, 4) NOT NULL,
[单位] [char](2) NOT NULL,
[销售价] [decimal](26, 4) NOT NULL,
[金额] [decimal](26, 4) NOT NULL自动计算 库存的数量
[库存清单]
[货号] [varchar](10) NOT NULL,
[货名] [varchar](10) NOT NULL,
[单位] [char](2) NOT NULL,
[数量] [decimal](26, 4) NOT NULL,
[进货价] [decimal](26, 4) NOT NULL,
[销售价] [decimal](26, 4) NOT NULL该存储过程怎么写。还是用 触发器为好??
谢谢了。
[进货单明细]
[编号] [varchar](10) NOT NULL,
[进货单号] [varchar](10) NOT NULL,
[货号] [varchar](10) NOT NULL,
[货名] [varchar](50) NOT NULL,
[数量] [decimal](26, 4) NOT NULL,
[单位] [char](2) NOT NULL,
[进价] [decimal](26, 4) NOT NULL,
[金额] [decimal](26, 4) NOT NULL
[销售单明细]
[编号] [varchar](10) NOT NULL,
[销售单号] [varchar](10) NOT NULL,
[货号] [varchar](10) NOT NULL,
[货名] [varchar](50) NOT NULL,
[数量] [decimal](26, 4) NOT NULL,
[单位] [char](2) NOT NULL,
[销售价] [decimal](26, 4) NOT NULL,
[金额] [decimal](26, 4) NOT NULL自动计算 库存的数量
[库存清单]
[货号] [varchar](10) NOT NULL,
[货名] [varchar](10) NOT NULL,
[单位] [char](2) NOT NULL,
[数量] [decimal](26, 4) NOT NULL,
[进货价] [decimal](26, 4) NOT NULL,
[销售价] [decimal](26, 4) NOT NULL该存储过程怎么写。还是用 触发器为好??
谢谢了。
http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281例如至少你应该给出,如果货号,货名,进货价(或者销售价)相同时是否进行累加?
USE [yurong_web]
GO
/****** 对象: Trigger [tri_detectware] 脚本日期: 04/18/2010 16:14:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE TRIGGER [tri_detectware]
ON [dbo].[detectWareHistory]
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for trigger here
declare @dchangetype varchar(10)
select @dchangetype =dchangetype from inserted
if @dchangetype='1'
update w set w.dAmount=w.dAmount+isnull(i.damount,0)
from detectware w,inserted i
where i.dtype= w.dtype
and i.dname=w.dname
and i.dspec=w.dspec
else
update w set w.dAmount=w.dAmount - isnull(i.damount,0)
from detectware w,inserted i
where i.dtype= w.dtype
and i.dname=w.dname
and i.dspec=w.dspec
if @@ROWCOUNT=0
if @dchangetype='1'
insert into detectware (dtype,dname,dspec,damount)
select dtype,dname,dspec,damount from inserted
else
insert into detectware (dtype,dname,dspec,damount)
select dtype,dname,dspec,-damount from inserted
ENDUSE [yurong_web]
GO
/****** 对象: Trigger [tru_detectware] 脚本日期: 04/18/2010 16:14:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE TRIGGER [tru_detectware]
ON [dbo].[detectWareHistory]
AFTER update
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for trigger here
declare @count int
declare @dchangetype varchar(10)
select @dchangetype =dchangetype from inserted
select @count= count(*) from inserted i ,deleted d
where i.dtype= d.dtype
and i.dname=d.dname
and i.dspec=d.dspec
if @count>0
begin
if @dchangetype='1'
update w set w.dAmount=w.dAmount+isnull(i.damount,0) - isnull(d.damount,0)
from detectware w,inserted i ,deleted d
where i.dtype= w.dtype
and i.dname=w.dname
and i.dspec=w.dspec
and d.dtype= w.dtype
and d.dname=w.dname
and d.dspec=w.dspec
else
update w set w.dAmount=w.dAmount - isnull(i.damount,0) + isnull(d.damount,0)
from detectware w,inserted i ,deleted d
where i.dtype= w.dtype
and i.dname=w.dname
and i.dspec=w.dspec
and d.dtype= w.dtype
and d.dname=w.dname
and d.dspec=w.dspec
end
else
begin
select @count= count(*) from detectware w,inserted i
where i.dtype= w.dtype
and i.dname=w.dname
and i.dspec=w.dspec
if @count>0
begin
if @dchangetype='1'
begin
update w set w.dAmount=w.dAmount - isnull(d.damount,0)
from detectware w,deleted d
where d.dtype= w.dtype
and d.dname=w.dname
and d.dspec=w.dspec
update w set w.dAmount=w.dAmount + isnull(i.damount,0)
from detectware w,inserted i
where i.dtype= w.dtype
and i.dname=w.dname
and i.dspec=w.dspec
end
else
begin
update w set w.dAmount=w.dAmount + isnull(d.damount,0)
from detectware w,deleted d
where d.dtype= w.dtype
and d.dname=w.dname
and d.dspec=w.dspec
update w set w.dAmount=w.dAmount - isnull(i.damount,0)
from detectware w,inserted i
where i.dtype= w.dtype
and i.dname=w.dname
and i.dspec=w.dspec
end
end
else
if @dchangetype='1'
insert into detectware (dtype,dname,dspec,damount)
select dtype,dname,dspec,damount from inserted
else
insert into detectware (dtype,dname,dspec,damount)
select dtype,dname,dspec,-damount from inserted
end
ENDUSE [yurong_web]
GO
/****** 对象: Trigger [trd_detectware] 脚本日期: 04/18/2010 16:14:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE TRIGGER [trd_detectware]
ON [dbo].[detectWareHistory]
AFTER delete
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for trigger here
declare @dchangetype varchar(10)
select @dchangetype =dchangetype from inserted
if @dchangetype='1'
update w set w.dAmount=w.dAmount - isnull(d.damount,0)
from detectware w ,deleted d
where d.dtype= w.dtype
and d.dname=w.dname
and d.dspec=w.dspec
else
update w set w.dAmount=w.dAmount + isnull(d.damount,0)
from detectware w ,deleted d
where d.dtype= w.dtype
and d.dname=w.dname
and d.dspec=w.dspec
END
as
begin
insert into [库存清单]
select a.[货号],a.[货名],b.[单位],a.[数量]-b.[数量] as [数量],a.[进货价],b.[销售价]
from [进货单明细] a join [销售单明细] b on
a.[货号]=b.[货号]
end
--执行
exec Inventory
--查询
select * from [库存清单]