SQL游标写入时触发

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 a <Prefix> followed 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 BEGIN 100 CLOSE InvoiceKey 101 DEALLOCATE InvoiceKey 102 END 103 ------robert update 104 105 106 IF (SELECT TOP 1 shipment_id FROM inserted) = 0 107 -- 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 SELECT 239 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 , @Username 352 , @Username 353 , @Today 354 , @Today 355 , 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 nk 368 ON nk.New_RowPointer = bt.RowPointer 369 ELSE 370 -- 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