·只有inserted表有数据时，当前操作为insert；·inserted和deleted两张表都有数据时，当前操作为update；·只有deleted表有数据时，当前操作为delete。
1.
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.
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 pubsCREATE trigger trigger_update on authorsfor updateasbegin print('update lastname=hoho')endCREATE proc Authors_procInsertas 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”即进入触发器代码