数据更新-触发器-MSSQL版本

数据更新-触发器案例
chinayaosir 12/02/2007
DATABASE平台MS SQL SERVER

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO


ALTER   trigger dbo.w_house_center_iutg on dbo.w_house_center
for insert,update as
    declare @mtrlid      varchar(38)
    declare @storewhouse varchar(20)
    declare @whouseset   varchar(20)
    declare @factqty     decimal(10,2)
    declare @mtrlunit    varchar(20)

begin
    select @mtrlid        =(select mtrlid from inserted)
    select @storewhouse    =(select storewhouse from inserted)            
    select @whouseset     =(select whouseset from inserted)
    select @factqty     =(select factqty  from inserted)            
    select @mtrlunit     =(select mtrlunit from inserted)
      
    if (not(@mtrlid is null))
    begin
    if  exists(select *  from b_mbasic ,inserted where(b_mbasic.mtrlid =inserted.mtrlid  ))        
        begin
        update b_mbasic   set b_mbasic.storewhouse=@storewhouse,b_mbasic.whouseset=@whouseset,b_mbasic.factqty=@factqty,b_mbasic.mtrlunit=@mtrlunit   from b_mbasic,inserted   where  b_mbasic.mtrlid =inserted.mtrlid
        end
    if  exists(select *  from w_house_center_init ,inserted where(w_house_center_init.mtrlid =inserted.mtrlid  ))        
        begin
        update w_house_center_init   set w_house_center_init.storewhouse=@storewhouse,w_house_center_init.whouseset=@whouseset,w_house_center_init.mtrlunit=@mtrlunit   from w_house_center_init,inserted   where  w_house_center_init.mtrlid =inserted.mtrlid
        end

    if  exists(select *  from w_house_center_in ,inserted where(w_house_center_in.mtrlid =inserted.mtrlid  ))        
        begin
        update w_house_center_in   set w_house_center_in.storewhouse=@storewhouse,w_house_center_in.whouseset=@whouseset,w_house_center_in.mtrlunit=@mtrlunit   from w_house_center_in,inserted   where  w_house_center_in.mtrlid =inserted.mtrlid
        end
    if  exists(select *  from w_house_center_out ,inserted where(w_house_center_out.mtrlid =inserted.mtrlid  ))        
        begin
        update w_house_center_out   set w_house_center_out.storewhouse=@storewhouse,w_house_center_out.whouseset=@whouseset,w_house_center_out.mtrlunit=@mtrlunit   from w_house_center_out,inserted   where  w_house_center_out.mtrlid =inserted.mtrlid
        end

    if update(factqty)
        begin    
            update b_mbasic set b_mbasic.factqty=@factqty,b_mbasic.mtrlunit=@mtrlunit
            from b_mbasic,inserted   where  b_mbasic.mtrlid =inserted.mtrlid    
        end
    end
end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

ALTER  trigger dbo.w_house_center_dtg on dbo.w_house_center                             
    for DELETE as
    declare @mtrlid      varchar(38)
begin
    select @mtrlid        =(select mtrlid from inserted)          
    if (not(@mtrlid is null))
    begin
        if  exists(select *  from b_mbasic ,inserted where(b_mbasic.mtrlid =inserted.mtrlid  ))
        begin
            update b_mbasic set b_mbasic.factqty=0     
            from b_mbasic,inserted   where  b_mbasic.mtrlid =inserted.mtrlid
        end
    end
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO