| Pre-query vs post-quey [message #478999] |
Thu, 14 October 2010 02:23  |
swapnil_naik
Messages: 269
Registered: December 2009
Location: Mumbai
|
Senior Member |
|
|
Hi friends,
Can somebody expalin me in simple words what is purpose of pre-query and post-query...i already read oracle document , but i cant get exact meaning...please expalin me in simple words.....in which certain condition i used this trigger??
|
|
|
|
| Re: Pre-query vs post-quey [message #479001 is a reply to message #478999] |
Thu, 14 October 2010 02:33  |
 |
Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Here's an example (Scott's schema): you have a form with two blocks. The first block is a non-database block and contains several items, such as JOB and DEPTNO. Once again - NON database items.
The second block (something like first block's detail block) contains all columns from the EMP table. This is a database block.
The idea is: you'll enter some values into JOB and DEPTNO items in the first block. You'll press <ENTER> on the DEPTNO item and you'll fetch all employees (who work in that department and are, for example, CLERKs) in the second block.
In order to do that, you'd create two triggers:-- KEY-NEXT-ITEM on DEPTNO item:
go_block('second_block');
execute_query;
-- PRE-QUERY trigger on the second block:
:second_block.job := :first_block.job;
:second_block.deptno := :first_block.deptno;
PRE-QUERY trigger will set those values BEFORE query is executed, so that you'd fetch only records you are interested in.
Now, in the second block, there's a DEPTNO item. But you'd like to see department NAME (instead of number). OK, so create a display (non-database) item in the second block. Create a POST-QUERY trigger which will, obviously, fire after query is executed:-- POST-QUERY trigger on the second block
select d.dname
into :second_block.department_name
from deptno d
where d.deptno = :second_block.deptno;
That's it.
|
|
|
|
|
|
|
|
| Re: Pre-query vs post-quey [message #479008 is a reply to message #479007] |
Thu, 14 October 2010 04:02 |
|
Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
OK, another example: if we have a form that is to be used when entering new employees, you'd have DEPTNO item (database item) and DEPARTMENT_NAME (non-database item). You'd put "10" into the DEPTNO item and expect "ACCOUNTING" to be displayed in the DEPARTMENT_NAME item. In order to do that, you'd write WHEN-VALIDATE-ITEM trigger on DEPTNO item:begin
select d.dname
into :block.department_name
from dept d
where d.deptno = :block.deptno;
exception
when no_data_found then
message('Invalid department number');
raise form_trigger_failure;
end;
For querying purposes, you'd have to create a POST-QUERY trigger. It would look like the one in my first example.
It means that - for that type of a form - you'd need both WHEN-VALIDATE-ITEM and POST-QUERY triggers.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
