|
"Good teaching is
one-fourth preparation and three-fourths theater." Gail Godwin |
Read
first then play the video:
FRM-VIDEO -(Hands-On
10) How to use Sequence object and Trigger (PRE-INSERT)
Sequence object, Trigger
(PRE-INSERT)
Introduction
SEQUENCE
A sequence is a
database object that generates a series of integer numbers according
to rules at the time you created the object. One of the purpose of
creating a sequence object is to generate primary keys automatically.
In this Hands-On, you will generate customer IDs.
Forms Trigger
A forms trigger is a
block of PL/SQL code that adds functionality to your application.
Triggers are attached to objects in your application. When a trigger
is fired, it executes the code it contains. Each trigger"s name
defines what event will fire it; for instance, a WHEN-BUTTON-PRESSED
trigger executes its code each time you click on the button to which
the trigger is attached. Or, we can say, a forms trigger is a
set of PL/SQL actions that happen each time an event such as
when-checkbox-changed, when-button-pressed, or
when-new-record-instance occurs. You can attach several triggers
to a data query. The most popular of them are the PRE-QUERY and
POST-QUERY.
POST-QUERY and PRE-QUERY
Trigger
The PRE-QUERY
trigger fires before the select statement is finalized. The POST-QUERY
trigger fires before selected records are presented to the user. It
fires after records are retrieved but before they are displayed. So,
you can use it to enhance a query"s records in a number of ways.
Your Post-Query trigger can contain code to calculate or populate
control items.
PRE-INSERT and
WHEN-NEW-FORM-INSTANCE trigger
Some other useful triggers
are: PRE-INSERT and WHEN-NEW-FORM-INSTANCE.
A PRE-INSERT
trigger fires once before each new record is inserted in a commit
process. The "WHEN-NEW-FORM-INSTANCE" trigger will be used
to prepare objects or an action when entering to a new form. It fires
when the form is entered.
Hands-On
It is very cumbersome for
your users to assign a new customer ID to a new customer. It requires
finding what the last customer ID was entered into their system; then
they increment it by 1 and use that number for a new customer ID. This
is not very a professional way of doing business particularly if you
have multiple transactions to enter.
You have been assigned to
solve this problem and automate this process. They ask you to add a
sequence number to customer ID (CUSTID) starting with 200 and
incremented by 1. So, your customer ID would start from 200, 201, 202,
and so on.
See Figure 12.
Your tasks are:
1- Create a sequence number
to generate a unique customer ID.
2- Assign the generated
sequence number to each customer each time a new customer is added to
a table.
3- Run and test all user
functional requirements.
F You will learn how
to: create sequence object, create and use "PRE-INSERT"
trigger, use tab canvas, use "object navigator," use "Data
Blocks," use "Layout Editor," use "Property Palette," use
"Run Form," and "Execute Query."
Figure 12
Open a Module
In the "Object Navigator"
window, highlight Forms. Go to the Main menu and choose "File,"
select "Open" to open an existing form (customer_orders_V10)
from the "iself"
folder.
Save a Module
Click on the "CUSTOMER_ORDERS_V10"
form. The color will change to blue. Now, change the name and then
save the Form name as version 10 (customer_orders_v10). This
way the original form is untouched.
Go to MS-DOS Prompt.
Login to "sqlplus" as
"iself" password "schooling."
CREATE SEQUENCE "
Create a sequence object
that starts from 200 and is incremented by 1.
SQL> CREATE SEQUENCE
seq_custid START WITH 200;
Retrieve Sequence
To see how it works: Use
the "Next Value" Function to increment the sequence number.
Repeat to run the query
SQL> SELECT
seq_custid.nextval FROM dual;
SQL> /
SQL> /
This will give you some
idea how a sequence object works.
Close the MS-DOS window or
minimize it.
Change a property palette
sheets
In the Layout Editor or
Object Navigator, you can right click on the "custid" item
to open its Property Palette.
In its Property Palette
window, change the "Required" property to "NO."
Then close the window.
Create a Trigger
In the Object Navigator,
highlight the Trigger item under the CUSTOMER data block and click on
the green "+" sign to create a trigger.
PRE-INSERT trigger
In the Trigger window, type
"P" then "R," and then select the "PRE-INSERT" trigger.
PL/SQL Editor
In the PL/SQL Editor, write
a select statement to assign a new sequence number to "custid."
(PL/SQL Editor)
SELECT seq_custid.nextval
INTO :customer.custid
FROM dual;
Compile a trigger
Compile the trigger and
then close the window.
Run the Form
Run the application.
Execute Query
Click "Execute query."
Navigate
Navigate through the
customers information.
Insert a record
Click on the
"insert" icon.
Now, the form is on the "insert"
mode.
Type the new customer
information.
Remember that the "Customer
ID" is going to be generated.
Save a transaction
Save the record.
New "Customer ID"
was generated.
Add more customers.
Duplicate the previous
record.
Notice that even though the
Customer ID is copied, the new "Customer ID" will be
assigned to it.
Click on "save."
Notice that "custid"
is changed to the new number (205).
Navigate
Navigate through the
application.
Then, close the application
and save the changes.
|
"Aim for success,
not perfection. Never give up your right to be wrong, because
then you will lose the ability to learn new things and move
forward with your life." Dr. David M. Burns |
Questions:
Q: Describe a Sequence
object in a Form Module.
Q: How do you create a
sequence object in a Form Module?
Q: Describe the PRE-INSERT
trigger.
Q: How do you create and
use a PRE-INSERT trigger in a Form module?
Q: How do you navigator
through tab canvases?
Q: What for do you use an
object"s property palette?
Q: How do you read and test
a sequence object?
Q: It is very cumbersome
for your users to assign a new customer ID to a new customer. It
requires finding what the last customer ID was entered into their
system; then they increment it by 1 and use that number for a new
customer ID. This is not very a professional way of doing business
particularly if you have multiple transactions to enter.
You have been assigned to
solve this problem and automate this process. They ask you to add a
sequence number to customer ID (CUSTID) starting with 200 and
incremented by 1. So, your customer ID would start from 200, 201, 202,
and so on.
See Figure 12.
Your tasks are:
1- Create a sequence number
to generate a unique customer ID.
2- Assign the generated
sequence number to each customer each time a new customer is added to
a table.
3- Run and test all user
functional requirements.
|