Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
chessmonster
Starting Member
6 Posts |
 Posted - 2011-06-05 : 10:04:53
|
| hi,I would like to create (or edit) a trigger and inside the trigger is a method to modify or do something with the generated value by the sequenceFOR EACH ROW DECLARE var NUMBER(15);BEGIN IF INSERTING THEN SELECT sequence.NEXTVAL INTO var FROM DUAL :NEW.primary_key_column := aMETHOD(var); END IF;END;this is the proposed block that I can try to use to do the job but I am somehow confused with this one, especially with the 'FROM' linetried to create my ownFOR EACH ROW DECLARE var NUMBER(15);BEGIN IF INSERTING THEN SELECT sequence.NEXTVAL INTO var FROM DUAL; INSERT INTO test_table (primary_key_column) VALUES (aMethod(var)); END IF;END;i tried this, and when I ran my small app it gave an error. ORA-00036: maximum number of recursive SQL levels (50) exceededbelieve it went to an endless loop. now i am a bit stomped.just want to ask any suggestions on how i can try to modify the values produced by the sequence while inside still of the trigger. and have that modified value inserted to the table..maybe somethings i can try to look at to give me some enlightenment on this little problem of mine. thanks.regards,simontoughness and stubborness |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2011-06-05 : 13:22:31
|
This is a MS SQL Server forum.You can get better help in an ORACLE forum.
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
|
|
|
|
|
value returning function inside a trigger