| On update trigger [message #241107] |
Mon, 28 May 2007 06:07  |
manyal
Messages: 84
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 #241223 is a reply to message #241110] |
Mon, 28 May 2007 22:52  |
manyal
Messages: 84
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 #241541 is a reply to message #241243] |
Tue, 29 May 2007 22:53 |
manyal
Messages: 84
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 |
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 |
manyal
Messages: 84
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
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
