我有3个表
库存(库存编号,物料编号,库存量,存放地点)
入仓单(入库编号,物料编号,...,数量)
出货单(出货时间,物料编号,,数量)
我往入仓单加一条记录,库存的库存量=库存量+入仓单.数量 出货单加条记录,库存量=库存量-出货单.数量
下面是入仓单更新的代码,上述功能如何VB语句中实现Private Sub cmdupdate_Click()
On Error GoTo cmdupdate_err
sql = "select * from 入仓单明细"
rs.Open sql, conn, adOpenKeyset, adLockPessimistic
If Trim(Text1(0).Text) = "" Then
MsgBox "入庫編號不能为空", vbExclamation + vbOKOnly, "警告"
Text1(0).SetFocus
Exit Sub
End If
.
.
.
If Trim(Text1(5).Text) = "" Then
MsgBox "進庫數量不能为空", vbExclamation + vbOKOnly, "警告"
Text1(5).SetFocus
Exit Sub
End If
If Not IsNumeric(Text1(5).Text) Then
MsgBox "進庫數量必须为数字"
Text1(5).SelStart = 0
Text1(5).SelLength = Len(Text1(5))
Text1(5).SetFocus
Exit Sub
End If
.
.
.
Adodc1.Recordset.UpdateBatch adAffectAllChapters
MsgBox "成功保存"
setbuttons True
Exit Sub
cmdupdate_err:
MsgBox Err.Description
End Sub
库存(库存编号,物料编号,库存量,存放地点)
入仓单(入库编号,物料编号,...,数量)
出货单(出货时间,物料编号,,数量)
我往入仓单加一条记录,库存的库存量=库存量+入仓单.数量 出货单加条记录,库存量=库存量-出货单.数量
下面是入仓单更新的代码,上述功能如何VB语句中实现Private Sub cmdupdate_Click()
On Error GoTo cmdupdate_err
sql = "select * from 入仓单明细"
rs.Open sql, conn, adOpenKeyset, adLockPessimistic
If Trim(Text1(0).Text) = "" Then
MsgBox "入庫編號不能为空", vbExclamation + vbOKOnly, "警告"
Text1(0).SetFocus
Exit Sub
End If
.
.
.
If Trim(Text1(5).Text) = "" Then
MsgBox "進庫數量不能为空", vbExclamation + vbOKOnly, "警告"
Text1(5).SetFocus
Exit Sub
End If
If Not IsNumeric(Text1(5).Text) Then
MsgBox "進庫數量必须为数字"
Text1(5).SelStart = 0
Text1(5).SelLength = Len(Text1(5))
Text1(5).SetFocus
Exit Sub
End If
.
.
.
Adodc1.Recordset.UpdateBatch adAffectAllChapters
MsgBox "成功保存"
setbuttons True
Exit Sub
cmdupdate_err:
MsgBox Err.Description
End Sub
CREATE trigger tri_in
on 入仓单明细 for insert
as
update 物料库存 set 庫存量=庫存量+(select 進庫數量 from inserted)
但是所有记录库存量都加上了进库数量,怎么才能只更改一套记录的值,
即 入仓单明细 中加一条物料编号为0001的记录,物料库存 中的0001记录 库存量=库存量+进库数量,其他的记录值不改变。
请高手指点
update 库存
set 库存量=库存量+inserted.进库数量
from inserted inner join 库存 on
库存.物料编号=inserted.物料编号如果再进一步,还要增加
库存.库存编号=inserted.库存编号
确定是那个仓库的物料数量增加或减少
入仓的数据只有审批的加到库存里,出仓一个道理,这样才有伸缩性。直接写个存储过程。
参数:A(入库编号\出库编号),物料编号,数量,B(入仓\出仓) 类型if B=入仓
begin
insert into 入仓单 Value(A,物料编号,数量)if (selct count(*)库存 where 物料编号=物料编号>0)
begin
update ...
end
else
begin
insert ...
end
endif B=出仓
begin
insert into 出仓单 Value(A,物料编号,数量)if (selct count(*)库存 where 物料编号=物料编号>0)
begin
update ...
end
else
begin
insert ...
end
end
On Error GoTo cmdupdate_err
Sql = "select * from 入仓单明细"
rs.Open Sql, conn, adOpenKeyset, adLockPessimistic
If Trim(Text1(0).Text) = "" Then
MsgBox "入庫編號不能为空", vbExclamation + vbOKOnly, "警告"
Text1(0).SetFocus
Exit Sub
End If
'.
'.
'.
If Trim(Text1(5).Text) = "" Then
MsgBox "進庫數量不能为空", vbExclamation + vbOKOnly, "警告"
Text1(5).SetFocus
Exit Sub
End If
If Not IsNumeric(Text1(5).Text) Then
MsgBox "進庫數量必须为数字"
Text1(5).SelStart = 0
Text1(5).SelLength = Len(Text1(5))
Text1(5).SetFocus
Exit Sub
End If
'.
'.
'.
If s_SaveIn Then
MsgBox "成功保存"
End If
setbuttons True
Exit Sub
cmdupdate_err:
MsgBox Err.Description
End Sub
Private Function s_SaveIn() As Boolean
Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSql As String
con.CursorLocation = adUseClient
con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\db\mydb.mdb;Persist Security Info=False" rs.Open "select * from mytable ", con, adOpenStatic, adLockOptimistic
con.BeginTrans
strSql = "insert into raw_in( " & _
" in_no " & _
" ,raw_no " & _
" ,in_num " & _
" ) " & _
" values( " & _
" '" & Trim(Text1(0).Text) & "'" & _
" ,'" & Trim(Text1(1).Text) & "'" & _
" ," & Val(Trim(Text1(5).Text)) & _
" ) "
con.Execute strSql
strSql = "update raw_stock " & _
" set stock_num = stock_num + " & Val(Trim(Text1(5).Text)) & _
" where raw_no = '" & Trim(Text1(1).Text) & "' "
con.Execute strSql
con.CommandTimeout
s_save = True
Exit Function
errS:
MsgBox Err.Number & ", " & Err.Description, vbOKOnly + vbExclamation, "save"
s_save = False
con.RollbackTrans
End Function
On Error GoTo cmdupdate_err
Sql = "select * from 入仓单明细"
rs.Open Sql, conn, adOpenKeyset, adLockPessimistic
If Trim(Text1(0).Text) = "" Then
MsgBox "入庫編號不能为空", vbExclamation + vbOKOnly, "警告"
Text1(0).SetFocus
Exit Sub
End If
'.
'.
'.
If Trim(Text1(5).Text) = "" Then
MsgBox "進庫數量不能为空", vbExclamation + vbOKOnly, "警告"
Text1(5).SetFocus
Exit Sub
End If
If Not IsNumeric(Text1(5).Text) Then
MsgBox "進庫數量必须为数字"
Text1(5).SelStart = 0
Text1(5).SelLength = Len(Text1(5))
Text1(5).SetFocus
Exit Sub
End If
'.
'.
'.
If s_SaveIn Then
MsgBox "成功保存"
End If
setbuttons True
Exit Sub
cmdupdate_err:
MsgBox Err.Description
End Sub
Private Function s_SaveIn() As Boolean
Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSql As String
con.CursorLocation = adUseClient
con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\db\mydb.mdb;Persist Security Info=False" rs.Open "select * from mytable ", con, adOpenStatic, adLockOptimistic
On Error GoTo errs
con.BeginTrans
strSql = "insert into raw_in( " & _
" in_no " & _
" ,raw_no " & _
" ,in_num " & _
" ) " & _
" values( " & _
" '" & Trim(Text1(0).Text) & "'" & _
" ,'" & Trim(Text1(1).Text) & "'" & _
" ," & Val(Trim(Text1(5).Text)) & _
" ) "
con.Execute strSql
strSql = "update raw_stock " & _
" set stock_num = stock_num + " & Val(Trim(Text1(5).Text)) & _
" where raw_no = '" & Trim(Text1(1).Text) & "' "
con.Execute strSql
con.CommandTimeout
s_save = True
Exit Function
errS:
MsgBox Err.Number & ", " & Err.Description, vbOKOnly + vbExclamation, "save"
s_save = False
con.RollbackTrans
End Function
另外,入仓的时候只要增加就行了,但出仓的时候先要判断要求出仓量是否小于库存量,否则会出现负库存的情况.
AFTER INSERT
AS
begin
declare @s_id char(4),
@tim datetime,
@name nvarchar(10),
@detail nvarchar(50),
@v decimal(9,3),
@real_v decimal(9,3)
select @tim=ymd, @real_v=reality_voltage, @s_id=sensor_id from inserted
select @name=sensor_name from tb_relation3 where tb_relation3.sensor_id=@s_id
select @v=reality_v from tb_v
set @detail=@name + '本体电位超限'
if (@real_v > @v)
insert into alarm
values(@tim,'01',@s_id,@detail)
end
虽然你结贴了,我还是再把我写的一个贴给你!