Home » SQL & PL/SQL » SQL & PL/SQL » Can we use cursors in triggers
Can we use cursors in triggers [message #191964] Sat, 09 September 2006 06:49 Go to next message
vishalpatil
Messages: 1
Registered: September 2006
Junior Member

Hi myself vishal patil,currently working on core banking,come across on problem

problem:

create or replace trigger auto_inc
before insert on input_details
for each row
declare
id number;

begin
open auto_inc_cur;
fetch auto_inc_cur into auto_inc_val;
id := nvl(auto_inc_val.input_id,0);
if id := 0 then
id :=1;
else
id := id+1;
end if;
close auto_inc_cur ;
:new.input_id := id;
end;
/

error:

7/10 PLS-00103: Encountered the symbol "=" when expecting one of the
following:
. ( * @ % & = - + < / > in mod not rem then an exponent (**)
<> or != or ~= >= <= <> and or like between is null is not ||
is dangling
The symbol "* was inserted before "=" to continue.

Why this is?
Re: Can we use cursors in triggers [message #192003 is a reply to message #191964] Sat, 09 September 2006 14:12 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Quote:

if id := 0 then

:= is used to assign a value to a variable, not for comparison (= is used for that)
Re: Can we use cursors in triggers [message #192015 is a reply to message #192003] Sat, 09 September 2006 14:37 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This, of course, isn't the whole trigger code, right? It opens a cursor that is never declared, fetches into a cursor variable (also undeclared).

Also, perhaps it would be a good idea to make sure that cursor fetches something (using, for example, %FOUND or %NOTFOUND, whichever you like better).
Re: Can we use cursors in triggers [message #192030 is a reply to message #192015] Sat, 09 September 2006 23:55 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Maybe the OP should read up on sequences. Also, read this and this to see why not to do it the way you are trying now. (non-scalable)

[Edit: mocked up urls again]

[Updated on: Sat, 09 September 2006 23:56]

Report message to a moderator

Previous Topic: BLOB
Next Topic: Changing backend of Crystal from SQL Server to Oracle
Goto Forum:
  


Current Time: Tue Dec 06 12:33:52 CST 2016

Total time taken to generate the page: 0.10203 seconds