Home » SQL & PL/SQL » SQL & PL/SQL » cursors in trigger
cursors in trigger [message #209822] Mon, 18 December 2006 00:36 Go to next message
mudalimuthu
Messages: 64
Registered: May 2005
Location: Bangalore
Member

create or replace trigger rpdtrig2
before update on trans2
for each row
DECLARE
cursor cur_a is
select r.docid
from trans1 t1, trans2 t2, rpd r
where trans1.trans1id = trns2.trnas1id and rpd.docid = t1.t1vchno;

a varchar2(30);

BEGIN
if updating then
if :new.mid <> :old.mid then
OPEN cur_a;
FETCH cur_a into a;
DELETE FROM rpd
WHERE CURRENT OF c1;

COMMIT;
end if;
end if;

end;


the above trigger is giving error message.
what would be the problem?

thanks
muthu
Re: cursors in trigger [message #209828 is a reply to message #209822] Mon, 18 December 2006 00:56 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Since you did not provide the error message itself, I can only give you some general advice:
Google for the error message. I am quite sure you will find what you need.
I think you got a lot of errors in that query, starting with a number of typos and ranging all the way to bad design..
Re: cursors in trigger [message #209830 is a reply to message #209822] Mon, 18 December 2006 00:58 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

it would have helped if you had posted the error message also.

create or replace trigger rpdtrig2
before update on trans2
for each row
DECLARE
cursor cur_a is
select r.docid
from trans1 t1, trans2 t2, rpd r
where trans1.trans1id = trns2.trnas1id and rpd.docid = t1.t1vchno;

a varchar2(30);

BEGIN
if updating then--unnecessary as the trigger is a before update trigger.
if :new.mid <> :old.mid then
OPEN cur_a;
FETCH cur_a into a;
DELETE FROM rpd
WHERE CURRENT OF c1;

COMMIT;--you can't you this in a trigger.except if it's a autonomous .
end if;
end if;
end



regards,

[Updated on: Mon, 18 December 2006 00:58]

Report message to a moderator

Re: cursors in trigger [message #209831 is a reply to message #209822] Mon, 18 December 2006 00:58 Go to previous message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
If you tell what error you are getting, it is easy to help.

At my first glance,
Close the cursor and loop around it, if needed.

By
Vamsi
Previous Topic: Spool file query
Next Topic: ORA-22950: cannot ORDER objects without MAP - XMLTYPE
Goto Forum:
  


Current Time: Sat Dec 10 01:14:56 CST 2016

Total time taken to generate the page: 0.05084 seconds