By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
439,978 Members | 1,389 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 439,978 IT Pros & Developers. It's quick & easy.

Using a cursor in a trigger - Is it the best method?

P: 1
Hi...

Looking for a solution to a problem that I am facing...

Basically I have a table schema defined as follows.

Table X(
br char(2),
Dealno char(8)
SeqNo char(4)
)

This table has multiple row updates being performed on it. And this is called many a time at quite frequent intervals of time. I need to be able to browse throught the contents of the "inserted" table and based on the value taken by the dealno column that can be of the following three cases..

Deal Number values
-----------------------------
1) Value = '1234567'
2) Value = NULL
3) Value = 'FAILED'

If the value is of type 1 or 3 then a certain set of action needs to be taken which is basically to insert the values of br,dealno, seqno into another table say Y.

If the dealno type is of type 2 then the action is to perform another search in another table and then based on the result.. perform an insert into table Y.

I have used a cursor for this but dont feel I get the best performance.. Is there anyone that can help me out in wrinting this in a more efficient way...

Sample:

create trigger abc on X for update as
begin

--decl of variables
declare @br char(2)
declare @dealno char(8)
declare @seq char(4)

-- declare cursor

declare c1 cursor for
select inserted.br,inserted,dealno,inserted,seqNo from inserted for read only
open c1
fetch c1 into variables....
while (@@sqlstatus <>2)
begin
if update(dealno)
begin
if (@dealno) is not null
insert into Y
else
select value from W
if value = "y"
insert into y
end
fetch c1 into variables
end


Help is much appreciated in advance. Thanks a million (BTW this is in SYBASE)

Rgds,
George.
Oct 12 '07 #1
Share this Question
Share on Google+
1 Reply


iburyak
Expert 100+
P: 1,017
Cursor can not be used anywhere especially in triggers.

I would replace your code with following:

Expand|Select|Wrap|Line Numbers
  1. insert into Y 
  2. Select br,dealno, seqno from inserted where not dealno is null
  3.  
  4. insert into Y
  5. Select br,dealno, seqno 
  6. from inserted 
  7. where dealno is null and exists(select value from W where value =  'y')
Good Luck.
Oct 12 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.