| Value from Proc Needed In Trigger [message #9090] |
Fri, 17 October 2003 09:52  |
Michele
Messages: 77
Registered: December 2000
|
Member |
|
|
Hi All,
I have a question on the best way to handle this situation.
We have a third party application that will be passing a value to a stored procedure for processing.
The stored procedure will insert a record into a table based on a subquery that will use the passed variable.
Ex:
exec stored_proc(pers_id);
create or replace procedure stored_proc(p_pers_id in number)
as
Begin
insert into table A
select * from table B where b.pers_id = p_persid;
End;
Now, I was going to put a trigger on table A specifically to capture the value of a sequence that is used on a column in table A so I can insert into another table. Ex:
Create or replace trigger table_c_ins after insert on
table A as
Begin
insert into Table C(column1,column2)
values
(:new.column_with_seq,?);
End;
The question is how can I insert the value of the passed value from stored proc(p_pers_id) in the trigger.
? would be p_pers_id
How can I capture this or accomplish what I need to do.
I don't think you can pass a value to a trigger..
Thanks
Michele
|
|
|
|
| Re: Value from Proc Needed In Trigger [message #9091 is a reply to message #9090] |
Fri, 17 October 2003 11:25  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Since you are inserting all the columns from tableB into tableA in the procedure, and the set of tableB values is based on the value of the parameter, you just to need to reference the column in tableA that maps to tableB.pers_id.
So, if the column is also called pers_id in tableA, then just:
insert into Table C (column1, column2) values (:new.column_with_seq, :new.pers_id); |
|
|
|
| Re: Value from Proc Needed In Trigger [message #9092 is a reply to message #9091] |
Fri, 17 October 2003 12:38 |
Michele
Messages: 77
Registered: December 2000
|
Member |
|
|
Hi Todd,
Thank you for the quick response.What if the situation was using the same column twice. Let me explain:
The stored proc inserts a a record into table a for a record that already exists. Like a duplicate record but with a different primary key:
Ex:
Pers_id last_name first_name status
1234,Duck,Donald,Public originating record
5678,Duck,Donald,Private new record
so the insert into table a select * from table b where b.pers_id = p_pers_id. In this case p_pers_id will be 1234,which is being passed.
Now, the trigger is on table a and I need the new value of pers_id from the private record and I also need the other primary from which it got created from.
Table C will hold the new pers_id with its relationship pers_id.
insert into table c(column1,column2)
values
(:new.pers_id,originating pers_id is needed here)
Table C
column1 column2
5678,1234
I hope I explained it clearly.
Michele
|
|
|
|
| Re: Value from Proc Needed In Trigger [message #9093 is a reply to message #9092] |
Fri, 17 October 2003 13:40 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
I'm not following where the pers_id is being transformed from 1234 to 5678. Your example always shows:
insert into tablea
select * from table b
where b.pers_id = p_pers_id;
If p_pers_id is passed as 1234, then 1234 is the pers_id that gets inserted into tablea. Or is it not really a 'select *' but instead 'select seq.nextval, b.last_name, first_name'?
If so, prior to the insert, you could set an application context or global variable with the 1234 value and then reference that value in the trigger code. Maybe something like this in the proc:
dbms_application_info.set_client_info(p_pers_id);
And then in the trigger:
insert into table c(column1, column2)
values (:new.pers_id, userenv('client_info')); |
|
|
|
| Re: Value from Proc Needed In Trigger [message #9095 is a reply to message #9093] |
Fri, 17 October 2003 14:45 |
Michele
Messages: 77
Registered: December 2000
|
Member |
|
|
Hi Todd,
Sorry about the insert code. You assumed right, I would do something like select seq.nextval.. for the pers_id..
Thank you for the suggestion. I will try to conduct some testing.
Michele
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
