zjcxc(邹建) 看到你很高兴这个是领料表
CREATE TABLE [PlanMO2] (
[PMoNo] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[PMoLine] [smallint] NOT NULL ,
[Rev] [smallint] NULL CONSTRAINT [DF_PlanPKList2_Rev] DEFAULT (0),
[CT] [nvarchar] (1) COLLATE Chinese_PRC_CI_AS NULL ,
[PartsID] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[UseMaterial] [nvarchar] (15) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF_PlanMO2_UseMaterial] DEFAULT (N'空'),
[PkLDate] [smalldatetime] NULL ,
[PkLQty] [real] NULL ,
[PkLQtyL] [real] NULL CONSTRAINT [DF_PlanPKList2_PkLQtyL] DEFAULT (0),
[PkQty] [real] NULL CONSTRAINT [DF_PlanPKList2_PkQty] DEFAULT (0),
[PkQtyL] [real] NULL CONSTRAINT [DF_PlanPKList2_PkQtyL] DEFAULT (0),
[Finish] [bit] NULL CONSTRAINT [DF_PlanPKList2_Finish] DEFAULT (0),
[Res] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
CONSTRAINT [PK_PlanPKList2] PRIMARY KEY CLUSTERED
(
[PMoNo],
[PMoLine],
[PartsID]
) ON [PRIMARY] ,
CONSTRAINT [FK_PlanPKList2_BCParts] FOREIGN KEY
(
[PartsID]
) REFERENCES [BCParts] (
[PartsID]
) ON UPDATE CASCADE
) ON [PRIMARY]
GO这个是发料表
CREATE TABLE [StorePick2] (
[PkNo] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Line] [smallint] NOT NULL ,
[PMoNo] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[PMoLine] [smallint] NULL ,
[PartsID] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[LotNo] [nvarchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
[Store] [nvarchar] (4) COLLATE Chinese_PRC_CI_AS NULL ,
[Bin] [nvarchar] (6) COLLATE Chinese_PRC_CI_AS NULL ,
[PkQty] [real] NULL ,
[PkQtyL] [real] NULL CONSTRAINT [DF_StorePick2_PkQtyL] DEFAULT (0),
[Price] [money] NULL CONSTRAINT [DF_StorePick2_Price] DEFAULT (0),
[Res] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
CONSTRAINT [PK_StorePick2] PRIMARY KEY CLUSTERED
(
[PkNo],
[Line]
) ON [PRIMARY] ,
CONSTRAINT [FK_StorePick2_BCParts] FOREIGN KEY
(
[PartsID]
) REFERENCES [BCParts] (
[PartsID]
) ON UPDATE CASCADE ,
CONSTRAINT [FK_StorePick2_StoreBin] FOREIGN KEY
(
[Store],
[Bin]
) REFERENCES [StoreBin] (
[Store],
[Bin]
) ON UPDATE CASCADE ,
CONSTRAINT [FK_StorePick2_StorePick1] FOREIGN KEY
(
[PkNo]
) REFERENCES [StorePick1] (
[PkNo]
) ON UPDATE CASCADE
) ON [PRIMARY]
GO
CREATE TABLE [PlanMO2] (
[PMoNo] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[PMoLine] [smallint] NOT NULL ,
[Rev] [smallint] NULL CONSTRAINT [DF_PlanPKList2_Rev] DEFAULT (0),
[CT] [nvarchar] (1) COLLATE Chinese_PRC_CI_AS NULL ,
[PartsID] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[UseMaterial] [nvarchar] (15) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF_PlanMO2_UseMaterial] DEFAULT (N'空'),
[PkLDate] [smalldatetime] NULL ,
[PkLQty] [real] NULL ,
[PkLQtyL] [real] NULL CONSTRAINT [DF_PlanPKList2_PkLQtyL] DEFAULT (0),
[PkQty] [real] NULL CONSTRAINT [DF_PlanPKList2_PkQty] DEFAULT (0),
[PkQtyL] [real] NULL CONSTRAINT [DF_PlanPKList2_PkQtyL] DEFAULT (0),
[Finish] [bit] NULL CONSTRAINT [DF_PlanPKList2_Finish] DEFAULT (0),
[Res] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
CONSTRAINT [PK_PlanPKList2] PRIMARY KEY CLUSTERED
(
[PMoNo],
[PMoLine],
[PartsID]
) ON [PRIMARY] ,
CONSTRAINT [FK_PlanPKList2_BCParts] FOREIGN KEY
(
[PartsID]
) REFERENCES [BCParts] (
[PartsID]
) ON UPDATE CASCADE
) ON [PRIMARY]
GO这个是发料表
CREATE TABLE [StorePick2] (
[PkNo] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Line] [smallint] NOT NULL ,
[PMoNo] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[PMoLine] [smallint] NULL ,
[PartsID] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[LotNo] [nvarchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
[Store] [nvarchar] (4) COLLATE Chinese_PRC_CI_AS NULL ,
[Bin] [nvarchar] (6) COLLATE Chinese_PRC_CI_AS NULL ,
[PkQty] [real] NULL ,
[PkQtyL] [real] NULL CONSTRAINT [DF_StorePick2_PkQtyL] DEFAULT (0),
[Price] [money] NULL CONSTRAINT [DF_StorePick2_Price] DEFAULT (0),
[Res] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
CONSTRAINT [PK_StorePick2] PRIMARY KEY CLUSTERED
(
[PkNo],
[Line]
) ON [PRIMARY] ,
CONSTRAINT [FK_StorePick2_BCParts] FOREIGN KEY
(
[PartsID]
) REFERENCES [BCParts] (
[PartsID]
) ON UPDATE CASCADE ,
CONSTRAINT [FK_StorePick2_StoreBin] FOREIGN KEY
(
[Store],
[Bin]
) REFERENCES [StoreBin] (
[Store],
[Bin]
) ON UPDATE CASCADE ,
CONSTRAINT [FK_StorePick2_StorePick1] FOREIGN KEY
(
[PkNo]
) REFERENCES [StorePick1] (
[PkNo]
) ON UPDATE CASCADE
) ON [PRIMARY]
GO
[PkQty] [real] NULL CONSTRAINT [DF_PlanPKList2_PkQty] DEFAULT (0),
[PkQtyL] [real] NULL CONSTRAINT [DF_PlanPKList2_PkQtyL] DEFAULT (0),发料表(StorePick2)中的数量列是(分左右边):
[PkQty] [real] NULL ,
[PkQtyL] [real] NULL CONSTRAINT [DF_StorePick2_PkQtyL] DEFAULT (0),
--修改后的触发器如下,你看看--添加时**********************************************************
CREATE TRIGGER StorePickINsert ON [dbo].[StorePick2]
FOR INSERT
AS
update PlanMo2 set PkQty=a.PkQty+b.PkQty,PkQtyL=a.PkQtyL+b.@PkQtyL
from PlanMo2 a,inserted b
Where a.PMoNo=b.PMoNo AND a.PMoLine=b.PMoLine AND a.PartsID=b.PartsID
and isnull(b.PMoNo,'')<>''
go--删除时****************************************************
CREATE TRIGGER StorePick2Update ON [dbo].[StorePick2]
FOR UPDATE
AS
update PlanMo2 set PkQty=a.PkQty-b.PkQty,PkQtyL=a.PkQtyL-b.@PkQtyL
from PlanMo2 a,deleted b
Where a.PMoNo=b.PMoNo AND a.PMoLine=b.PMoLine AND a.PartsID=b.PartsID
and isnull(b.PMoNo,'')<>''
go--修改时****************************************************
CREATE TRIGGER StorePick2Delete ON [dbo].[StorePick2]
FOR DELETE
AS
--还原
update PlanMo2 set PkQty=a.PkQty-b.PkQty,PkQtyL=a.PkQtyL-b.@PkQtyL
from PlanMo2 a,deleted b
Where a.PMoNo=b.PMoNo AND a.PMoLine=b.PMoLine AND a.PartsID=b.PartsID
and isnull(b.PMoNo,'')<>''--应用新的修改
update PlanMo2 set PkQty=a.PkQty+b.PkQty,PkQtyL=a.PkQtyL+b.@PkQtyL
from PlanMo2 a,inserted b
Where a.PMoNo=b.PMoNo AND a.PMoLine=b.PMoLine AND a.PartsID=b.PartsID
and isnull(b.PMoNo,'')<>''
其实,可以将上面的合并为一个触发器:
--新增,修改,删除时****************************************************
CREATE TRIGGER StorePick2Delete ON [dbo].[StorePick2]
FOR INSERT,UPDATE,DELETE
AS
--如果是修改和删除,则deleted表中有数据,这些数据需要还原
update PlanMo2 set PkQty=a.PkQty-b.PkQty,PkQtyL=a.PkQtyL-b.@PkQtyL
from PlanMo2 a,deleted b
Where a.PMoNo=b.PMoNo AND a.PMoLine=b.PMoLine AND a.PartsID=b.PartsID
and isnull(b.PMoNo,'')<>''--如果是新增和修改,则inserted表中有数据,这些数据需要应用修改
update PlanMo2 set PkQty=a.PkQty+b.PkQty,PkQtyL=a.PkQtyL+b.@PkQtyL
from PlanMo2 a,inserted b
Where a.PMoNo=b.PMoNo AND a.PMoLine=b.PMoLine AND a.PartsID=b.PartsID
and isnull(b.PMoNo,'')<>''
FOR UPDATE
ASupdate a set a.PkQty=a.PkQty+b.PkQty,a.PkQtyL=a.PkQtyL+b.PkQtyL
PlanMo2 a Join
Inserted b on a. PmoNo=b. PmoNo and a. PmoLine=b. PmoLine and a. PartsID=b. PartsIDupdate a set a.PkQty=a.PkQty+b.PkQty,a.PkQtyL=a.PkQtyL+b.PkQtyL
PlanMo2 a Join
D
CREATE TRIGGER StorePick2Update ON [dbo].[StorePick2]
FOR UPDATE
ASupdate a set a.PkQty=a.PkQty+b.PkQty,a.PkQtyL=a.PkQtyL+b.PkQtyL
PlanMo2 a Join
Inserted b on a. PmoNo=b. PmoNo and a. PmoLine=b. PmoLine and a. PartsID=b. PartsIDupdate a set a.PkQty=a.PkQty+b.PkQty,a.PkQtyL=a.PkQtyL+b.PkQtyL
PlanMo2 a Join
Deleted b on a. PmoNo=b. PmoNo and a. PmoLine=b. PmoLine and a. PartsID=b. PartsID
FOR UPDATE
ASupdate a set a.PkQty=a.PkQty+b.PkQty,a.PkQtyL=a.PkQtyL+b.PkQtyL
PlanMo2 a Join
Inserted b on a. PmoNo=b. PmoNo and a. PmoLine=b. PmoLine and a. PartsID=b. PartsIDupdate a set a.PkQty=a.PkQty-b.PkQty,a.PkQtyL=a.PkQtyL-b.PkQtyL
PlanMo2 a Join
Deleted b on a. PmoNo=b. PmoNo and a. PmoLine=b. PmoLine and a. PartsID=b. PartsID