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.
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.
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.
No comments:
Post a Comment