--定义变量,用于累计事务执行过程中的错误declare@ErrorSumint--初始化为0,即无错误set@ErrorSum=0--编写事务begintransaction--更新语句update Goods set Number = Number +5where CategoryNO in(select CategoryNO from Category where CategoryName like'饼干')--累计是否有错误set@ErrorSum=@ErrorSum+@@ERROR--判断执行过程中是否出现故障,是的话,回滚事务,否则提交事务if(@ErrorSum>0)rollbacktransactionelsecommittransaction
createtrigger tri_SaleBill_Instered on SaleBill
forinsertasbeginbegintrandeclare@ErrorSumintset@ErrorSum=0declare@numberint,@goodsnovarchar(10)select@number= Number,@goodsno= GoodsNO from inserted
update Goods set Number = Number +@numberwhere GoodsNO =@goodsnoset@ErrorSum=@ErrorSum+ @@ERRORif(@ErrorSum<>0)rollbacktranelsecommittranend--测试insertinto SaleBill values('GN0011','S01','2018-06-09 00:00:00.000',5)
createtrigger tri_SaleBill_Delete on SaleBill fordeleteasbeginbegintrandeclare@ErrorSumintset@ErrorSum=0declare@Numberintselect@Number= Number from deleted
update Goods set Number = Number -@Numberset@ErrorSum=@ErrorSum+ @@ERRORif(@ErrorSum<>0)rollbacktranelsecommittranenddeletefrom SaleBill where GoodsNO ='GN0011'and SNO ='S07';