--after updatecreatetrigger tgr_lineitem_price_update
on Sales.LINEITEM
afterupdateasbeginifupdate(L_EXTENDEDPRICE)orupdate(L_DISCOUNT)orupdate(L_TAX)begin--定义所需变量,存储旧行和新行的值declare@L_valuediffRealdeclare@newExRealdeclare@newDisRealdeclare@newTaxRealdeclare@oldExRealdeclare@oldDisRealdeclare@oldTaxRealdeclare@newOrderkeyintselect@newEx= L_EXTENDEDPRICE from inserted
select@newDis= L_DISCOUNT from inserted
select@newTax= L_TAX from inserted
select@oldEx= L_EXTENDEDPRICE from deleted
select@oldDis= L_DISCOUNT from deleted
select@oldTax= L_TAX from deleted
select@newOrderkey= L_ORDERKEY from inserted
--更新别的表的值set@L_valuediff=@newEx*(1-@newDis)*(1+@newTax)-@oldEx*(1-@oldDis)*(1+@oldTax)update Sales.ORDERS set O_TOTALPRICE=O_TOTALPRICE+@L_valuediffwhere O_ORDERKEY =@newOrderkeyendend
--instead of update--sql server可以用instead of 来代替before触发器createtrigger tri_update
on Sales.LINEITEM
instead ofupdateasbeginifupdate(L_QUANTITY)begindeclare@L_valuediffintdeclare@L_availqtyintdeclare@newQuintdeclare@newPaintdeclare@newSuintdeclare@oldQuintselect@newQu= L_QUANTITY from inserted
select@newPa= L_PARTKEY from inserted
select@newSu= L_SUPPKEY from inserted
select@oldQu= L_QUANTITY from deleted
set@L_valuediff=@newQu-@oldQuselect@L_availqty= PS_AVAILQTY from Sales.PARTSUPP
where PS_PARTKEY =@newPaand PS_SUPPKEY =@newSuif(@L_availqty-@L_valuediff>=0)begin--有可用的数量,满足订单订购数量print'Available quantity is enough'update Sales.PARTSUPP
set PS_AVAILQTY = PS_AVAILQTY-@L_availqtywhere PS_PARTKEY =@newPaand PS_SUPPKEY =@newSuendelseprint'Available quantity is not enough'endend