SQL Server行级触发器

Question

我试图使用游标来实现行级delete触发器，但是当我尝试从表中删除任何行时，它似乎花费了很长时间。

我不能理解它到底停留在哪里。

/****** Object:  Trigger [delStudent]    Script Date: 06/24/2010 12:33:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [delStudent]
   ON  [dbo].[Student]
   FOR DELETE
AS 
DECLARE @Roll as varChar(50); 
DECLARE @Name as varChar(50);
DECLARE @Age as int;
DECLARE @UserName as varChar(50);

SELECT @UserName=SYSTEM_USER;

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;

declare CurD cursor for select roll, Sname, age from deleted    

open CurD

WHILE @@FETCH_STATUS = 0
 BEGIN
    INSERT INTO [dbo].[Audit]
            (roll,sname,age,userId)
    VALUES
            (@Roll,@Name,@Age,@UserName)
 END    
COMMIT TRANSACTION;
Close CurD
DEALLOCATE CurD

Answer 1

我认为你应该在插入-选择语句中转换你的光标。我不确定这是否能解决您的问题，但不管怎样，这是一个很好的最佳实践。

INSERT  [dbo].[Audit] (roll,sname,age,userId)
SELECT 'FIELDS FROM DELETED', SYSTEM_USER 
FROM deleted

尽量避免游标，这将带来更好的性能。