Cursor in a trigger

Hi all,
Why it is not recommended to use a cursor in a Trigger?
ref:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_08_3ypf.asp
I want to perform a specific task for each row of the inserted/deleted
table, is there another way than using a Cursor'
Thank you in advance.
--
--
G.Haddad
--> is there another way than using a Cursor'
Impossible for us to say unless you explain what " perform a specific task for each row " means. I
suggest that you post what it is that you need to accomplish from your trigger and someone might be
able to suggest a set based solution for that.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"jouj" <jouj@.discussions.microsoft.com> wrote in message
news:B5213F96-9CF8-432A-B025-B0CAB0EF7B91@.microsoft.com...
> Hi all,
> Why it is not recommended to use a cursor in a Trigger?
> ref:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_08_3ypf.asp
> I want to perform a specific task for each row of the inserted/deleted
> table, is there another way than using a Cursor'
> Thank you in advance.
> --
> --
> G.Haddad
> --|||Hi
To make a long story shorter , a trigger opens transaction and it remains
opened till will commit. Having a cursor inside the trigger will hit your
perfomance and as result you may face soem blocking or locking issues
If you show us DDL + smaple data + expected result we may suggest a solution
without using a cursor at all.
"jouj" <jouj@.discussions.microsoft.com> wrote in message
news:B5213F96-9CF8-432A-B025-B0CAB0EF7B91@.microsoft.com...
> Hi all,
> Why it is not recommended to use a cursor in a Trigger?
> ref:
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_08_3ypf.asp
> I want to perform a specific task for each row of the inserted/deleted
> table, is there another way than using a Cursor'
> Thank you in advance.
> --
> --
> G.Haddad
> --|||hi jouj
It is advisible not to use a cursor anywhere, not only in a trigger.
The iterations increase if you to use a cursor and cursors can be avoided by
changing the query where ever possible.
even in your case INSERTED/DELETED tables, try to modify the query so that
cursor can be avoided. If there is no other way u can very well use a cursor
in that place.
Just give us the requirement, and we will try to see if we can avoid the
cursor
--
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"jouj" wrote:
> Hi all,
> Why it is not recommended to use a cursor in a Trigger?
> ref:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_08_3ypf.asp
> I want to perform a specific task for each row of the inserted/deleted
> table, is there another way than using a Cursor'
> Thank you in advance.
> --
> --
> G.Haddad
> --|||Thanks for your fast reply,
My code is similar to:
==============================================CREATE TRIGGER T_MyTable_ITrig ON dbo.MyTable FOR INSERT AS
SET NOCOUNT ON
DECLARE @.MyID nvarchar(14)
DECLARE Cur_MyID CURSOR FOR
SELECT MyID FROM inserted
OPEN Cur_MyID
FETCH NEXT FROM Cur_MyID INTO @.MyID
WHILE @.@.FETCH_STATUS = 0
BEGIN
EXEC
dbo.SP_UpdateSeveralTables_using_MyID_and_Other_Variables_and_UDF_Values
@.MyID
/*one of the tables might be MyTable itself*/
FETCH NEXT FROM Cur_MyID INTO @.MyID
END
CLOSE Cur_MyID
DEALLOCATE Cur_MyID
GO
==============================================
Thank you again.|||The key here is what actions SP_UpdateSeveralTables_using_MyID_and_Other_Variables_and_UDF_Values
@.MyID performs. It might be possible to incorporate this into a set based solution, but without
knowing that it does, we can't say anything.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"jouj" <jouj@.discussions.microsoft.com> wrote in message
news:EBCFC96A-4621-43AC-8B7D-9C1906C95FC5@.microsoft.com...
> Thanks for your fast reply,
> My code is similar to:
> ==============================================> CREATE TRIGGER T_MyTable_ITrig ON dbo.MyTable FOR INSERT AS
> SET NOCOUNT ON
> DECLARE @.MyID nvarchar(14)
> DECLARE Cur_MyID CURSOR FOR
> SELECT MyID FROM inserted
> OPEN Cur_MyID
> FETCH NEXT FROM Cur_MyID INTO @.MyID
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> EXEC
> dbo.SP_UpdateSeveralTables_using_MyID_and_Other_Variables_and_UDF_Values
> @.MyID
> /*one of the tables might be MyTable itself*/
> FETCH NEXT FROM Cur_MyID INTO @.MyID
> END
> CLOSE Cur_MyID
> DEALLOCATE Cur_MyID
> GO
> ==============================================> Thank you again.|||The SP looks like that:
SP: Using @.MyID
=========================================================
Declare @.V1 nvarchar(20)
Declare @.V2 nvarchar(20)
SELECT @.V1
FROM TableList
WHERE Tablen.MyID = @.MyID AND WhereClause
/*If then else*/
Case @.V1
...
..
...
Select @.V2
From TableList
WHERE Tablej.V1 = @.V1
/*end if*/
Update Table1
Set Cost = dbo.Get_Rates(@.V1,@.V2)
WHERE Table1.MyID = @.MyID
/*The UDF Get_Rates performs miscellaneous Selects and comparaisons from
different tables.*/
=========================================================
and The trigger on Update:
****************************************************************
Declare @.iV1 , @.iV2
Declare @.dV1 , @.dV2
Declare @.MyID
Select @.MyID = MyID , @.iV1 = V1 , @.iV2 = V2 from inserted
Select @.dV1 = V1 , @.dV2 = V2 from deleted
If @.iV1 <> @.iv2
Begin
SP @.MyID
...
End
Else
Begin
SP2 @.MyID
End
/*SP2 is similar to SP but from different tables and with different tests*/
Go
****************************************************************
By the way, if you find this thing boring or annoying, drop it and thanks
for your effort and patience.
jouj|||Based on this fragment of code you can apparently replace your trigger with
something like this:
CREATE TRIGGER T_MyTable_ITrig ON dbo.MyTable
FOR INSERT AS
UPDATE Table1
SET cost = (SELECT dbo.Get_Rates(v1,v2)
FROM Inserted
WHERE myid = Table1.myid)
WHERE EXISTS
(SELECT *
FROM Inserted
WHERE myid = Table1.myid)
Your IF statements can probably be replaced by CASE expressions (you didn't
include them so I'm just guessing). Do consider however, that your first
priority should be to avoid storing redundant data. If columns will always
be derived from others in the same database then they don't need to exist as
actual columns - try putting them in views and queries instead. Over-complex
trigger logic may just be a symptom of some design problems.
--
David Portas
SQL Server MVP
--sql