blockquote code font size face Courier New color black font color 0000

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
<blockquote><code><font size="2" face="Courier New" color="black"><font color="#0000ff">ALTER</font> <font color="#0000ff">TRIGGER</font> [DBO].[TROUTE_CHANNEL_LINKS_DELETE]
<font color="#0000ff">ON</font> [DBO].[TROUTE_CHANNEL_LINKS]
<font color="#0000ff">FOR</font> <font color="#0000ff">DELETE</font> <font color="#0000ff">AS</font>
<font color="#0000ff">BEGIN</font>
&nbsp;&nbsp;<font color="#008000">-- ВЫБОР ТОЧЕК МАРШРУТА КОТОРЫЕ НЕОБХОДИМО ПЕРЕНУМЕРОВАТЬ</font>
&nbsp;&nbsp;<font color="#0000ff">CREATE</font> <font color="#0000ff">TABLE</font> #TEMPTABLE (
&nbsp;&nbsp;&nbsp;&nbsp;ROUTE_ID UNIQUEIDENTIFIER,
&nbsp;&nbsp;&nbsp;&nbsp;POINT_ID UNIQUEIDENTIFIER,
&nbsp;&nbsp;&nbsp;&nbsp;CHANNEL_SETTING_ID UNIQUEIDENTIFIER,
&nbsp;&nbsp;&nbsp;&nbsp;SYNCRO <font color="#0000ff">INT</font>,
&nbsp;&nbsp;&nbsp;&nbsp;ORDER_ID <font color="#0000ff">INT</font>
&nbsp;&nbsp;)
&nbsp;&nbsp;<font color="#0000ff">INSERT</font> <font color="#0000ff">INTO</font> #TEMPTABLE(ROUTE_ID, POINT_ID, CHANNEL_SETTING_ID, SYNCRO)
&nbsp;&nbsp;<font color="#0000ff">SELECT</font> RCL.ROUTE_ID, RCL.POINT_ID, RCL2.CHANNEL_SETTING_ID, CS.SYNCRO_GROUP_ID&nbsp;<font color="#0000ff">FROM</font>
&nbsp;&nbsp;&nbsp;&nbsp;(&nbsp;&nbsp;<font color="#0000ff">SELECT</font> ROUTE_ID, POINT_ID <font color="#0000ff">FROM</font> TROUTE_CHANNEL_LINKS RCL
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color="#0000ff">WHERE</font> ROUTE_ID <font color="#0000ff">IN</font> (<font color="#0000ff">SELECT</font> ROUTE_ID <font color="#0000ff">FROM</font> DELETED)
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color="#0000ff">GROUP</font> <font color="#0000ff">BY</font> ROUTE_ID, POINT_ID
&nbsp;&nbsp;&nbsp;&nbsp;) <font color="#0000ff">AS</font> RCL
&nbsp;&nbsp;<font color="#0000ff">INNER</font> <font color="#0000ff">JOIN</font> TROUTE_CHANNEL_LINKS RCL2 <font color="#0000ff">ON</font> RCL2.ROUTE_ID = RCL.ROUTE_ID <font color="#0000ff">AND</font> RCL2.POINT_ID = RCL.POINT_ID
&nbsp;&nbsp;<font color="#0000ff">INNER</font> <font color="#0000ff">JOIN</font> TCHANNEL_SETTINGS CS <font color="#0000ff">ON</font> CS.CHANNEL_SETTING_ID = RCL2.CHANNEL_SETTING_ID
&nbsp;&nbsp;<font color="#0000ff">ORDER</font> <font color="#0000ff">BY</font> RCL.ROUTE_ID, RCL.POINT_ID, RCL2.ORDER_ID
&nbsp;&nbsp;<font color="#0000ff">DECLARE</font> RENUM_CURSOR <font color="#0000ff">CURSOR</font> <font color="#0000ff">FOR</font> <font color="#0000ff">SELECT</font> ROUTE_ID, POINT_ID, CHANNEL_SETTING_ID, SYNCRO <font color="#0000ff">FROM</font> #TEMPTABLE
&nbsp;&nbsp;
&nbsp;&nbsp;<font color="#0000ff">DECLARE</font> @OLD_ROUTE_ID UNIQUEIDENTIFIER
&nbsp;&nbsp;<font color="#0000ff">DECLARE</font> @ROUTE_ID UNIQUEIDENTIFIER
&nbsp;&nbsp;<font color="#0000ff">DECLARE</font> @POINT_ID UNIQUEIDENTIFIER
&nbsp;&nbsp;<font color="#0000ff">DECLARE</font> @CHANNEL_SETTING_ID UNIQUEIDENTIFIER
&nbsp;&nbsp;<font color="#0000ff">DECLARE</font> @SYNCRO <font color="#0000ff">INT</font>
&nbsp;&nbsp;
&nbsp;&nbsp;<font color="#0000ff">DECLARE</font> @SYNCRO_GROUP <font color="#0000ff">INT</font>
&nbsp;&nbsp;<font color="#0000ff">DECLARE</font> @COUNTER <font color="#0000ff">INT</font>
&nbsp;&nbsp;<font color="#008000">-- "ЛОГИЧЕСКОЕ" ЗНАЧЕНИЕ @SYNCRO_GROUP СДЕЛАНО "ИНВЕРСИРОВАННЫМ" ДЛЯ ТОГО ЧТО-БЫ СОКРАТИТЬ ЛОГИКУ УВЕЛИЧЕНИЯ НОМЕРА ТОЧКИ В СИНХРОГРУППАХ</font>
&nbsp;&nbsp;<font color="#0000ff">SET</font> @SYNCRO_GROUP = 1
&nbsp;&nbsp;<font color="#0000ff">SET</font> @COUNTER = 1
&nbsp;&nbsp;
&nbsp;&nbsp;<font color="#008000">-- ПЕРЕНУМЕРАЦИЯ </font>
&nbsp;&nbsp;<font color="#0000ff">FETCH</font> <font color="#0000ff">NEXT</font> <font color="#0000ff">FROM</font> RENUM_CURSOR <font color="#0000ff">INTO</font> @ROUTE_ID, @POINT_ID, @CHANNEL_SETTING_ID, @SYNCRO
&nbsp;&nbsp;<font color="#0000ff">WHILE</font> <font color="#0000ff">@@FETCH_STATUS</font> = 0
&nbsp;&nbsp;<font color="#0000ff">BEGIN</font>
&nbsp;&nbsp;&nbsp;&nbsp;<font color="#0000ff">IF</font> @OLD_ROUTE_ID &#60;&#62; @ROUTE_ID
&nbsp;&nbsp;&nbsp;&nbsp;<font color="#0000ff">BEGIN</font>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color="#0000ff">SET</font> @COUNTER = 1
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color="#0000ff">SET</font> @SYNCRO_GROUP = <font color="#0000ff">CASE</font> @SYNCRO <font color="#0000ff">WHEN</font> -1 <font color="#0000ff">THEN</font> 0 <font color="#0000ff">WHEN</font> 1 <font color="#0000ff">THEN</font> 1 <font color="#0000ff">END</font>
&nbsp;&nbsp;&nbsp;&nbsp;<font color="#0000ff">END</font>
&nbsp;&nbsp;&nbsp;&nbsp;<font color="#0000ff">ELSE</font> <font color="#0000ff">BEGIN</font>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color="#0000ff">IF</font> @SYNCRO = -1
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color="#0000ff">BEGIN</font>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color="#0000ff">SET</font> @COUNTER = @COUNTER + 1
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color="#0000ff">SET</font> @SYNCRO_GROUP = 1
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color="#0000ff">END</font>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color="#0000ff">ELSE</font> <font color="#0000ff">BEGIN</font>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color="#0000ff">SET</font> @COUNTER = @COUNTER + @SYNCRO_GROUP
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color="#0000ff">SET</font> @SYNCRO_GROUP = 0
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color="#0000ff">END</font>
&nbsp;&nbsp;&nbsp;&nbsp;<font color="#0000ff">END</font>
&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;<font color="#0000ff">UPDATE</font> #TEMPTABLE <font color="#0000ff">SET</font> ORDER_ID = @COUNTER <font color="#0000ff">WHERE</font> @ROUTE_ID = ROUTE_ID <font color="#0000ff">AND</font> @POINT_ID = POINT_ID <font color="#0000ff">AND</font> @CHANNEL_SETTING_ID = CHANNEL_SETTING_ID
&nbsp;&nbsp;&nbsp;&nbsp;<font color="#0000ff">SET</font> @OLD_ROUTE_ID = @ROUTE_ID
&nbsp;&nbsp;&nbsp;&nbsp;<font color="#0000ff">FETCH</font> <font color="#0000ff">NEXT</font> <font color="#0000ff">FROM</font> RENUM_CURSOR <font color="#0000ff">INTO</font> @ROUTE_ID, @POINT_ID, @CHANNEL_SETTING_ID, @SYNCRO
&nbsp;&nbsp;<font color="#0000ff">END</font>
&nbsp;&nbsp;--ОБНОВЛЕНИЕ НОМЕРОВ
&nbsp;&nbsp;
&nbsp;&nbsp;<font color="#0000ff">UPDATE</font> TROUTE_CHANNEL_LINKS
&nbsp;&nbsp;<font color="#0000ff">SET</font> ORDER_ID = TMP.ORDER_ID
&nbsp;&nbsp;<font color="#0000ff">FROM</font> #TEMPTABLE TMP
&nbsp;&nbsp;<font color="#0000ff">INNER</font> <font color="#0000ff">JOIN</font> TROUTE_CHANNEL_LINKS RCL2 <font color="#0000ff">ON</font>
&nbsp;&nbsp;&nbsp;&nbsp;RCL2.ROUTE_ID = TMP.ROUTE_ID <font color="#0000ff">AND</font> RCL2.POINT_ID = TMP.POINT_ID <font color="#0000ff">AND</font> RCL2.CHANNEL_SETTING_ID = TMP.CHANNEL_SETTING_ID
&nbsp;&nbsp;<font color="#0000ff">CLOSE</font> RENUM_CURSOR
&nbsp;&nbsp;<font color="#0000ff">DEALLOCATE</font> RENUM_CURSOR
&nbsp;&nbsp;<font color="#0000ff">DROP</font> <font color="#0000ff">TABLE</font> #TEMPTABLE
<font color="#0000ff">END</font>
</font>
<font size="1" color="gray">* This source code was highlighted with <a href="http://virtser.net/blog/post/source-code-highlighter.aspx"><font size="1" color="gray">Source Code Highlighter</font></a>.</font></code></blockquote>