Table fields from other tables

1
Table fields from other tables Also called
Non-Base Table Fields. In the example of
CUSTOMER, the Sales Area is known by number, but
not the description of the Sales Area. This has
to be obtained from another table, SALESAREA. A
trigger needs to be created that will insert the
sales area description when the sales area number
appears.
2
Adding a new field from another table to an
existing data block i.e. AreaName from SALESAREA
to the CUSTOMER data block
Highlight CUSTOMER. Right-click and invoke the
Layout Editor
3
The description needs to be added here.
Click on the Text Item tool. This will create a
new field in the data block
4
A new item, TEXT_ITEM6 has been added. The same
height and y-coordinates as Sales Area can be
obtained by using the property palette.
5
In the Property Palette, change the name to
DESCRIPTION.
6
The name has been changed to DESCRIPTION.
7
In the Property Palette, make the following
changes. Enabled Yes Keyboard Navigable No
prevents the users tabbing into this
field Database Item No i.e. does not map to an
attribute in the CUSTOMER table Query
Only No Primary Key No Insert
Allowed No Query Allowed No Update Allowed No
Prevents any of the actions from happening
8
Notice some of the changes made here for the
DESCRIPTION field
The new data item, DESCRIPTION, has now been
added to the data block. But it is not yet known
where to get the data to fill this field. This is
done by creating a trigger.
9
Creating a trigger for the DESCRIPTION field
There are different types of triggers upon the
execution of an event 1. PRE the trigger
fires before the event. 2. POST the trigger
fires after the event. 3. WHEN the trigger
fires in place of the event execution. 4. KEY
the trigger fires when a certain key is pressed.
In this example the trigger will fire after the
Sales Area field changes in the CUSTOMER data
block. This requires that a POST-CHANGE trigger
is created on the CUSTOMER.CompanySalesArea
field. This is performed by first opening the
Object Navigator
The colon indicates that it comes from a data
block
10
Within the CUSTOMER data block, highlight
COMPANYSALESAREA, and then click on Triggers
11
The following pop-up appears. Scroll down to
POST_CHANGE
12
POST_CHANGE has been selected
Click OK
13
The PL/SQL Editor. The code for the trigger is
now entered.
A POST_CHANGE
The CUSTOMER data block and the COMPANYSALESAREA
field
14
Once the code is entered, compile the PL/SQL code
Enter the following code
This shows that the PL/SQL has not yet been
compiled.
The code is being modified
15
Once compiled, EXIT
The code is not being modified
The code has been compiled
16
There now is a POST_CHANGE trigger for
COMPANYSALESAREA
17
Compile and run the CUSTOMER form
18
The Sales Area description now appears. Try
modifying or deleting the field. It should not be
allowed if the previous steps were performed
correctly
19
A new LOV can now be created when the Customer
Detail frame is displayed. The previous LOV just
displayed different sales areas by number. The
new LOV should also show Area Name.
LOV shown from previous slide show. This LOV
should be deleted to be replaced by the new LOV
Highlight both of these items and then delete
them by Edit, Clear.
20
Highlight LOV under CUSTOMER and pull down the
Edit menu and enter Create
21
Choose the LOV Wizard
22
Enter this SQL statement. Notice that this is a
join because items from two tables are needed.
Click Next
23
Include both CompanySalesArea and AreaName
24
Change the names as shown
Choose Automatically size columns and click Look
up return item
25
Each return value has to be looked up
separately. The return value for Number is
Customer.CompanySalesArea. The return value for
Name is Customer.Description
Click Next
26
Enter the name as shown
27
Retrieve 6 records
28
Both return items are assigned to the LOV
29
(No Transcript)
30
The LOV is created
Compile and run the form
31
The LOV showing the six sales areas
32
(No Transcript)
33
(No Transcript)
34
(No Transcript)