1.
|
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
|
CREATE TRIGGER trTask_Update ON
dbo.Task
FOR
UPDATE
AS BEGIN
DECLARE
@TaskId INT
DECLARE
@NewTaskOrder INT
DECLARE
@WorkstreamId INT
DECLARE
@OldTaskOrder INT
--update BEGIN
SELECT
@TaskId = id ,
@OldTaskOrder = TaskOrder ,
@WorkstreamId = WorkstreamId
FROM
deleted
SELECT
@NewTaskOrder = TaskOrder
FROM
dbo.Task
WHERE
Id = @TaskId
--When updating a row, if old value is greater than new value, then +1 all values that are >= the new value and < the old value IF @OldTaskOrder > @NewTaskOrder
BEGIN
UPDATE
dbo.Task
SET
TaskOrder = TaskOrder + 1
WHERE
WorkstreamId = @WorkstreamId
AND
TaskOrder >= @NewTaskOrder
AND
TaskOrder < @OldTaskOrder
AND
Id <> @TaskId
END
IF @OldTaskOrder < @NewTaskOrder
BEGIN UPDATE
dbo.Task
SET
TaskOrder = TaskOrder - 1
WHERE
WorkstreamId = @WorkstreamId
AND
TaskOrder <= @NewTaskOrder
AND
TaskOrder > @OldTaskOrder
AND
Id <> @TaskId
END
END
END
|
2.
|
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
|
CREATE TRIGGER trTask_Insert ON
dbo.Task
FOR
INSERT
AS BEGIN
DECLARE
@TaskId INT
DECLARE
@NewTaskOrder INT
DECLARE
@WorkstreamId INT
DECLARE
@OldTaskOrder INT
--insert BEGIN
SELECT
@TaskId = id ,
@NewTaskOrder = TaskOrder ,
@WorkstreamId = WorkstreamId
FROM
INSERTED
--When inserting a new,+1 to all task orders that are equal to or greater than the newly inserted task‘s task order UPDATE
dbo.Task
SET
TaskOrder = TaskOrder + 1
WHERE
WorkstreamId = @WorkstreamId
AND
TaskOrder >= @NewTaskOrder
AND
Id <> @TaskId
END
END
|
3.如何调试触发器：
一、打开SQL查询分析器
二、将以下Sql语句复制到查询窗口并运行
use pubs
CREATE trigger
trigger_update on authors
for update
as
begin
print(‘update
lastname=hoho‘)
end
CREATE proc
Authors_procInsert
as
begin
update authors set
au_lname=‘HOHO‘ where au_id=‘172-32-1176‘
end
三、在左边的对象浏览器中选择pubs->存储过程在Authors_procInsert(如未出现请刷新pubs数据库）上右击‘Execute
stored procedure’，设置参数点击确定打开生成的执行存储过程的脚本 -> 点击SQL Server Management 菜单上的Debug
-> Start Debugging...
四、当运行到" update authors set
au_lname=‘HOHO‘ where au_id=‘172-32-1176‘“时按“F11”即进入触发器代码