数据库-数据更新-触发器(SYBASE)

 --sql code with sybase
--1.插入与更新触发器
--2.删除触发器
--chinanetboy
--表自动更新其它6个表的记录

--1.插入与更新触发器
 create trigger dbo.upd_i_o_factoryqty on dbo.o_factoryqty                            
    for INSERT, UPDATE as                             
    declare @datenull datetime            
    declare @hot_date datetime            
    declare @oc_number char(8)           
    declare @itemnumber char(14)         
    declare @factoryid char(10)           
    declare @shipp_date datetime            
    declare @ddihote int           
    declare @emghote int           
    declare @mtyhote int            
    declare @hote_is char(1)    
    declare @maker_nu int  
    declare @makerqty int   
    declare @maker_id char(15)
    declare @officepric real
    declare @confirnpric real
    declare @nowdt datetime
begin
    select @officepric  =0
    select @confirnpric =0
    select @nowdt="01/01/1910 00:00:00"
    select @oc_number  =(select oc_number from inserted)           
    select @itemnumber=(select itemnumber from inserted)           
    select @shipp_date  =(select shipp_date from inserted)            
    select @factoryid    =(select factory_id from inserted)           
    select @ddihote  =(select hotitemddi from inserted)           
    select @emghote=(select hotitememg from inserted)           
    select @mtyhote   =(select hotitemmty   from inserted)    
    select @makerqty =(select factory_qty from inserted)  
    select @maker_nu=(select count(*) from b_factorymake
 where b_factorymake.factory_id=@factoryid )    
    if @maker_nu=1
    begin  
        select @maker_id=(select makerofid from b_factorymake where b_factorymake.factory_id=@factoryid )   
    end  
    else  
    begin  
        select @maker_id =@factoryid   
        select @makerqty =0  
    end  
  
    if ((not(@oc_number is null)) and (not(@itemnumber is null))       
    and (not(@shipp_date is null)) and (not(@factoryid is null)))         
    begin         
  --如果XX表1没有数据则插入此数据
  if not exists(
  select *  from o_makerofqty,inserted                             
  where   (o_makerofqty.oc_number =inserted.oc_number)  and                       
    (o_makerofqty.itemnumber=inserted.itemnumber) and                         
    (o_makerofqty.shipp_date=inserted.shipp_date) and
    (o_makerofqty.factory_id=inserted.factory_id)
  )  
  begin  
   insert into o_makerofqty  
   values(@oc_number,@itemnumber ,@shipp_date,@factoryid,@maker_id,@makerqty,getdate())
  end
       --如果XX表2没有数据则插入此数据               
  if not exists(
  select * from i_ftyandpric,inserted
  where (i_ftyandpric.oc_number =inserted.oc_number)and
    (i_ftyandpric.itemnumber=inserted.itemnumber)and
    (i_ftyandpric.shipp_date =inserted.shipp_date )and
    (i_ftyandpric.factory_id  =inserted.factory_id)
  )
  begin
   insert into i_ftyandpric (sequenceno,oc_number,itemnumber,shipp_date,factory_id,offer_date,ex2factory,ex_factory)
   values(1,@oc_number,@itemnumber,@shipp_date,@factoryid,@nowdt,@officepric,@confirnpric)
  end
       --如果XX表3没有数据则插入此数据       
  if not exists(
  select *  from o_bab_factoryqty,inserted
  where (o_bab_factoryqty.oc_number =inserted.oc_number)and
    (o_bab_factoryqty.itemnumber=inserted.itemnumber)and
    (o_bab_factoryqty.shipp_date=inserted.shipp_date)
  )
  begin
        insert into o_bab_factoryqty(oc_number,itemnumber,shipp_date) values(@oc_number,@itemnumber,@shipp_date)
  end
         
  else --以上为插入数据,下面为修改数据
       
  --如果修改了factory_id栏位
  if update(factory_id)
        begin
            if exists(
   select *  from i_ftyandpric,inserted
            where (i_ftyandpric.oc_number =inserted.oc_number )and
     (i_ftyandpric.itemnumber=inserted.itemnumber)and
     (i_ftyandpric.shipp_date =inserted.shipp_date )
   )
   begin
    update i_ftyandpric 
    set i_ftyandpric.factory_id=@factoryid ,
     i_ftyandpric.ex_factory=@officepric ,
     i_ftyandpric.ex2factory=@confirnpric ,
     i_ftyandpric.remarkof="0",
     i_ftyandpric.offer_date=@nowdt
    from i_ftyandpric,inserted  
    where ((i_ftyandpric.oc_number =inserted.oc_number)and
      (i_ftyandpric.itemnumber=inserted.itemnumber)and
      (i_ftyandpric.shipp_date =inserted.shipp_date ))  
   end
        end
       
  --如果修改了factory_qty栏位    
        if update(factory_qty)  
        begin  
            update o_makerofqty  
            set o_makerofqty.oaquantity=@makerqty ,  
                   o_makerofqty.oaof_date =getdate()  
            from o_makerofqty,inserted  
            where ((o_makerofqty.oc_number =inserted.oc_number) and
     (o_makerofqty.itemnumber=inserted.itemnumber) and
     (o_makerofqty.shipp_date=inserted.shipp_date) and
     (o_makerofqty.factory_id=inserted.factory_id))  
        end  
  
                                                              
        --如果XX表没有数据则插入此数据   
        if not exists(
   select *  from o_itemofhote,inserted                             
   where (o_itemofhote.oc_number =inserted.oc_number)and
   (o_itemofhote.itemnumber=inserted.itemnumber )      and
   (o_itemofhote.shipp_date =inserted.shipp_date )     and
   (o_itemofhote.factory_id   =inserted.factory_id)
  )                  
        --插入此数据 
        begin   
            if  (@ddihote>0) or (@emghote>0) or (@mtyhote>0)     
            begin     
                insert into o_itemofhote                           
                values(@oc_number,@itemnumber,@shipp_date ,@factoryid,           
                @ddihote,@emghote,@mtyhote,0,0,0,0,0,GETDATE(),           
                @datenull,@datenull,'','','','',@datenull,'')                             
            end     
        end
                           
        else
  --更新此数据             
        begin                           
            if update(hotitemddi) or update(hotitememg) or update(hotitemmty) 
                select @hot_date=getdate() 
            else 
                select @hot_date=(select o_itemofhote.hotedate 
                from o_itemofhote,inserted 
                where (o_itemofhote.oc_number=inserted.oc_number) 
                and (o_itemofhote.itemnumber=inserted.itemnumber) 
                and (o_itemofhote.shipp_date=inserted.shipp_date) 
                and (o_itemofhote.factory_id=inserted.factory_id)) 
            update o_itemofhote               
            set    o_itemofhote.oc_number  =@oc_number ,           
            o_itemofhote.itemnumber =@itemnumber ,           
            o_itemofhote.shipp_date   =@shipp_date ,           
            o_itemofhote.factory_id     =@factoryid ,           
            o_itemofhote.hotitemddi  =@ddihote ,           
            o_itemofhote.hotitememg=@emghote ,           
            o_itemofhote.hotitemmty =@mtyhote ,           
            o_itemofhote.hotedate      =@hot_date            
            from o_itemofhote,inserted         
            where (o_itemofhote.oc_number=inserted.oc_number) and                        
     (o_itemofhote.itemnumber=inserted.itemnumber)and       
     (o_itemofhote.shipp_date=inserted.shipp_date)and                          
     (o_itemofhote.factory_id=inserted.factory_id)                        
        end             
  
        select @hote_is=(select hotitem_is from inserted)           
       
  if @hote_is='N'
  begin
   --清空XX表记录           
            delete o_itemofhote from o_itemofhote,inserted           
            where (o_itemofhote.oc_number =inserted.oc_number and 
     (o_itemofhote.itemnumber=inserted.itemnumber)and                        
     (o_itemofhote.shipp_date=inserted.shipp_date)and                         
     (o_itemofhote.factory_id=inserted.factory_id)
        end            
        
        select @ddihote =(select sum(hotitemddi) from o_itemofhote  where itemnumber=@itemnumber )        
        select @emghote =(select sum(hotitememg) from o_itemofhote  where itemnumber=@itemnumber )        
        select @mtyhote =(select sum(hotitemmty) from o_itemofhote  where itemnumber=@itemnumber )               
        select @hote_is='Y'        
        if (@ddihote+@emghote+@mtyhote=0)or((@ddihote+@emghote+@mtyhote) is null)   
        begin        
            select @hote_is='N'      
        end
  --更新XX表      
        update i_itembasic         
        set hotitememg=@emghote ,        
   hotitemmty =@mtyhote ,                  
   hotitemddi  =@ddihote ,       
   hotitem_is  =@hote_is              
        where itemnumber=@itemnumber          
        --更新XX表
        update o_itemallrow          
        set orderc_dt=getdate(),oashipqty=
   (select sum(factory_qty) from o_factoryqty where 
    (o_factoryqty.oc_number=@oc_number)and          
    (o_factoryqty.itemnumber=@itemnumber)and
    (o_factoryqty.shipp_date=@shipp_date )
   )         
        where (o_itemallrow.oc_number = @oc_number )and
    (o_itemallrow.itemnumber =@itemnumber)and
    (o_itemallrow.shipp_date = @shipp_date )     
    end            
end

create trigger dbo.del_o_factoryqty
on  dbo.o_factoryqty           
for DELETE as      
    declare @oc_number char(8)      
    declare @itemnumber char(14)      
    declare @shipp_date datetime    
    declare @order_date datetime     
    declare @hotddi int     
    declare @hotemg int     
    declare @hotmty int       
    declare @hot_is char(1)       
    begin           
        select @oc_number  =(select oc_number from deleted)      
        select @itemnumber=(select itemnumber from deleted)      
        select @shipp_date =(select shipp_date from deleted)           
  --清空表1记录
  delete o_bab_factoryqty 
  from o_bab_factoryqty ,deleted           
        where (o_bab_factoryqty.oc_number =deleted.oc_number  )and
    (o_bab_factoryqty.itemnumber=deleted.itemnumber )and
    (o_bab_factoryqty.shipp_date =deleted.shipp_date )
  --清空表2记录
  delete i_ftyandpric  
  from i_ftyandpric ,deleted           
        where   (i_ftyandpric.oc_number =deleted.oc_number  ) and
    (i_ftyandpric.itemnumber=deleted.itemnumber ) and
    (i_ftyandpric.shipp_date=deleted.shipp_date ) and
    (i_ftyandpric.factory_id=deleted.factory_id )    
  --清空表3记录                                  
        delete o_itemofhote       
        from o_itemofhote ,deleted           
        where   (o_itemofhote.oc_number =deleted.oc_number )  and
    (o_itemofhote.itemnumber=deleted.itemnumber)  and
    (o_itemofhote.shipp_date=deleted.shipp_date)  and
    (o_itemofhote.factory_id=deleted.factory_id)        
  --清空表4记录 
        delete o_makerofqty       
        from o_makerofqty ,deleted           
        where   (o_makerofqty.oc_number =deleted.oc_number)   and
    (o_makerofqty.itemnumber=deleted.itemnumber)  and
    (o_makerofqty.shipp_date=deleted.shipp_date)  and
    (o_makerofqty.factory_id=deleted.factory_id)        
     
        select @hotddi =(select sum(hotitemddi) from o_itemofhote  where itemnumber=@itemnumber )     
        select @hotemg =(select sum(hotitememg) from o_itemofhote  where itemnumber=@itemnumber )     
        select @hotmty =(select sum(hotitemmty)  from o_itemofhote  where itemnumber=@itemnumber )              
        select @hot_is='Y'     
        if (@hotddi+@hotemg+@hotmty=0) or ((@hotddi+@hotemg+@hotmty) is null) 
        begin     
            select @hot_is='N'     
        end     
     --更新表1记录
        update i_itembasic      
        set  hotitememg=@hotemg,hotitemmty =@hotmty,hotitemddi   =@hotddi,hotitem_is   =@hot_is   
        where itemnumber=@itemnumber       
      
        select @order_date='1997.01.01'
     --更新表2记录
        update o_itemallrow      
        set orderc_dt=@order_date ,
  oashipqty=( select sum
    (factory_qty)   
    from o_factoryqty
  where (o_factoryqty.oc_number=@oc_number   )and 
    (o_factoryqty.itemnumber=@itemnumber)and
    (o_factoryqty.shipp_date=@shipp_date )
    )
         
        where (o_itemallrow.oc_number = @oc_number  )and
    (o_itemallrow.itemnumber =@itemnumber )and
    (o_itemallrow.shipp_date =@shipp_date )
 end