SQL Server触发器以及如何在SQL Server Manager中调试触发器

·只有inserted表有数据时，当前操作为insert；
·inserted和deleted两张表都有数据时，当前操作为update；
·只有deleted表有数据时，当前操作为delete。

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”即进入触发器代码