OraFAQ Forum: Forms » Global Variable values for Triggers

Home » Developer & Programmer » Forms » Global Variable values for Triggers

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Global Variable values for Triggers [message #253847]

Wed, 25 July 2007 01:56

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.

Report message to a moderator

Re: Global Variable values for Triggers [message #253849 is a reply to message #253847]

Wed, 25 July 2007 02:05

azamkhan
Messages: 557
Registered: August 2005

Senior Member

Where you have created this Global variable in database or at form level?

Report message to a moderator

Re: Global Variable values for Triggers [message #253852 is a reply to message #253847]

Wed, 25 July 2007 02:08

oralover
Messages: 97
Registered: January 2005

Member

at form level.

Report message to a moderator

Re: Global Variable values for Triggers [message #253853 is a reply to message #253852]

Wed, 25 July 2007 02:12

azamkhan
Messages: 557
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.

Report message to a moderator

Re: Global Variable values for Triggers [message #253855 is a reply to message #253847]

Wed, 25 July 2007 02:15

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.

Report message to a moderator

Re: Global Variable values for Triggers [message #253856 is a reply to message #253855]

Wed, 25 July 2007 02:19

azamkhan
Messages: 557
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

Report message to a moderator

Re: Global Variable values for Triggers [message #253858 is a reply to message #253847]

Wed, 25 July 2007 02:21

Littlefoot
Messages: 21823
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.

Report message to a moderator

Re: Global Variable values for Triggers [message #253859 is a reply to message #253847]

Wed, 25 July 2007 02:23

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

azamkhan
Messages: 557
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.

Report message to a moderator

Re: Global Variable values for Triggers [message #253861 is a reply to message #253860]

Wed, 25 July 2007 02:30

oralover
Messages: 97
Registered: January 2005

Member

any SESSION variable like thing i can use???

Report message to a moderator

Re: Global Variable values for Triggers [message #253863 is a reply to message #253861]

Wed, 25 July 2007 02:32

azamkhan
Messages: 557
Registered: August 2005

Senior Member

ok just tell me do you want to pick the currently login user id?

Report message to a moderator

Re: Global Variable values for Triggers [message #253865 is a reply to message #253847]

Wed, 25 July 2007 02:37

oralover
Messages: 97
Registered: January 2005

Member

no, as i mentioned earlier its my Generated ID in Form...

Report message to a moderator

Re: Global Variable values for Triggers [message #253867 is a reply to message #253865]

Wed, 25 July 2007 02:40

azamkhan
Messages: 557
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?

Report message to a moderator

Re: Global Variable values for Triggers [message #253868 is a reply to message #253847]

Wed, 25 July 2007 02:48

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.

Report message to a moderator

Re: Global Variable values for Triggers [message #253888 is a reply to message #253868]

Wed, 25 July 2007 04:05

Littlefoot
Messages: 21823
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).

Report message to a moderator

Re: Global Variable values for Triggers [message #255167 is a reply to message #253888]

Mon, 30 July 2007 22:58

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS 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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Mar 12 09:47:28 CDT 2025