SYbase ASA 触发器写法，当某值被更改时触发执行语句

更新触发器案例

最新推荐文章于 2013-11-28 00:20:56 发布

原创最新推荐文章于 2013-11-28 00:20:56 发布 · 1.9k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#触发器 #sql server #sybase #判断是否更新

SYBASe ASA 专栏收录该内容

37 篇文章

订阅专栏

本文介绍了一个使用SQL定义的触发器案例，该触发器在更新批准状态时调整客户余额。具体操作包括更新客户余额减少或增加，取决于订单是否被批准，并在未修改批准状态时更新订单编辑时间。

ALTER TRIGGER "T" AFTER UPDATE OF "approved"
ORDER 3 ON "DBA"."shipper"
referencing new as new_shipper3
FOR EACH ROW /* WHEN( 搜索条件 ) */
 
BEGIN

case when   new_shipper3.approved='Y'  --是否被更新
then update DBA.customer set customer.user_7=customer.user_7-(shipper_line.quantity*isnull(part.user_10,0)) from  DBA.shipper,DBA.shipper_line,DBA.customer,DBA.part--此片一定要加"DBA"
             where             new_shipper3.trans_no=shipper.trans_no and 
                               shipper.trans_no=shipper_line.trans_no and 
                             shipper.customer_id=customer.customer_id and
                                    shipper_line.part_id=part.part_id and 
                                                 part.product_code ='XQ'    
      when   new_shipper3.approved='N' 
then update Dba.customer set customer.user_7=customer.user_7+(shipper_line.quantity*isnull(part.user_10,0)) from dba.shipper,dba.shipper_line,dba.customer,dba.part --此片一定要加"DBA"

          where             new_shipper3.trans_no=shipper.trans_no and 
                               shipper.trans_no=shipper_line.trans_no and 
                             shipper.customer_id=customer.customer_id and
                                    shipper_line.part_id=part.part_id and 
                                                 part.product_code ='XQ'   
else
  update shipper set
    edit_time = getdate(*) where
    shipper.trans_no = new_shipper3.trans_no
end case
end