TJC表
FNumber FQty
7.01.01.01.0021 50
ICStockBillEntry表
FNumber(物料代码) FQty(数量) FInterID(单据)
7.01.01.01.0021 50 001
二表通过FNumber关联ICStockBillEntry表对应的前台界面有这样一个设置,如果是第一次插入这样的单据保存,更新TJC表的FQty是正确的,但是一旦再次点击保存时,ICStockBillEntry表会自动将这张单据自动删除,再插入同样的单据号(内容都一样)求解如何写这样的触发器?
当向ICStockBillEntry表插入代码和数量时,更新TJC表的FQty(FQty=FQty+ICStockBillEntry.FQty)
而在前台界面再次点击保存时,不会更新TJC表的FQty高分求解
FNumber FQty
7.01.01.01.0021 50
ICStockBillEntry表
FNumber(物料代码) FQty(数量) FInterID(单据)
7.01.01.01.0021 50 001
二表通过FNumber关联ICStockBillEntry表对应的前台界面有这样一个设置,如果是第一次插入这样的单据保存,更新TJC表的FQty是正确的,但是一旦再次点击保存时,ICStockBillEntry表会自动将这张单据自动删除,再插入同样的单据号(内容都一样)求解如何写这样的触发器?
当向ICStockBillEntry表插入代码和数量时,更新TJC表的FQty(FQty=FQty+ICStockBillEntry.FQty)
而在前台界面再次点击保存时,不会更新TJC表的FQty高分求解
解决方案 »
- php
- SQL语句存放方式
- VB连接sql server失败的原因
- varchar型字段,前面加一固定字符串,该怎么做?在线,谢谢
- 請教一簡單SQL語句,關於結果合併的.謝謝大家
- 一句Sql能否解决这个问题
- 为什么这样就错了(仅有十分了)
- 請問該sql語句執行的效率怎麼樣:SELECT TOP1 * FROM TABLE WHERE FIELDS='aa',謝謝
- 如何从Windows 98连接另外一台运行Windows 98的SQL Server
- 求写个JAVA程序实现自动导入excel表中信息到My SQL 数据库对应表中方法
- 统计求和查询
- 非常急 ----jdbc sqlserver2008
当保存时直接更新库存
当审核时直接更新库存当审核时直接更新库存,这个触发器我可以写出来就是它的前台界面,一单击保存,K3的数据库表Inventocky(库存表),只更新一次,
TJC是我自建的表,单击N次保存,我的FQty(库存量)就拼命的减
写个insert delete 触发器就可以了
你保存的时候要减还是要加啊
是写到这个ICStockBillEntry表上吧
当向ICStockBillEntry表插入代码和数量时,更新TJC表的FQty(FQty=FQty+ICStockBillEntry.FQty)
而在前台界面再次点击保存时,不会更新TJC表的FQty 什么意思?CREATE TRIGGER TRI ON ICStockBillEntry
FOR INSERT
AS
IF EXISTS
(SELECT * FROM INSERTED I WHERE EXISTS(SELECT 1 FROM ICStockBillEntry WHREE I.FNumber=FNumber)) UPDATE TJC SET FQty=FQty +(SELECT FQty FROM INSERTED) FROM ICStockBillEntry S WHERE S.FNumber=TJC.FNumber
FOR INSERT
AS
IF EXISTS
(SELECT * FROM INSERTED I WHERE EXISTS(SELECT 1 FROM ICStockBillEntry WHREE I.FNumber=FNumber)) UPDATE TJC SET FQty=FQty +(SELECT FQty FROM INSERTED) FROM INSERTED I WHERE I.FNumber=TJC.FNumber
那就说明这个入库表ICStockBillEntry它是先删除这张单据,再插入这张单据
偶的自建表是insert,这样就不断更新偶的FQty(入库数量了)
after insert
as
begin
if exists(select * from inserted i join TJC on i.FNumber=TJC.FNumber)
BEGIN
UPDATE tjc
SET FQty=FQty+i.FQty
from inserted i join TJC on i.FNumber=TJC.FNumber
END
else
begin
insert TJC(FNUMBER,FQTY)
select FNUMBER,FQTY
from inserted
end
end
我在前台做一张入库单据,保存这张单据在ICStockBillEntry就会新建一行记录而当我再次保存这张单据,就会自动删除这张单据,再插入这张单据如果写的是insert触发器的话,那就保存一次就更新一次FQty咯但偶希望的只是一次
after insert
as
begin
insert TJC(FNUMBER,FQTY)
select FNUMBER,FQTY
from inserted i
where not exists(select * from TJC where i.FNumber=FNumber)
UPDATE tjc
SET FQty=FQty+i.FQty
from inserted i join TJC on i.FNumber=TJC.FNumberend
这个批量应该可以
go
create Table ICStockBillEntry(FNumber nvarchar(20),FQty int,FInterID nvarchar(20))
go
insert into TJC values('7.01.01.01.0021',50)
go
insert into ICStockBillEntry values('7.01.01.01.0021',50,'001')
goselect * from TJC
select * from ICStockBillEntry
go
-------------------------
if object_ID('t_tjc','tr') is not null
drop trigger t_tjc
go
create trigger t_tjc on ICStockBillEntry
for insert,delete
as
BEGIN
DECLARE @FInterID intupdate A
set A.FQty=A.FQty-I.FQty
from tjc A
inner join inserted I on A.FNumber=I.FNumberupdate A
set A.FQty=A.FQty+I.FQty
from tjc A
inner join deleted I on A.FNumber=I.FNumber
end------------------------
insert into ICStockBillEntry values('7.01.01.01.0021',50,'001')
select * from tjc
/*FNumber FQty
-------------------- -----------
7.01.01.01.0021 0(1 row(s) affected)*/
delete from ICStockBillEntry where FInterID='001'
select * from tjc
/*
FNumber FQty
-------------------- -----------
7.01.01.01.0021 50
*/
drop table TJC,ICStockBillEntry
当生产入库出来的产品,就说明车间里的结存就少了,所以减
刚才那个在一次插入多条的时候有问题
create Table TJC(FNumber nvarchar(20),FQty int)
go
create Table ICStockBillEntry(FNumber nvarchar(20),FQty int,FInterID nvarchar(20))
go
insert into TJC values('7.01.01.01.0021',50)
go
-------------------------
if object_ID('t_tjc','tr') is not null
drop trigger t_tjc
go
create trigger t_tjc on ICStockBillEntry
for insert,delete
as
BEGIN
DECLARE @FInterID int
update A
set A.FQty=A.FQty-I.FQty
from tjc A
inner join (
select FNumber,FQty=Sum(FQty)
from inserted
group by FNumber
)I on A.FNumber=I.FNumberupdate A
set A.FQty=A.FQty+I.FQty
from tjc A
inner join (
select FNumber,FQty=Sum(FQty)
from deleted
group by FNumber
)I on A.FNumber=I.FNumber
end------------------------
insert into ICStockBillEntry
select '7.01.01.01.0021',50,'001' union all select
'7.01.01.01.0021',1,'001'select * from tjc
select * from ICStockBillEntry/*FNumber FQty
-------------------- -----------
7.01.01.01.0021 0(1 row(s) affected)*/
delete from ICStockBillEntry where FInterID='001'
select * from tjc
/*
FNumber FQty
-------------------- -----------
7.01.01.01.0021 50
*/