| Issue with Triggers and Variables [message #38457] |
Thu, 18 April 2002 13:05  |
philip
Messages: 6
Registered: February 2001
|
Junior Member |
|
|
I'm using a trigger to populate a table appon entry of a row into another table. Pretty basic, eh?
The kicker here is that I need entry(ies) from a third table for full population.
I'm 100% new at Triggers, matter of fact, this is my first one I've ever written. Here's the code:
================================
CREATE OR REPLACE TRIGGER CustMap
AFTER INSERT OR UPDATE ON ar_debtor
FOR EACH ROW
DECLARE
v_acc_num cv_consumer.oldacnum%TYPE;
CURSOR c_Cv_consumer IS
SELECT *
FROM cv_consumer
WHERE www_address = v_acc_num;
BEGIN
-- :new.oldacnum => ar_debtor.oldacnum
-- :new.debtornum => ar_debtor.debtornu
-- v_oldacc.oldacnum => cv_consumer.oldacnum
v_acc_num := :new.oldacnum;
FOR v_consumer_row IN c_Cv_consumer LOOP
INSERT INTO CV_ACC_NUMBERS ( old_acc_id, old_cust_id, debtornum )
VALUES ( v_consumer_row.oldacnum, :new.oldacnum, :new.debtornum );
END LOOP;
--INSERT INTO CV_ACC_NUMBERS ( old_acc_id, old_cust_id, debtornum )
--VALUES ( v_acc_num, :new.oldacnum, :new.debtornum );
END CustMap;
===========================
Layout:
Creating a new Customer number. Must use the old customer number to locate one or more account numbers from another table.
Then, put new and old customer number in a table with a row for each found old account number.
Here's my problem:
As you see, cause of the requirement for the third table entry(ies) I need to turn through a for loop for each entry found.
For some reason, it never seems to work. I've tried using an incline select ( ie: did the select in the FOR statement ) and all that I can think of.
v_acc_num is getting populated, thus the one commented out INSERT, it ran.
I've hard coded a value into the v_acc_num spot, and it worked :
FOR v_consumer_row IN ( SELECT *
FROM cv_consumer
WHERE www_address = '9478274832' ) LOOP
( having v_acc_num in place of '9478274832' didn't work either )
From what I can gather off online sites and my books, this should work, no?
What am I missing here? The need for the loop and the requirement for the variable is needed, there's just no way around that.
Thanks
Philip
philipg@peace.com
|
|
|
|
| Re: Issue with Triggers and Variables [message #38458 is a reply to message #38457] |
Thu, 18 April 2002 13:29  |
 |
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
hi. are you expecting something like this?
if not let me know.
*********************************************
This is the table in which the trigger is created.
********************************************
SQL> desc e
Name
----------------------
EMPNO
ENAME
****************************************
for every insert in table e the rows are populated into
table ed. the rows are derived from the standard tables emp and dept
****************************************
SQL> desc ed
Name
--------------------
EMPNO
DEPTNO
LOC
******************************************
this is the trigger code
******************************************
SQL> get t
line 12 truncated.
1 create or replace trigger Etr
2 after insert on e
3 for each row
4 declare
5 cursor c1 is select a.empno eno ,a.deptno dno ,b.loc loc from emp a, dept b where a.deptno=b.de
6 begin
7 for mag in c1 loop
8 exit when c1%notfound;
9 insert into ed values (mag.eno,mag.dno,mag.loc);
10 end loop;
11* end;
12 /
Trigger created.
SQL>
************************************************
SQL> insert into e values(7900,'ford');
1 row created.
SQL> select * from ed;
EMPNO DEPTNO LOC
---------- ---------- ------------
7900 30 CHICAGO
SQL>
*****************************************************
[Updated on: Mon, 24 April 2006 12:10] Report message to a moderator |
|
|
|
|
|
| Re: Issue with Triggers and Variables [message #38475 is a reply to message #38460] |
Fri, 19 April 2002 11:16 |
philip
Messages: 6
Registered: February 2001
|
Junior Member |
|
|
>>your syntax for declaring the loop is WRONG.
declare the cursor first (say c1).
then do the looping like
for variable in c1 loop <<
That's what I've initially done. According to my PL/SQL book, both of my examples ( note: I gave you mutliple versions ) will work.
And .. They do, when I hard code a value into them. That's not what I need.
I dont like the formatting of this forum. Take a look at: http://www.gpcentre.net/tmp/trigger.txt
for the full SQL I'm using.
I'm using the PreDeclared cursor in there, and it's still not looping.
Do note the 7 lines from the cv_acc_numbers table are inserted are from the TEST insert ( to insure my values *are* there for the variables ).
Dote the rows with the blank value, those are from the LOOP.
As as you see, the last column with the old_ac_id, the example number *is* there. Sooooo ... leads me to my initial question:
Why isn't my LOOP looping?
I gave you full SQL of what I'm doing properly formated and I don't know what's wrong. :(
My Correlation Var version and Bind Var version don't work, but my Hard Coded version does.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
