SQL Server触发器以及如何在SQL Server Manager中调试触发器
·只有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 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”即进入触发器代码