Home » Developer & Programmer » Forms » On update trigger
On update trigger [message #241107] Mon, 28 May 2007 06:07 Go to next message
manyal
Messages: 77
Registered: March 2005
Member
Dear Friends.

I want to fire on update trigger for the table but in trigger there is one condition in which i want to perform some action based on the old values of the another table while the transaction.is it possible?

Manish Manyal
Re: On update trigger [message #241110 is a reply to message #241107] Mon, 28 May 2007 06:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You mean on the current value of another table.
old value of another table is meaningless as you don't modify this other table, so there is no new or old value, just current.

Now, you can check another table in your update trigger.

Regards
Michel
Re: On update trigger [message #241223 is a reply to message #241110] Mon, 28 May 2007 22:52 Go to previous messageGo to next message
manyal
Messages: 77
Registered: March 2005
Member
Dear Michel,

Actually i am using form 6i(master detail form) and after commiting the form which leads to the updation of both the table and when on update trigger fires at back end on one table i need the old value of one attribute of another table to perform specific action.


manyal

Re: On update trigger [message #241232 is a reply to message #241223] Mon, 28 May 2007 23:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You post this question in the wrong forum, so you have inaccurate answer.

I move it to the Forms forum.

Regards
Michel
Re: On update trigger [message #241243 is a reply to message #241223] Mon, 28 May 2007 23:54 Go to previous messageGo to next message
mustaf_82
Messages: 20
Registered: May 2007
Location: UAE
Junior Member

If you want to perform the action based on the old value of the table you can do this simply by writing PL/SQL on Update Trigger.
Re: On update trigger [message #241541 is a reply to message #241243] Tue, 29 May 2007 22:53 Go to previous messageGo to next message
manyal
Messages: 77
Registered: March 2005
Member
Dear Mustaf,

you are right by using pl/sql in trigger we can perform some action but how we would know the old value of the attribute of another table when the transaction is about to commit as within a single transaction we can update multiple tables.

(not the old value of the attribute in which on update trigger is applied)

manyal
Re: On update trigger [message #241877 is a reply to message #241107] Wed, 30 May 2007 15:50 Go to previous messageGo to next message
hemavb
Messages: 103
Registered: May 2007
Location: Dubai , UAE
Senior Member
ok tricky one...
see if this suffices...

a. DO NOT USE DB LEVEL TRIGGERS.

b. Assumptions:
1) You are using forms.
2) "mast" is your master table (and master block) from which you need the "old" value from, lets say, column/field M1.
3) "dtl" is the details table on which you want to perform an operation based on the "old" value of M1.

c. What do you do...
1) write POST-QUERY trigger on "mast". There put the following code:
:global.m1_old_data := :mast.m1;

2) write your trigger to the POST-UPDATE of the "dtl" block. There where-ever you want to refer to the "old" value of M1 use the global variable :global.m1_old_data.
Once you are done with it write:
ERASE('global.m1_old_data');
This will clear the memory of the global variable.
You donot need to initialize the global variable and it is by defa of type VARCHAR2. So use TO_NUMBER if you want to use it in numeric functions or operations.

This should solve your doubt.
Enjoy.
Re: On update trigger [message #241899 is a reply to message #241877] Wed, 30 May 2007 23:00 Go to previous messageGo to next message
manyal
Messages: 77
Registered: March 2005
Member
Dear hemavb,

you are right, by implementing triggers on front end we can achieve the target but i was trying to implement this thru db level so that if i run the same transaction thru any other tool the trigger must fire and consistancy remains intact.

if not possible , i will have to resort on front end level triggers.

Manyal
Re: On update trigger [message #241959 is a reply to message #241107] Thu, 31 May 2007 03:49 Go to previous messageGo to next message
hemavb
Messages: 103
Registered: May 2007
Location: Dubai , UAE
Senior Member
there is a way to work it around.. simple and may sound stupid.
but this one will do the task from the db it self.. no need for the form.

This is what u do:
1) In the "mast" table include one more field for the M1 column like old_m1.

2) write a before update trigger on "mast" table. assign the value of m1 to old_m1:
:new.old_m1 := :old.m1;

3) now when you write the after update trigger to "dtl" refer to the old_m1 column instead of m1.

Hope this will help

ENJOY Cool
Re: On update trigger [message #241977 is a reply to message #241959] Thu, 31 May 2007 05:00 Go to previous message
manyal
Messages: 77
Registered: March 2005
Member
Dear Hemavb,

Yeh! you r right this trick would work.

Thanx a lot.

Manyal
Previous Topic: how get running total in grid
Next Topic: Commit button
Goto Forum:
  


Current Time: Mon Dec 05 02:38:33 CST 2016

Total time taken to generate the page: 0.05137 seconds