SQL游标写入时触发
时间：2021-07-01 10:21:17
帮助过：4人阅读
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
