Home » SQL & PL/SQL » SQL & PL/SQL » cursors in trigger (oracle 9i)
cursors in trigger [message #389410] Sun, 01 March 2009 23:11 Go to next message
ramesh55.sse
Messages: 262
Registered: December 2008
Location: Hyderabad
Senior Member
Can we use cursor in triggers,if it's poosible please give me example?
Re: cursors in trigger [message #389411 is a reply to message #389410] Sun, 01 March 2009 23:14 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
A cursor is too dumb to know whether or not it resides in a trigger or not.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Re: cursors in trigger [message #389425 is a reply to message #389411] Mon, 02 March 2009 00:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
A cursor is too dumb to know whether or not it resides in a trigger or not.

Laughing

Regards
Michel
Re: cursors in trigger [message #389456 is a reply to message #389410] Mon, 02 March 2009 03:11 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Hi,

You could have just told that It is possible,

I didnt know about this, so tried ( I believe this forum is here for sharing knowledge) as following,although don't know the exact need for this.

CREATE OR REPLACE TRIGGER trig1
   AFTER INSERT
   ON emp
   FOR EACH ROW
DECLARE
   l_dname   dept.dname%TYPE;

   CURSOR c1
   IS
      SELECT dname
        FROM dept;
BEGIN
   OPEN c1;

   LOOP
      FETCH c1
       INTO l_dname;

      EXIT WHEN c1%NOTFOUND;

      INSERT INTO dept_dname
                  (dname
                  )
           VALUES (l_dname
                  );
   END LOOP;
END;


Re: cursors in trigger [message #389464 is a reply to message #389456] Mon, 02 March 2009 03:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I didnt know about this,

You should say you didn't think about it.
What is the difference between a trigger and a procedure from PL/SQL point of view? Think about it and you will be no more surprised on what you can do.

Regards
Michel
Re: cursors in trigger [message #389471 is a reply to message #389456] Mon, 02 March 2009 04:09 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You *forgot* to close the cursor.
Re: cursors in trigger [message #389472 is a reply to message #389410] Mon, 02 March 2009 04:11 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Yes LittleFoot,Good Observation.

Re: cursors in trigger [message #389474 is a reply to message #389472] Mon, 02 March 2009 04:23 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Of course if you'd used a FOR LOOP you wouldn't have needed to remember to close the cursor.
Re: cursors in trigger [message #389475 is a reply to message #389410] Mon, 02 March 2009 04:25 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

I Think the main topic was on to see whether Cursors can be used in the triggers or not !!


Regards,
Ashoka BL
Bengaluru
Re: cursors in trigger [message #389482 is a reply to message #389474] Mon, 02 March 2009 05:17 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
And if you rewrote it as a single piece of SQL, then the question of whether or not you could use a trigger wouldn't even arise, AND your code would run quicker.
Previous Topic: Record type
Next Topic: Get all days for a month, along with Week, Day when first, last day are passed (merged 5)
Goto Forum:
  


Current Time: Fri Dec 02 16:25:12 CST 2016

Total time taken to generate the page: 0.17441 seconds