1 USE [Test] 2 GO 3 /****** Object: Trigger [dbo].[shipment_mstInsert] Script Date: 2017/6/22 19:11:16 ******/ 4 SET ANSI_NULLS ON 5 GO 6 SET QUOTED_IDENTIFIER ON 7 GO 8 ALTER TRIGGER [dbo].[shipment_mstInsert] 9 ON [dbo].[shipment_mst] 10 INSTEAD OF INSERT 11 AS 12 DECLARE @TRIGGER_ROW_COUNT INT 13 SET @TRIGGER_ROW_COUNT=@@ROWCOUNT 14 IF @TRIGGER_ROW_COUNT = 0 RETURN 15 16 SET NOCOUNT ON 17 18 DECLARE @Today DateType 19 SET @Today = dbo.GetSiteDate(GETDATE()) 20 21 DECLARE @UserName LongListType 22 SET @UserName = dbo.UserNameSp() 23 24 DECLARE 25 @RowPointer RowPointerType 26 , @DefaultPrefix PoNumType 27 , @Prefix PoNumType 28 , @Invoice InvNumType 29 , @KeyLength INT 30 ,@Severity INT 31 ,@Inforbar InfobarType 32 DECLARE @NewKeys TABLE ( 33 -- Use unique names to simplify INSERT statement: 34 New_RowPointer uniqueidentifier, 35 New_Invoice_num nvarchar(12), 36 PRIMARY KEY (New_RowPointer) 37 ) 38 39 SET @DefaultPrefix=N'LP' 40 SET @Severity=0 41 --添加自动生成Invoice单号 robert-update 42 IF @TRIGGER_ROW_COUNT > 1 43 BEGIN 44 DECLARE InvoiceKey CURSOR LOCAL STATIC READ_ONLY FOR 45 SELECT Uf_Shipmet_Invoice, RowPointer 46 FROM inserted bt 47 OPEN InvoiceKey 48 END 49 50 WHILE @Severity = 0 51 BEGIN 52 IF @TRIGGER_ROW_COUNT > 1 53 BEGIN 54 FETCH InvoiceKey INTO @Invoice, @RowPointer 55 56 IF @@FETCH_STATUS = -1 57 BREAK 58 END 59 ELSE 60 SELECT @Invoice = RTRIM(Uf_Shipmet_Invoice), @RowPointer = RowPointer 61 FROM inserted bt 62 63 -- If po_num is To Be Determined, or is afollowed by question-mark, 64 IF @Invoice= N'TBD' OR @Invoice LIKE N'%?' 65 -- Or is all zeroes: 66 --AND REPLACE(REPLACE(@Invoice, ' ', ''), '0', '') = '' 67 BEGIN 68 -- Generate (& register, if appropriate) a new key: 69 SET @Prefix = ISNULL(dbo.StripPrefix(@Invoice), @DefaultPrefix) 70 EXEC @Severity=DBNextInvoiceNumSp 71 @Context=NULL 72 ,@Prefix=@Prefix 73 ,@KeyLength=@KeyLength 74 ,@Key=@Invoice OUTPUT 75 ,@Infobar=@Inforbar OUTPUT 76 77 IF @Severity <> 0 78 BREAK 79 80 INSERT INTO @NewKeys VALUES (@RowPointer, @Invoice) 81 82 -- Remember it for insertion into the appropriate row below: 83 84 IF @TRIGGER_ROW_COUNT = 1 BREAK 85 CONTINUE 86 END 87 88 ---- Register a new key: 89 --EXEC @Severity = dbo.InsertNewKeySp 90 -- @TableName = N'po' 91 -- , @ColumnName = N'po_num' 92 -- , @Key = @po_num 93 -- , @Infobar = @Infobar OUTPUT 94 95 IF @Severity <> 0 OR @TRIGGER_ROW_COUNT = 1 96 BREAK 97 END 98 IF @TRIGGER_ROW_COUNT > 1 99 BEGIN100 CLOSE InvoiceKey101 DEALLOCATE InvoiceKey102 END103 ------robert update 104 105 106 IF (SELECT TOP 1 shipment_id FROM inserted) = 0107 -- INSERT did not specify value(s) for the Identity column shipment_id,108 -- so we let the database manager automatically generate values here.109 INSERT [shipment_mst] (110 [site_ref]111 , [status]112 , [cust_num]113 , [cust_seq]114 , [whse]115 , [ship_loc]116 , [ship_date]117 , [qty_packages]118 , [weight]119 , [weight_u_m]120 , [packer]121 , [ship_code]122 , [carrier_contact]123 , [carrier_code]124 , [veh_num]125 , [pro_number]126 , [tracking_number]127 , [tracking_url]128 , [route]129 , [container]130 , [pickup_date]131 , [authorizer]132 , [authorizer_title]133 , [authorizer_phone]134 , [authorizer_fax]135 , [bol_printed]136 , [proforma_printed]137 , [customs_invoice_printed]138 , [cert_of_origin_printed]139 , [pack_slip_printed]140 , [invoice_printed]141 , [special_inst##1]142 , [special_inst##2]143 , [col_fee]144 , [col_freight_charges]145 , [col_misc_charges]146 , [cod_amount]147 , [value]148 , [ppd_fee]149 , [ppd_freight_charges]150 , [ppd_misc_charges]151 , [consignee_name]152 , [consignee_addr##1]153 , [consignee_addr##2]154 , [consignee_addr##3]155 , [consignee_addr##4]156 , [consignee_city]157 , [consignee_state]158 , [consignee_zip]159 , [consignee_county]160 , [consignee_country]161 , [consignee_contact]162 , [consignee_phone]163 , [consignee_fax]164 , [consignee_tax_reg_num]165 , [consignor_name]166 , [consignor_addr##1]167 , [consignor_addr##2]168 , [consignor_addr##3]169 , [consignor_addr##4]170 , [consignor_city]171 , [consignor_state]172 , [consignor_zip]173 , [consignor_county]174 , [consignor_country]175 , [consignor_contact]176 , [consignor_phone]177 , [consignor_fax]178 , [invoicee_name]179 , [invoicee_addr##1]180 , [invoicee_addr##2]181 , [invoicee_addr##3]182 , [invoicee_addr##4]183 , [invoicee_city]184 , [invoicee_state]185 , [invoicee_zip]186 , [invoicee_county]187 , [invoicee_country]188 , [invoicee_contact]189 , [invoicee_phone]190 , [invoicee_fax]191 , [invoicee_tax_reg_num]192 , [producer_name]193 , [producer_addr##1]194 , [producer_addr##2]195 , [producer_addr##3]196 , [producer_addr##4]197 , [producer_city]198 , [producer_state]199 , [producer_zip]200 , [producer_county]201 , [producer_country]202 , [producer_contact]203 , [producer_phone]204 , [producer_fax]205 , [producer_tax_reg_num]206 , [exporter_name]207 , [exporter_addr##1]208 , [exporter_addr##2]209 , [exporter_addr##3]210 , [exporter_addr##4]211 , [exporter_city]212 , [exporter_state]213 , [exporter_zip]214 , [exporter_county]215 , [exporter_country]216 , [exporter_contact]217 , [exporter_phone]218 , [exporter_fax]219 , [exporter_tax_reg_num]220 , [RowPointer]221 , [NoteExistsFlag]222 , [CreatedBy]223 , [UpdatedBy]224 , [CreateDate]225 , [RecordDate]226 , [InWorkflow]227 , [TH_fob_point]228 , [TH_from_shipping_port]229 , [TH_to_shipping_port]230 , [TH_item_category]231 , [asn_printed]232 , [asn_extracted]233 , [consignor_contact_id]234 , [parent_container_num]235 , [Uf_Shipmet_Invoice]236 , [Uf_Shipment_values]237 )238 SELECT239 bt.[site_ref]240 , bt.[status]241 , bt.[cust_num]242 , bt.[cust_seq]243 , bt.[whse]244 , bt.[ship_loc]245 , bt.[ship_date]246 , bt.[qty_packages]247 , bt.[weight]248 , bt.[weight_u_m]249 , bt.[packer]250 , bt.[ship_code]251 , bt.[carrier_contact]252 , bt.[carrier_code]253 , bt.[veh_num]254 , bt.[pro_number]255 , bt.[tracking_number]256 , bt.[tracking_url]257 , bt.[route]258 , bt.[container]259 , bt.[pickup_date]260 , bt.[authorizer]261 , bt.[authorizer_title]262 , bt.[authorizer_phone]263 , bt.[authorizer_fax]264 , bt.[bol_printed]265 , bt.[proforma_printed]266 , bt.[customs_invoice_printed]267 , bt.[cert_of_origin_printed]268 , bt.[pack_slip_printed]269 , bt.[invoice_printed]270 , bt.[special_inst##1]271 , bt.[special_inst##2]272 , bt.[col_fee]273 , bt.[col_freight_charges]274 , bt.[col_misc_charges]275 , bt.[cod_amount]276 , bt.[value]277 , bt.[ppd_fee]278 , bt.[ppd_freight_charges]279 , bt.[ppd_misc_charges]280 , bt.[consignee_name]281 , bt.[consignee_addr##1]282 , bt.[consignee_addr##2]283 , bt.[consignee_addr##3]284 , bt.[consignee_addr##4]285 , bt.[consignee_city]286 , bt.[consignee_state]287 , bt.[consignee_zip]288 , bt.[consignee_county]289 , bt.[consignee_country]290 , bt.[consignee_contact]291 , bt.[consignee_phone]292 , bt.[consignee_fax]293 , bt.[consignee_tax_reg_num]294 , bt.[consignor_name]295 , bt.[consignor_addr##1]296 , bt.[consignor_addr##2]297 , bt.[consignor_addr##3]298 , bt.[consignor_addr##4]299 , bt.[consignor_city]300 , bt.[consignor_state]301 , bt.[consignor_zip]302 , bt.[consignor_county]303 , bt.[consignor_country]304 , bt.[consignor_contact]305 , bt.[consignor_phone]306 , bt.[consignor_fax]307 , bt.[invoicee_name]308 , bt.[invoicee_addr##1]309 , bt.[invoicee_addr##2]310 , bt.[invoicee_addr##3]311 , bt.[invoicee_addr##4]312 , bt.[invoicee_city]313 , bt.[invoicee_state]314 , bt.[invoicee_zip]315 , bt.[invoicee_county]316 , bt.[invoicee_country]317 , bt.[invoicee_contact]318 , bt.[invoicee_phone]319 , bt.[invoicee_fax]320 , bt.[invoicee_tax_reg_num]321 , bt.[producer_name]322 , bt.[producer_addr##1]323 , bt.[producer_addr##2]324 , bt.[producer_addr##3]325 , bt.[producer_addr##4]326 , bt.[producer_city]327 , bt.[producer_state]328 , bt.[producer_zip]329 , bt.[producer_county]330 , bt.[producer_country]331 , bt.[producer_contact]332 , bt.[producer_phone]333 , bt.[producer_fax]334 , bt.[producer_tax_reg_num]335 , bt.[exporter_name]336 , bt.[exporter_addr##1]337 , bt.[exporter_addr##2]338 , bt.[exporter_addr##3]339 , bt.[exporter_addr##4]340 , bt.[exporter_city]341 , bt.[exporter_state]342 , bt.[exporter_zip]343 , bt.[exporter_county]344 , bt.[exporter_country]345 , bt.[exporter_contact]346 , bt.[exporter_phone]347 , bt.[exporter_fax]348 , bt.[exporter_tax_reg_num]349 , bt.[RowPointer]350 , bt.[NoteExistsFlag]351 , @Username352 , @Username353 , @Today354 , @Today355 , bt.[InWorkflow]356 , bt.[TH_fob_point]357 , bt.[TH_from_shipping_port]358 , bt.[TH_to_shipping_port]359 , bt.[TH_item_category]360 , bt.[asn_printed]361 , bt.[asn_extracted]362 , bt.[consignor_contact_id]363 , bt.[parent_container_num]364 ,ISNULL(New_Invoice_num,bt.[Uf_Shipmet_Invoice]) --, bt.[Uf_Shipmet_Invoice] 365 , bt.[Uf_Shipment_values]366 FROM inserted bt 367 LEFT OUTER JOIN @NewKeys nk368 ON nk.New_RowPointer = bt.RowPointer369 ELSE370 -- INSERT did specify value(s) for the Identity column shipment_id.371 -- (Inserter must have already SET IDENTITY_INSERT correctly, to avoid error 544.)372 INSERT [shipment_mst] (373 [site_ref]374 , [shipment_id]375 , [status]376 , [cust_num]377 , [cust_seq]378 , [whse]379 , [ship_loc]380 , [ship_date]381 , [qty_packages]382 , [weight]383 , [weight_u_m]384 , [packer]385 , [ship_code]386 , [carrier_contact]387 , [carrier_code]388 , [veh_num]389 , [pro_number]390 , [tracking_number]391 , [tracking_url]392 , [route]393 , [container]394 , [pickup_date]395 , [authorizer]396 , [authorizer_title]397 , [authorizer_phone]398 , [authorizer_fax]399 , [bol_printed]400 , [proforma_printed]401 , [customs_invoice_printed]402 , [cert_of_origin_printed]403 , [pack_slip_printed]404 , [invoice_printed]405 , [special_inst##1]406 , [special_inst##2]407 , [col_fee]408 , [col_freight_charges]409 , [col_misc_charges]410 , [cod_amount]411 , [value]412 , [ppd_fee]413 , [ppd_freight_charges]414 , [ppd_misc_charges]415 , [consignee_name]416 , [consignee_addr##1]417 , [consignee_addr##2]418 , [consignee_addr##3]419 , [consignee_addr##4]420 , [consignee_city]421 , [consignee_state]422 , [consignee_zip]423 , [consignee_county]424 , [consignee_country]425 , [consignee_contact]426 , [consignee_phone]427 , [consignee_fax]428 , [consignee_tax_reg_num]429 , [consignor_name]430 , [consignor_addr##1]431 , [consignor_addr##2]432 , [consignor_addr##3]433 , [consignor_addr##4]434 , [consignor_city]435 , [consignor_state]436 , [consignor_zip]437 , [consignor_county]438 , [consignor_country]439 , [consignor_contact]440 , [consignor_phone]441 , [consignor_fax]442 , [invoicee_name]443 , [invoicee_addr##1]444 , [invoicee_addr##2]445 , [invoicee_addr##3]446 , [invoicee_addr##4]447 , [invoicee_city]448 , [invoicee_state]449 , [invoicee_zip]450 , [invoicee_county]451 , [invoicee_country]452 , [invoicee_contact]453 , [invoicee_phone]454 , [invoicee_fax]455 , [invoicee_tax_reg_num]456 , [producer_name]457 , [producer_addr##1]458 , [producer_addr##2]459 , [producer_addr##3]460 , [producer_addr##4]461 , [producer_city]462 , [producer_state]463 , [producer_zip]464 , [producer_county]465 , [producer_country]466 , [producer_contact]467 , [producer_phone]468 , [producer_fax]469 , [producer_tax_reg_num]470 , [exporter_name]471 , [exporter_addr##1]472 , [exporter_addr##2]473 , [exporter_addr##3]474 , [exporter_addr##4]475 , [exporter_city]476 , [exporter_state]477 , [exporter_zip]478 , [exporter_county]479 , [exporter_country]480 , [exporter_contact]481 , [exporter_phone]482 , [exporter_fax]483 , [exporter_tax_reg_num]484 , [RowPointer]485 , [NoteExistsFlag]486 , [CreatedBy]487 , [UpdatedBy]488 , [CreateDate]489 , [RecordDate]490 , [InWorkflow]491 , [TH_fob_point]492 , [TH_from_shipping_port]493 , [TH_to_shipping_port]494 , [TH_item_category]495 , [asn_printed]496 , [asn_extracted]497 , [consignor_contact_id]498 , [parent_container_num]499 , [Uf_Shipmet_Invoice]500 , [Uf_Shipment_values]501 )502 SELECT503 bt.[site_ref]504 , bt.[shipment_id]505 , bt.[status]506 , bt.[cust_num]507 , bt.[cust_seq]508 , bt.[whse]509 , bt.[ship_loc]510 , bt.[ship_date]511 , bt.[qty_packages]512 , bt.[weight]513 , bt.[weight_u_m]514 , bt.[packer]515 , bt.[ship_code]516 , bt.[carrier_contact]517 , bt.[carrier_code]518 , bt.[veh_num]519 , bt.[pro_number]520 , bt.[tracking_number]521 , bt.[tracking_url]522 , bt.[route]523 , bt.[container]524 , bt.[pickup_date]525 , bt.[authorizer]526 , bt.[authorizer_title]527 , bt.[authorizer_phone]528 , bt.[authorizer_fax]529 , bt.[bol_printed]530 , bt.[proforma_printed]531 , bt.[customs_invoice_printed]532 , bt.[cert_of_origin_printed]533 , bt.[pack_slip_printed]534 , bt.[invoice_printed]535 , bt.[special_inst##1]536 , bt.[special_inst##2]537 , bt.[col_fee]538 , bt.[col_freight_charges]539 , bt.[col_misc_charges]540 , bt.[cod_amount]541 , bt.[value]542 , bt.[ppd_fee]543 , bt.[ppd_freight_charges]544 , bt.[ppd_misc_charges]545 , bt.[consignee_name]546 , bt.[consignee_addr##1]547 , bt.[consignee_addr##2]548 , bt.[consignee_addr##3]549 , bt.[consignee_addr##4]550 , bt.[consignee_city]551 , bt.[consignee_state]552 , bt.[consignee_zip]553 , bt.[consignee_county]554 , bt.[consignee_country]555 , bt.[consignee_contact]556 , bt.[consignee_phone]557 , bt.[consignee_fax]558 , bt.[consignee_tax_reg_num]559 , bt.[consignor_name]560 , bt.[consignor_addr##1]561 , bt.[consignor_addr##2]562 , bt.[consignor_addr##3]563 , bt.[consignor_addr##4]564 , bt.[consignor_city]565 , bt.[consignor_state]566 , bt.[consignor_zip]567 , bt.[consignor_county]568 , bt.[consignor_country]569 , bt.[consignor_contact]570 , bt.[consignor_phone]571 , bt.[consignor_fax]572 , bt.[invoicee_name]573 , bt.[invoicee_addr##1]574 , bt.[invoicee_addr##2]575 , bt.[invoicee_addr##3]576 , bt.[invoicee_addr##4]577 , bt.[invoicee_city]578 , bt.[invoicee_state]579 , bt.[invoicee_zip]580 , bt.[invoicee_county]581 , bt.[invoicee_country]582 , bt.[invoicee_contact]583 , bt.[invoicee_phone]584 , bt.[invoicee_fax]585 , bt.[invoicee_tax_reg_num]586 , bt.[producer_name]587 , bt.[producer_addr##1]588 , bt.[producer_addr##2]589 , bt.[producer_addr##3]590 , bt.[producer_addr##4]591 , bt.[producer_city]592 , bt.[producer_state]593 , bt.[producer_zip]594 , bt.[producer_county]595 , bt.[producer_country]596 , bt.[producer_contact]597 , bt.[producer_phone]598 , bt.[producer_fax]599 , bt.[producer_tax_reg_num]600 , bt.[exporter_name]601 , bt.[exporter_addr##1]602 , bt.[exporter_addr##2]603 , bt.[exporter_addr##3]604 , bt.[exporter_addr##4]605 , bt.[exporter_city]606 , bt.[exporter_state]607 , bt.[exporter_zip]608 , bt.[exporter_county]609 , bt.[exporter_country]610 , bt.[exporter_contact]611 , bt.[exporter_phone]612 , bt.[exporter_fax]613 , bt.[exporter_tax_reg_num]614 , bt.[RowPointer]615 , bt.[NoteExistsFlag]616 , @Username617 , @Username618 , @Today619 , @Today620 , bt.[InWorkflow]621 , bt.[TH_fob_point]622 , bt.[TH_from_shipping_port]623 , bt.[TH_to_shipping_port]624 , bt.[TH_item_category]625 , bt.[asn_printed]626 , bt.[asn_extracted]627 , bt.[consignor_contact_id]628 , bt.[parent_container_num]629 ,ISNULL(New_Invoice_num,bt.[Uf_Shipmet_Invoice])--bt.[Uf_Shipmet_Invoice]630 , bt.[Uf_Shipment_values]631 FROM inserted bt632 LEFT OUTER JOIN @NewKeys nk633 ON nk.New_RowPointer = bt.RowPointer634 635 -- The AFTER INSERT Triggers fire now, in the following order:636 -- shipment_mstIup First (if exists; manually maintained)637 -- shipment_mstInsAudit and/or any custom triggers (if exist; generated by AuditLoggingGenCodeSp and/or manually maintained)638 -- shipment_mstIupReplicate Last (if exists; generated by ReplicationTriggerIupCodeSp)639 640 RETURN