SQL Server 行级触发器
我尝试通过使用游标来实现行级删除触发器，但是当尝试从表中删除任何行时，需要很长时间。
我不明白它到底卡在哪里。
/****** 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
I tried to achieve row level delete trigger by using cursor but when in trying yo delete the any row from table it tooks so long time.
I could not understand where exactly it stuck.
/****** 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
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我认为你应该将光标转换为插入选择句子。我不确定这是否能解决您的问题，但无论如何这是一个很好的最佳实践。
尽量避免使用游标，这会带来更好的性能。
I think you should transform your cursor in an insert-select sentence. I'm not sure this will solve your problem, but it's a good best practice anyway.
Try to avoid cursors, and this will result in better performance.