求写一个更新当前库存的触发器！

[winwhere]

目的，根据出入库单据表来更新表kcb，使kcb里的dqkc字段成为最新的库存数。

给出一步步的思路即可。

哪位大侠举手相助，当感激不尽。

[j9dai]

关注 + 友情UP

[孤单北风]

查看数据库帮助！！

[swordmanli]

--库存主从表结构

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_sto_io_detail_sto_io_master]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[sto_io_detail] DROP CONSTRAINT FK_sto_io_detail_sto_io_master
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sto_io_detail]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[sto_io_detail]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sto_io_master]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[sto_io_master]
GO

CREATE TABLE [dbo].[sto_io_detail] (
[iden_id] [int] IDENTITY (1, 1) NOT NULL ,
[iden_order_detail_id] [int] NULL ,
[comp_id] varchar(24) NOT NULL ,
[io_id] varchar(24) NOT NULL ,
[order_id] varchar(24) NULL ,
[prod_id] varchar(24) NOT NULL ,
[color_id] varchar(24) NOT NULL ,
[batch_id] varchar(24) NOT NULL ,
[site_id] varchar(24) NOT NULL ,
[unit_id] varchar(24) NOT NULL ,
[unit_specs] [money] NULL ,
[unit_weight] [money] NOT NULL ,
[unit_weight_net] [money] NOT NULL ,
[unit_cube] [money] NOT NULL ,
[avail_num] [money] NULL ,
[plan_num] [money] NOT NULL ,
[io_num] [money] NOT NULL ,
[price] [money] NOT NULL ,
[relating_id] varchar(24) NULL ,
[relating_num] [money] NULL ,
[consign_bal_num] [money] NOT NULL ,
[consign_bal_flag] char(2) NOT NULL ,
[status_flag] char(2) NULL ,
[io_type_id] varchar(24) NULL ,
[tax_rate] [money] NOT NULL ,
[utax_amt] [money] NOT NULL ,
[tax_amt] [money] NOT NULL ,
[store_id] varchar(24) NULL ,
[amt] AS ([utax_amt] + [tax_amt]) ,
[sale_cost] [money] NULL ,
[view026_flag] [int] NULL ,
[view027_flag] [int] NULL ,
[view038_flag] [int] NULL ,
[iden_fit_detail_id] [int] NULL ,
[remark] varchar(255) NULL ,
[batch_rage] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[sto_io_master] (
[iden_id] [int] IDENTITY (1, 1) NOT NULL ,
[comp_id] varchar(24) NOT NULL ,
[io_id] varchar(24) NOT NULL ,
[fit_date] [smalldatetime] NULL ,
[io_date] [smalldatetime] NOT NULL ,
[audit_date] [smalldatetime] NULL ,
[hand_id] varchar(24) NULL ,
[io_type_id] varchar(24) NOT NULL ,
[plan_id] varchar(24) NULL ,
[order_id] varchar(24) NULL ,
[move_id] varchar(24) NULL ,
[store_id] varchar(24) NOT NULL ,
[relating_store_id] varchar(24) NULL ,
[object_type] varchar(24) NULL ,
[object_id] varchar(24) NULL ,
[client_id] varchar(24) NULL ,
[funds_client_id] varchar(24) NOT NULL ,
[deliv_client_id] varchar(24) NOT NULL ,
[bonus_client_id] varchar(24) NOT NULL ,
[sales_man_id] varchar(24) NOT NULL ,
[total_amt] [money] NOT NULL ,
[cut_rate] [money] NOT NULL ,
[total_cuted_amt] [money] NOT NULL ,
[total_num] [money] NOT NULL ,
[total_con_num] [money] NOT NULL ,
[total_weight] [money] NOT NULL ,
[total_weight_net] [money] NOT NULL ,
[total_cube] [money] NOT NULL ,
[curr_id] varchar(24) NOT NULL ,
[exchange_rate] [money] NOT NULL ,
[check_flag] varchar(24) NOT NULL ,
[status_flag] char(2) NOT NULL ,
[if_trans] char(2) NULL ,
[if_sign] char(2) NULL ,
[print_times] [tinyint] NULL ,
[relating_id] varchar(24) NULL ,
[close_flag] char(2) NULL ,
[cost_flag] char(2) NOT NULL ,
[rec_flag] char(2) NOT NULL ,
[remark] varchar(255) NULL ,
[check_user] varchar(24) NULL ,
[check_time] [smalldatetime] NULL ,
[cost_check_user] varchar(24) NULL ,
[cost_check_time] [smalldatetime] NULL ,
[create_user] varchar(255) NOT NULL ,
[create_time] [smalldatetime] NOT NULL ,
[last_update_time] [smalldatetime] NOT NULL ,
[last_update_user] varchar(24) NOT NULL ,
[using_credit] [money] NOT NULL ,
[src_flag] char(2) NOT NULL ,
[supplier_id] varchar(24) NULL ,
[borrow_rec_flag] char(2) NOT NULL ,
[fit_deliv_id] varchar(24) NULL ,
[inform_deliv_date] [smalldatetime] NULL ,
[arrive_date] [smalldatetime] NULL ,
[addr_name] varchar(255) NULL ,
[linkman] varchar(24) NULL ,
[link_tel] varchar(24) NULL ,
[budget] varchar(24) NULL ,
[e_mail] varchar(255) NULL ,
[deliv_remark] varchar(255) NULL ,
[fact_arrive_date] [smalldatetime] NULL ,
[abnormal_reason] varchar(255) NULL ,
[sign_remark] varchar(255) NULL ,
[resp_user] varchar(24) NULL ,
[resp_date] [smalldatetime] NULL ,
[io_user] varchar(24) NULL ,
[total_amt_rec] [money] NULL ,
[incomp_id] varchar(24) NULL ,
[instore_id] varchar(24) NULL ,
[in_io_type_id] varchar(24) NULL ,
[proj_id] varchar(24) NULL ,
[purpose] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[inproj_id] varchar(24) NULL
) ON [PRIMARY]
GO

[swordmanli]

--主表触发器

CREATE TRIGGER trg_sto_io_master ON dbo.sto_io_master
FOR INSERT, UPDATE
AS
set nocount on

declare @old_status char(2),
@new_status char(2),
@comp_id char(2),
@io_id char(2),
@rowcount int,
@io_type_id varchar(24),
@bill_id varchar(24),
@rtn_value int


if update(status_flag) or update(store_id) or update(io_type_id)
begin
update sto_io_detail
set status_flag = i.status_flag,
store_id = i.store_id,
io_type_id = i.io_type_id
from sto_io_detail sd, inserted i
where sd.comp_id = i.comp_id and
sd.io_id = i.io_id
end

[swordmanli]

CREATE TRIGGER trg_sto_io_detail_storage_upd ON dbo.sto_io_detail
FOR UPDATE

AS
/*
单据状态：
AS 已签收
CC 已作废
NA 未确认
YA 已确认
*/




set nocount on

declare @rtn int

declare @comp_id common_id,
@store_id common_id,
@site_id common_id,
@prod_id common_id,
@batch_id common_id,
@color_id common_id,
@unit_id common_id,
@io_num common_amt,
@unit_specs common_amt,
@update_flag common_status,
@status_flag common_status,
@io_flag int,
@price common_amt --add by yy 2003-8-6



if update(prod_id) or update(unit_id)
begin
update sto_io_detail
set unit_specs = dbo.f_get_prod_unit_specs(i.prod_id,i.unit_id),
unit_weight = dbo.f_get_prod_unit_weight(i.prod_id,i.unit_id),
unit_weight_net = dbo.f_get_prod_unit_weight_net(i.prod_id,i.unit_id),
unit_cube = dbo.f_get_prod_unit_cube(i.prod_id,i.unit_id)
from sto_io_detail sd, inserted i
where sd.iden_id = i.iden_id

if @@error <> 0
begin
raiserror('更新出入库明细档包装单位信息出错!',16,1)
return
end
end

if (update(status_flag) or update(io_type_id) or update(comp_id) or update(store_id) or update(site_id) or update(prod_id) or update(batch_id) or update(color_id) or update(unit_id) or update(unit_specs) or update(io_num) or update(price) ) --add by yy 2003-8-6
begin


declare io_cursor cursor for
select d.comp_id, d.store_id, d.site_id, d.prod_id, d.batch_id, d.color_id, d.unit_id, d.unit_specs,
d.io_num, d.status_flag, pt.io_flag, 'D' ,d.price --add by yy 2003-8-6
from deleted d, pub_io_type pt
where d.comp_id = pt.comp_id and
d.io_type_id = pt.io_type_id and
d.status_flag in ('NA', 'YA') and
d.unit_specs > 0
union all
select i.comp_id, i.store_id, i.site_id, i.prod_id, i.batch_id, i.color_id, i.unit_id, i.unit_specs,
i.io_num, i.status_flag, pt.io_flag, 'I' ,i.price --add by yy 2003-8-6
from inserted i, pub_io_type pt
where i.comp_id = pt.comp_id and
i.io_type_id = pt.io_type_id and
i.status_flag in ('NA', 'YA') and
i.unit_specs > 0


open io_cursor
if @@error <> 0
begin
close io_cursor
deallocate io_cursor
raiserror('打开游标错误！',16,1)
return
end

fetch io_cursor into @comp_id, @store_id, @site_id, @prod_id, @batch_id, @color_id, @unit_id, @unit_specs,
@io_num, @status_flag, @io_flag, @update_flag ,@price -- add by yy 2003-8-6

while @@fetch_status = 0
begin

execute @rtn = sto_modify_storage @comp_id, @store_id, @site_id, @prod_id, @batch_id, @color_id, @unit_id, @unit_specs,
@io_num, @status_flag, @io_flag, @update_flag , @price -- add by yy 2003-8-6
if @rtn <> 1
begin
close io_cursor
deallocate io_cursor
raiserror('更新库存错误1！',16,1)
return
end

fetch io_cursor into @comp_id, @store_id, @site_id, @prod_id, @batch_id, @color_id, @unit_id, @unit_specs,
@io_num, @status_flag, @io_flag, @update_flag ,@price -- add by yy 2003-8-6
end
close io_cursor
deallocate io_cursor

end

return