SQL游标增改状态时触发
1 USE [Test] 2 GO 3 /****** Object: Trigger [dbo].[DBpoitem_mstIup] Script Date: 2017/6/1 9:35:41 ******/ 4 SET ANSI_NULLS ON 5 GO 6 SET QUOTED_IDENTIFIER ON 7 GO 8 9 ALTER TRIGGER [dbo].[DBpoitem_mstIup] 10 ON [dbo].[poitem_mst] 11 FOR UPDATE 12 AS 13 DECLARE @Site SiteType 14 , @InsertFlag TINYINT 15 , @ActionExpression NVARCHAR(60) 16 SELECT 17 @InsertFlag = CASE 18 WHEN EXISTS ( SELECT 1 FROM deleted ) THEN 0 19 ELSE 1 20 END 21 22 DECLARE @UserName LongListType 23 SET @UserName = dbo.UserNameSp() 24 25 DECLARE 26 @Severity INT 27 , @Infobar InfobarType 28 , @BypassPoitemTrg ListYesNoType 29 , @ESBSupplierBalanceFlag ListYesNoType 30 31 SET @Severity = 0 32 33 DECLARE 34 @poitemRowPointer RowPointerType 35 , @PoNum PoNumType 36 , @PoVendNum VendNumType 37 , @PoLine PoLineType 38 , @PoRelease PoReleaseType 39 , @oldPoitemStat PoitemStatType 40 , @newPoitemStat PoitemStatType 41 , @oldDueDate DateType 42 , @newDueDate DateType 43 , @oldPromiseDate DateType 44 , @newPromiseDate DateType 45 , @oldReleaseDate DateType 46 , @newReleaseDate DateType 47 , @oldItem ItemType 48 , @newItem ItemType 49 , @oldVendItem VendItemType 50 , @newVendItem VendItemType 51 , @oldDescription DescriptionType 52 , @newDescription DescriptionType 53 , @oldItemType PoLineItemTypeType 54 , @newItemType PoLineItemTypeType 55 , @oldCostType FaCostTypeType 56 , @newCostType FaCostTypeType 57 , @oldUM UMType 58 , @newUM UMType 59 , @QtyReceived QtyUnitNoNegType 60 , @oldQtyOrdered QtyUnitNoNegType 61 ,@newQtyOrdered QtyUnitNoNegType 62 , @oldQtyOrderedConv QtyUnitNoNegType 63 , @newQtyOrderedConv QtyUnitNoNegType 64 , @oldPlanCostConv CostPrcType 65 , @newPlanCostConv CostPrcType 66 , @oldItemCostConv CostPrcType 67 , @newItemCostConv CostPrcType 68 , @oldUnitMatCostConv CostPrcType 69 , @newUnitMatCostConv CostPrcType 70 , @oldUnitFreightCostConv CostPrcType 71 , @newUnitFreightCostConv CostPrcType 72 , @oldUnitDutyCostConv CostPrcType 73 , @newUnitDutyCostConv CostPrcType 74 , @oldUnitBrokerageCostConv CostPrcType 75 , @newUnitBrokerageCostConv CostPrcType 76 , @oldUnitInsuranceCostConv CostPrcType 77 , @newUnitInsuranceCostConv CostPrcType 78 , @oldUnitLocFrtCostConv CostPrcType 79 , @newUnitLocFrtCostConv CostPrcType 80 , @oldLcOverride ListYesNoType 81 , @newLcOverride ListYesNoType 82 , @oldExpedited ListYesNoType 83 , @newExpedited ListYesNoType 84 , @oldWhse WhseType 85 , @newWhse WhseType 86 , @oldRevision RevisionType 87 , @newRevision RevisionType 88 , @ItemRevision RevisionType 89 , @oldDrawingNbr DrawingNbrType 90 , @newDrawingNbr DrawingNbrType 91 , @oldRefType RefTypeIJKOTType 92 , @newRefType RefTypeIJKOTType 93 , @oldRefNum CoJobProjTrnNumType 94 , @newRefNum CoJobProjTrnNumType 95 , @oldRefLineSuf CoLineSuffixProjTaskTrnLineType 96 , @newRefLineSuf CoLineSuffixProjTaskTrnLineType 97 , @oldRefRelease CoReleaseOperNumType 98 , @newRefRelease CoReleaseOperNumType 99 , @oldNonInvAcct AcctType 100 , @newNonInvAcct AcctType 101 , @oldNonInvAcctUnit1 UnitCode1Type 102 , @newNonInvAcctUnit1 UnitCode1Type 103 , @oldNonInvAcctUnit2 UnitCode2Type 104 , @newNonInvAcctUnit2 UnitCode2Type 105 , @oldNonInvAcctUnit3 UnitCode3Type 106 , @newNonInvAcctUnit3 UnitCode3Type 107 , @oldNonInvAcctUnit4 UnitCode4Type 108 , @newNonInvAcctUnit4 UnitCode4Type 109 , @oldTaxCode1 TaxCodeType 110 , @newTaxCode1 TaxCodeType 111 , @oldTaxCode2 TaxCodeType 112 , @newTaxCode2 TaxCodeType 113 , @oldTransNat TransNatType 114 , @newTransNat TransNatType 115 , @oldTransNat2 TransNat2Type 116 , @newTransNat2 TransNat2Type 117 , @oldDelterm DeltermType 118 , @newDelterm DeltermType 119 , @oldProcessInd ProcessIndType 120 , @newProcessInd ProcessIndType 121 , @oldEcCode EcCodeType 122 , @newEcCode EcCodeType 123 , @oldCommCode CommodityCodeType 124 , @newCommCode CommodityCodeType 125 , @oldOrigin EcCodeType 126 , @newOrigin EcCodeType 127 , @oldUnitWeight UnitWeightType 128 , @newUnitWeight UnitWeightType 129 , @oldSupplQtyConvFactor UMConvFactorType 130 , @newSupplQtyConvFactor UMConvFactorType 131 , @oldExportValue AmountType 132 , @newExportValue AmountType 133 , @oldShipAddr DropShipTypeType 134 , @newShipAddr DropShipTypeType 135 , @oldDropShipNo DropShipNoType 136 , @newDropShipNo DropShipNoType 137 , @oldDropSeq DropSeqType 138 , @newDropSeq DropSeqType 139 , @ContainsOnlyTaxFreeMatls ListYesNoType 140 , @TaxFreeMatl ListYesNoType 141 , @OldQtyReceived QtyUnitNoNegType 142 , @oldManufacturerId ManufacturerIdType 143 , @newManufacturerId ManufacturerIdType 144 , @oldManufacturerItem ManufacturerItemType 145 , @newManufacturerItem ManufacturerItemType 146 , @OldPreassignLots ListYesNoType 147 , @NewPreassignLots ListYesNoType 148 , @OldPreassignSerials ListYesNoType 149 , @NewPreassignSerials ListYesNoType 150 , @ProjStat ProjStatusType 151 , @ProjtaskStat ProjStatusType 152 , @AutoReceiveDemandingSitePO ListYesNoType 153 , @newFaNum FaNumType 154 , @oldFaNum FaNumType 155 , @PoNumLinkFaNum PoNumType 156 , @PoLineLinkFaNum PoLineType 157 158 159 DECLARE poitem_mstIupCrs CURSOR LOCAL STATIC READ_ONLY 160 FOR SELECT 161 ii.RowPointer 162 , ii.po_num 163 , ISNULL(ii.po_line, 0) 164 , ISNULL(ii.po_release, 0) 165 , dd.stat 166 , ii.stat 167 , dd.due_date 168 , ii.due_date 169 , dd.expedited 170 , ii.expedited 171 , dd.promise_date 172 , ii.promise_date 173 , dd.release_date 174 , ii.release_date 175 , dd.item 176 , ii.item 177 , dd.vend_item 178 , ii.vend_item 179 , dd.description 180 , ii.description 181 , dd.item_type 182 , ii.item_type 183 , dd.cost_type 184 , ii.cost_type 185 , dd.u_m 186 , ii.u_m 187 ,dd.qty_ordered 188 ,ISNULL(ii.qty_ordered,0) 189 , dd.qty_ordered_conv 190 , ISNULL(ii.qty_ordered_conv, 0) 191 , dd.plan_cost_conv 192 , ii.plan_cost_conv 193 , dd.item_cost_conv 194 , ii.item_cost_conv 195 , dd.unit_mat_cost_conv 196 , ISNULL(ii.unit_mat_cost_conv, 0) 197 , dd.unit_freight_cost_conv 198 , ISNULL(ii.unit_freight_cost_conv, 0) 199 , dd.unit_duty_cost_conv 200 , ISNULL(ii.unit_duty_cost_conv, 0) 201 , dd.unit_brokerage_cost_conv 202 , ISNULL(ii.unit_brokerage_cost_conv, 0) 203 , dd.unit_insurance_cost_conv 204 , ISNULL(ii.unit_insurance_cost_conv, 0) 205 , dd.unit_loc_frt_cost_conv 206 , ISNULL(ii.unit_loc_frt_cost_conv, 0) 207 , dd.lc_override 208 , ii.lc_override 209 , dd.whse 210 , ii.whse 211 , dd.revision 212 , ii.revision 213 , item.revision 214 , dd.drawing_nbr 215 , ii.drawing_nbr 216 , dd.ref_type 217 , ii.ref_type 218 , dd.ref_num 219 , ii.ref_num 220 , dd.ref_line_suf 221 , ISNULL(ii.ref_line_suf, 0) 222 , dd.ref_release 223 , ISNULL(ii.ref_release, 0) 224 , dd.non_inv_acct 225 , ii.non_inv_acct 226 , dd.non_inv_acct_unit1 227 , ii.non_inv_acct_unit1 228 , dd.non_inv_acct_unit2 229 , ii.non_inv_acct_unit2 230 , dd.non_inv_acct_unit3 231 , ii.non_inv_acct_unit3 232 , dd.non_inv_acct_unit4 233 , ii.non_inv_acct_unit4 234 , dd.tax_code1 235 , ii.tax_code1 236 , dd.tax_code2 237 , ii.tax_code2 238 , dd.trans_nat 239 , ii.trans_nat 240 , dd.trans_nat_2 241 , ii.trans_nat_2 242 , dd.delterm 243 , ii.delterm 244 , dd.process_ind 245 , ii.process_ind 246 , dd.ec_code 247 , ii.ec_code 248 , dd.comm_code 249 , ii.comm_code 250 , dd.origin 251 , ii.origin 252 , dd.unit_weight 253 , ii.unit_weight 254 , dd.suppl_qty_conv_factor 255 , ii.suppl_qty_conv_factor 256 , dd.export_value 257 , ii.export_value 258 , dd.ship_addr 259 , ii.ship_addr 260 , dd.drop_ship_no 261 , ii.drop_ship_no 262 , dd.drop_seq 263 , ii.drop_seq 264 , dd.qty_received 265 , ii.qty_received 266 , dd.manufacturer_id 267 , ii.manufacturer_id 268 , dd.manufacturer_item 269 , ii.manufacturer_item 270 , dd.preassign_lots 271 , ii.preassign_lots 272 , dd.preassign_serials 273 , ii.preassign_serials 274 , po.auto_receive_demanding_site_po 275 , ii.fa_num 276 , dd.fa_num 277 FROM inserted ii 278 LEFT OUTER JOIN deleted AS dd ON 279 dd.RowPointer = ii.RowPointer 280 left outer join po WITH (HOLDLOCK) on 281 po.po_num = ii.po_num 282 left outer join vendor on 283 vendor.vend_num = po.vend_num 284 left outer join currency with (readuncommitted) on 285 currency.curr_code = vendor.curr_code 286 left outer join item on 287 item.item = ii.item 288 LEFT OUTER JOIN non_inventory_item ON 289 non_inventory_item.item = ii.item 290 291 OPEN poitem_mstIupCrs 292 WHILE @Severity = 0 293 BEGIN -- cursor loop 294 FETCH poitem_mstIupCrs INTO 295 @poitemRowPointer 296 , @PoNum 297 , @PoLine 298 , @PoRelease 299 , @oldPoitemStat 300 , @newPoitemStat 301 , @oldDueDate 302 , @newDueDate 303 , @oldExpedited 304 , @newExpedited 305 , @oldPromiseDate 306 , @newPromiseDate 307 , @oldReleaseDate 308 , @newReleaseDate 309 , @oldItem 310 , @newItem 311 , @oldVendItem 312 , @newVendItem 313 , @oldDescription 314 , @newDescription 315 , @oldItemType 316 , @newItemType 317 , @oldCostType 318 , @newCostType 319 , @oldUM 320 , @newUM 321 ,@oldQtyOrdered 322 ,@newQtyOrdered 323 , @oldQtyOrderedConv 324 , @newQtyOrderedConv 325 , @oldPlanCostConv 326 , @newPlanCostConv 327 , @oldItemCostConv 328 , @newItemCostConv 329 , @oldUnitMatCostConv 330 , @newUnitMatCostConv 331 , @oldUnitFreightCostConv 332 , @newUnitFreightCostConv 333 , @oldUnitDutyCostConv 334 , @newUnitDutyCostConv 335 , @oldUnitBrokerageCostConv 336 , @newUnitBrokerageCostConv 337 , @oldUnitInsuranceCostConv 338 , @newUnitInsuranceCostConv 339 , @oldUnitLocFrtCostConv 340 , @newUnitLocFrtCostConv 341 , @oldLcOverride 342 , @newLcOverride 343 , @oldWhse 344 , @newWhse 345 , @oldRevision 346 , @newRevision 347 , @ItemRevision 348 , @oldDrawingNbr 349 , @newDrawingNbr 350 , @oldRefType 351 , @newRefType 352 , @oldRefNum 353 , @newRefNum 354 , @oldRefLineSuf 355 , @newRefLineSuf 356 , @oldRefRelease 357 , @newRefRelease 358 , @oldNonInvAcct 359 , @newNonInvAcct 360 , @oldNonInvAcctUnit1 361 , @newNonInvAcctUnit1 362 , @oldNonInvAcctUnit2 363 , @newNonInvAcctUnit2 364 , @oldNonInvAcctUnit3 365 , @newNonInvAcctUnit3 366 , @oldNonInvAcctUnit4 367 , @newNonInvAcctUnit4 368 , @oldTaxCode1 369 , @newTaxCode1 370 , @oldTaxCode2 371 , @newTaxCode2 372 , @oldTransNat 373 , @newTransNat 374 , @oldTransNat2 375 , @newTransNat2 376 , @oldDelterm 377 , @newDelterm 378 , @oldProcessInd 379 , @newProcessInd 380 , @oldEcCode 381 , @newEcCode 382 , @oldCommCode 383 , @newCommCode 384 , @oldOrigin 385 , @newOrigin 386 , @oldUnitWeight 387 , @newUnitWeight 388 , @oldSupplQtyConvFactor 389 , @newSupplQtyConvFactor 390 , @oldExportValue 391 , @newExportValue 392 , @oldShipAddr 393 , @newShipAddr 394 , @oldDropShipNo 395 , @newDropShipNo 396 , @oldDropSeq 397 , @newDropSeq 398 , @OldQtyReceived 399 , @QtyReceived 400 , @oldManufacturerId 401 , @newManufacturerId 402 , @oldManufacturerItem 403 , @newManufacturerItem 404 , @OldPreassignLots 405 , @NewPreassignLots 406 , @OldPreassignSerials 407 , @NewPreassignSerials 408 , @AutoReceiveDemandingSitePO 409 , @newFaNum 410 , @oldFaNum 411 412 IF @@FETCH_STATUS = -1 413 BREAK 414 415 IF UPDATE(Stat) AND @newPoitemStat=N'O' 416 BEGIN 417 EXEC dbo.DBPoItemToInvLotSp @Stat = @newPoitemStat, -- PoitemStatType 418 @PoNum = @PoNum, -- PoNumType 419 @PoLine = @PoLine, -- PoLineType 420 @PoRelease = @PoRelease, -- PoReleaseType 421 @Item = @newItem, -- ItemType 422 @Qty_ordered = @newQtyOrdered-- QtyUnitNoNegType 423 END 424 END -- End of cursor loop 425 426 CLOSE poitem_mstIupCrs 427 DEALLOCATE poitem_mstIupCrs
好的代码就和美食一样，都是需要时间烹饪出来的！
