CODEWITHSUNDEEP
sql-servertriggers
Dhananjay
Dhananjay

Reputation: 1

SQL Server row level triggers

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

Upvotes: 0

Views: 1519

Answers (1)

Jonathan
Jonathan

Reputation: 12025

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.

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

Try to avoid cursors, and this will result in better performance.

Upvotes: 3

Related Questions