Cursor in a trigger

Hi all,
Why it is not recommended to use a cursor in a Trigger?
ref:
http://msdn.microsoft.com/library/de...es_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/de...es_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/de...es_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/de...es_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_Va riables_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_Variab les_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_Va riables_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

Cursor in a trigger

Hi all,
Why it is not recommended to use a cursor in a Trigger?
ref:
http://msdn.microsoft.com/library/d...>
_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 f
or each row " means. I
suggest that you post what it is that you need to accomplish from your trigg
er 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/d...
es_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:
>
[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_08_3ypf.asp[/ur
l]
> 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/d...
es_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_Ot
her_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_Ot
her_Var
iables_and_UDF_Values
@.MyID performs. It might be possible to incorporate this into a set based so
lution, 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_Ot
her_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
--

Cursor

I have to perform a number of calculations on a table but these
calculations need intermediate values ( like for example I have to
first calculate one value before I can use it in the next step of the
calcualtion). There are two ways of doing this:
1. Have columns for intermediate values ( it is possible as my
calculations are on a temp tbl) and use update statements. One update
statement for every step in the calculation.
2. Have a cursor, loop through the resultset and do the calculations
like we would in a programming language like C#.
Which one is better? Are updates ( a number of them) faster than a
cursor?
Thanks.John
A set based solution is much faster that cursors, however since you have
not posted DDL+ sample data it is hard to suggest something
"John Smith" <postmaster@.sumanthcp.plus.com> wrote in message
news:1139492787.074798.293960@.g14g2000cwa.googlegroups.com...
> I have to perform a number of calculations on a table but these
> calculations need intermediate values ( like for example I have to
> first calculate one value before I can use it in the next step of the
> calcualtion). There are two ways of doing this:
>
> 1. Have columns for intermediate values ( it is possible as my
> calculations are on a temp tbl) and use update statements. One update
> statement for every step in the calculation.
>
> 2. Have a cursor, loop through the resultset and do the calculations
> like we would in a programming language like C#.
>
> Which one is better? Are updates ( a number of them) faster than a
> cursor?
>
> Thanks.
>|||Cursors are usually slower than an SQL operation which updates all the rows
in
one go.
I would only use a cursor for this sort of operation if there were any
locking issues.
Are Riksaasen
"John Smith" wrote:

> I have to perform a number of calculations on a table but these
> calculations need intermediate values ( like for example I have to
> first calculate one value before I can use it in the next step of the
> calcualtion). There are two ways of doing this:
>
> 1. Have columns for intermediate values ( it is possible as my
> calculations are on a temp tbl) and use update statements. One update
> statement for every step in the calculation.
>
> 2. Have a cursor, loop through the resultset and do the calculations
> like we would in a programming language like C#.
>
> Which one is better? Are updates ( a number of them) faster than a
> cursor?
>
> Thanks.
>|||John Smith wrote:
> I have to perform a number of calculations on a table but these
> calculations need intermediate values ( like for example I have to
> first calculate one value before I can use it in the next step of the
> calcualtion). There are two ways of doing this:
>
> 1. Have columns for intermediate values ( it is possible as my
> calculations are on a temp tbl) and use update statements. One update
> statement for every step in the calculation.
>
> 2. Have a cursor, loop through the resultset and do the calculations
> like we would in a programming language like C#.
>
> Which one is better? Are updates ( a number of them) faster than a
> cursor?
>
> Thanks.
Neither of the above if you can avoid them. Create derived columns in
your queries or updates rather than using temp tables. Cursors are the
method of last resort for anything in SQL.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--