Home » Developer & Programmer » Forms » Global Variable values for Triggers
Global Variable values for Triggers [message #253847] Wed, 25 July 2007 01:56 Go to next message
oralover
Messages: 97
Registered: January 2005
Member
hi all gurus!

using database: Oracle9i Enterprise Edition Release 9.2.0.8.0
Developer: 6i and 10g Release 1

i have a table:

create table order_track (
buyer_id     varchar2(5),
seller_id    varchar2(5),
item_id      varchar2(10),
item_qty     number(8),
item_rate    number(10,2),
initiated_on date,
delivered_on date
)


the table has a trigger which is Before Insert, and its inserting data in another table.

i have made a user interface (FORM) and i am assining an ID to the user which is :GLOBAL.user_id, how i can catch this variable's value in the said Trigger so i can insert in another table.

NOTE: i searched for GLOBAL variables but failed to find something for this scenario.

thanks in advance.
Re: Global Variable values for Triggers [message #253849 is a reply to message #253847] Wed, 25 July 2007 02:05 Go to previous messageGo to next message
azamkhan
Messages: 548
Registered: August 2005
Senior Member
Where you have created this Global variable in database or at form level?
Re: Global Variable values for Triggers [message #253852 is a reply to message #253847] Wed, 25 July 2007 02:08 Go to previous messageGo to next message
oralover
Messages: 97
Registered: January 2005
Member
at form level.
Re: Global Variable values for Triggers [message #253853 is a reply to message #253852] Wed, 25 July 2007 02:12 Go to previous messageGo to next message
azamkhan
Messages: 548
Registered: August 2005
Senior Member
If you have declared it at FORM level then just use its name :Global.Variable_name to refer to its values.
Re: Global Variable values for Triggers [message #253855 is a reply to message #253847] Wed, 25 July 2007 02:15 Go to previous messageGo to next message
oralover
Messages: 97
Registered: January 2005
Member
how it will be passed to the Trigger as trigger normally catches the values of the concerned table values with OLD and NEW or whatever you specify and the values for the variables we defined in the Trigger's declare section.
Re: Global Variable values for Triggers [message #253856 is a reply to message #253855] Wed, 25 July 2007 02:19 Go to previous messageGo to next message
azamkhan
Messages: 548
Registered: August 2005
Senior Member
What I have understood is that you want to refer the value of a Global variable... plz clearify your problem
Re: Global Variable values for Triggers [message #253858 is a reply to message #253847] Wed, 25 July 2007 02:21 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What is USER_ID? Is it Oracle username (for example, 'scott')? If so, perhaps you don't have to do anything - user which is connected to a form is the same as the user which inserts records into the table.

You can get this user in a database trigger by selecting it into a locally declared variable as
SELECT user INTO l_user FROM dual;
Now you'll be able to insert it into a desired table.
Re: Global Variable values for Triggers [message #253859 is a reply to message #253847] Wed, 25 July 2007 02:23 Go to previous messageGo to next message
oralover
Messages: 97
Registered: January 2005
Member
azamkhan wrote:
Quote:
What I have understood is that you want to refer the value of a Global variable... plz clearify your problem


yes, absolutely, but to pass the value we have to pass the Global variable to the Trigger like we are passing to Functions/Procedures the variables with some values....


to Littlefoot:
its my Generated ID in Form...

[Updated on: Wed, 25 July 2007 02:26]

Report message to a moderator

Re: Global Variable values for Triggers [message #253860 is a reply to message #253858] Wed, 25 July 2007 02:28 Go to previous messageGo to next message
azamkhan
Messages: 548
Registered: August 2005
Senior Member
no passing a value to a function or procedure is different. You dont pass a value to a trigger. You use a variable in a trigger to use its value.
Re: Global Variable values for Triggers [message #253861 is a reply to message #253860] Wed, 25 July 2007 02:30 Go to previous messageGo to next message
oralover
Messages: 97
Registered: January 2005
Member
any SESSION variable like thing i can use???
Re: Global Variable values for Triggers [message #253863 is a reply to message #253861] Wed, 25 July 2007 02:32 Go to previous messageGo to next message
azamkhan
Messages: 548
Registered: August 2005
Senior Member
ok just tell me do you want to pick the currently login user id?
Re: Global Variable values for Triggers [message #253865 is a reply to message #253847] Wed, 25 July 2007 02:37 Go to previous messageGo to next message
oralover
Messages: 97
Registered: January 2005
Member
no, as i mentioned earlier its my Generated ID in Form...
Re: Global Variable values for Triggers [message #253867 is a reply to message #253865] Wed, 25 July 2007 02:40 Go to previous messageGo to next message
azamkhan
Messages: 548
Registered: August 2005
Senior Member
ok now tell me the event when you are generating this I.D and when you are assigning this value to the Global variable?
Re: Global Variable values for Triggers [message #253868 is a reply to message #253847] Wed, 25 July 2007 02:48 Go to previous messageGo to next message
oralover
Messages: 97
Registered: January 2005
Member
- when user first entering into main interface and if he is using above said form to make new entry.

- the global variable contains logged in date, time, ip and user_id from which he logged in.

the main problem is the logged in date and time as these values can be changed at the time of data entry.
Re: Global Variable values for Triggers [message #253888 is a reply to message #253868] Wed, 25 July 2007 04:05 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Perhaps there is a way to do it the way you'd like it to (i.e. pass a global variable value down to the database level (db trigger)), but - I'd say that you'll have to insert those values using a FORM trigger (instead of a database trigger).
Re: Global Variable values for Triggers [message #255167 is a reply to message #253888] Mon, 30 July 2007 22:58 Go to previous message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
I don't know whether you want to populate the 'buyer_id' or the 'seller_id' so I will assume the 'buyer_id'.

When the user signs on to the system I assume that you are populating ':GLOBAL.user_id'.

I suggest that you populate 'buyer_id' either in the When-Create-Record or Pre-Insert trigger in the form and then in the pre-insert trigger in the database use the ':new.buyer_id' as part of insert statement to the other table.

David
Previous Topic: how i can sum values in one detailed text item ?
Next Topic: Error while calling Report from Forms 10g
Goto Forum:
  


Current Time: Tue Dec 06 08:42:24 CST 2016

Total time taken to generate the page: 0.21537 seconds