can cursors be made permanently reside in memory?

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

hi, i am implementing cursor in trigger
it works well for the first time when the trigger is implemented and the cursor is declared for the first time.
but later when the trigger is implemented again..
it gives an error that the particular cursor is already declared..

so is there any way to make cursor permanently reside in memory??

Posted 2-Jan-12 6:21am

Sachin gulati

Add a Solution

2 solutions

Solution 2

As this was a bit different question I added a new solution.

Basically the order of the triggers firing is not guaranteed. However, it's possible to set the first or the last firing AFTER trigger but relying on the execution order is something to avoid. If you need to set the first or the last, see: sp_settriggerorder[^]

Posted 2-Jan-12 7:53am

Wendelius

Comments

Sachin gulati 2-Jan-12 15:13pm

actually my second trigger is dependent on the first trigger so i need to use it.
as you referred.. i am using this code:

exec sp_settriggerorder @triggername = 'send_all',
@order = 'first',
@stmttype = 'insert',
@namespace = null

exec sp_settriggerorder @triggername = 'inbox',
@order = 'last',
@stmttype = 'insert',
@namespace = null
go

but unfortunately it is not working..
my 2nd trigger "inbox" always get executed before 1st "send_all"
both the triggers fire after an insert command on the database.
am i doing something wrong?

Wendelius 2-Jan-12 15:50pm

Did the calls succeed and were you using the correct database? Also why do you suspect that the triggers fire in wrong order? Is the last one seeing wrong data or something like that?

Sachin gulati 3-Jan-12 6:14am

i have 3 triggers on the same table after insert command..

1 has to be performed first..
1 has to be performed after it because it works on the coloumnn first trigger just updated..
and the last one can be fired in any order... it is independent...

i am fetching data from the inserted table in all the cases.
which is always the same as before not updated by the first trigger.
so that is the reason why my triggers are not working properly..

am i right?

i have found some other way to complete my task independent of the order of the trigger and i also reduced the use of unnecessary triggers.

Wendelius 3-Jan-12 12:40pm

First, sorry for late reply. I have either missed this comment or never received it...

Haven't tested this, but could it be that you're actually fetching the first inserted row from the inserted table.

Anyhow, I feel that if you just possibly can, try using for example stored procedures etc and coll them from a single trigger. Handling the trigger dependencies can be a real mess if they are not independent.

Sachin gulati 3-Jan-12 12:29pm

and yeah it was nice talking to you... thanks alot mika... :)

Wendelius 3-Jan-12 12:40pm

You're welcome :)

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2012-01-02T06:33:00

Solution 1

Have you DEALLOCATEd[^] the cursor in the end of the trigger.

If you're thinking about the performance, SQL Server has 'special' memory structures which are meant to handle situations when the same trigger, procedure etc is called. SQL Server caches both the compiled version and the optimization. So in performance point of view, in normal situations, you don't have to worry about the caching.

Posted 2-Jan-12 6:33am

Wendelius

Updated 2-Jan-12 6:38am

v2

Comments

Sachin gulati 2-Jan-12 12:38pm

hey thanx it worked... you are genius.. :)

Wendelius 2-Jan-12 12:39pm

Thanks :) I also updated the answer about the performance considerations.

Sachin gulati 2-Jan-12 13:43pm

thanx for the extra information......
hey pls tell me do triggers also have some order in which dey implement...
or can we make them implement in some defined order when they all are called at a same time??