|
FORM BLOCK and TRANSACTIONAL TRIGGERS
Introduction
Your
organization wants to use the �managers_pkg�
package that you created to insert, delete,
update, lock, and count a record.
You have been
assigned to develop an application form to
manipulate the managers table using
transactional triggers.
Database
Objects
Open the FORMS
builder. In the FORMS builder window, click
on the "+" sign next to "Database objects"
to see its entire object. If the database is
not open, you will be prompted a login
window to log in to the database.
After
successful login, you will see a �+� sign
next to the Database Objects. Click on the
�+� sign to expand it. All the schemas in
the database will be listed.
Check your
created package
Expand the ORACLE schema. Then, expand the
"Stored Program Units" item. You should at
least see the last package (MANAGERS_PKG)
that you created. There must be two items.
One for package specification and the other
for package body. There should not be any
�*� next to them. An �*� indicates that your
package was not compile successfully.
Open the "Managers_PKG" package body. Browse
through the package body that had been
created during the previous hands-on
exercise. We are going to use the delete,
update, and insert procedures to manipulate
the "FORM BLOCK" object. Then close
"WINDOWS."
Browse through the package specification and
then close the window.
Save a Module
In
the Object Navigator, change the "MODULEnn"
name to "MANAGERS_INFORMATION."
Create a Data Block
In the �Object
Navigator� window, highlight "Data Blocks,�
and click on the "create� icon.
Data Block
Wizard
In the �New
Data Block� window, choose the default
option �Data Block Wizard� and click "OK."
Welcome Data
Block
In the
�Welcome Data Block Wizard� window click
�NEXT.�
Type of Data
Block
Select the
type of data block you would like to create
by clicking on a radio button. Select the
default option �Table or View� and then
click �NEXT� again.
Select Table
Click on
�browse.� In the �Tables� window, highlight
the "MANAGERS� table; then click "OK."
Select columns
for the Data Block Wizard
To choose all
columns, click on the two arrow signs in the
�Data Block Wizard� window. To choose
selected columns, click on the one arrow
sign. For this hands-on exercise select all
columns, and click �next.�
Layout Wizard
End of the
Data Block Wizard and beginning of the
Layout Wizard
In the
�Congratulations� screen, select the "Just
Create the data block" option and click
"FINISH." You can also use the Data Block
Wizard to modify your existing data block.
Simply select the data block in the Object
Navigator and click the Data Block Wizard
toolbar button, or choose �Data Block
wizard� from the �Tools� menu.
Open and
change a property palette sheet
In the Object
Navigator, right click on the �MANAGERS"
data block item to open its Property
Palette.
In the its
property palette, change the "Query Data
Source Type" item to "Procedure," change the
"Source Name" item to "MANAGERS_PKG.MANAGERS_REFCUR,�
change the "Source Arguments" item, in the
�Query Data Source Arguments� window, type "MANAGERS_DATA�
in the �Argument Name� box, change the Type
item to "REFCURSOR,� type "MANAGERS_PKG.C_MANAGERS�
in the Type Name box, set the "Mode" to "IN
OUT,� and click �OK� to close the window.
Back to property palette (Data Block:
MANAGERS), change the "DML Data Target Type"
item to "Transactional Triggers." Then close
the window.
Create a
trigger
In
the Object Navigator, highlight "Triggers"
for the MANAGERS data block and click on the
"Create" icon.
ON-INSERT
trigger
In
the Trigger window, type the letter "O,"
then "I," to select the ON-INSERT trigger,
and click "OK."
PL/SQL Editor
In
the PL/SQL window, write a stored procedure
to insert a record into the "MANAGERS"
table.
(PL/SQL
Editor)
DECLARE
r
managers_pkg.managers_rec;
BEGIN
r.empno := :managers.empno;
r.ename := :managers.ename;
managers_pkg.managers_insert ( r );
END;
Notice that the "MANAGERS_INSERT" procedure
was already created during the previous
Hands-On.
Compile
ON-INSERT
Compile the trigger.
Create ON-LOCK
trigger
In
the PL/SQL Editor, click on "New" and in the
Trigger window, type the letter "O" and "L�
to get the ON-LOCK trigger. Then click on
"OK."
PL/SQL for
ON-LOCK trigger
In
the PL/SQL Editor, write a stored procedure
to lock a record. Use "MANAGERS_LOCK" that
was created from the previous Hands-On.
(PL/SQL
Editor)
managers_pkg.managers_lock
(:managers.empno);
Compile ON-LOCK trigger
Compile the
trigger.
Create
ON-UPDATE trigger
In
the PL/SQL Editor, click on "New" and in the
Trigger window, type the letter "O" and "U"
to get the ON-UPDATE trigger. Then click
"OK."
In the PL/SQL Editor, write a stored
procedure to update a record. Use the
"UPDATE" procedure from your created
package.
(PL/SQL
Editor)
DECLARE
r
managers_pkg.managers_rec;
BEGIN
r.empno :=
:managers.empno;
r.ename :=
:managers.ename;
managers_pkg.managers_update ( r );
END;
Compile then
trigger.
Create
ON-DELETE trigger
In the PL/SQL
Editor, click on "New" and in the Trigger
window, type the letter "O" and "D," select
the ON-DELETE trigger. Then click "OK."
In the PL/SQL Editor, write a stored
procedure to update a record. Use the
"Delete" procedure from your created
package.
(PL/SQL
Editor)
DECLARE
r
managers_pkg.managers_rec;
BEGIN
r.empno :=
:managers.empno;
r.ename :=
:managers.ename;
managers_pkg.managers_delete ( r );
END;
Compile it.
Create
ON-COUNT trigger
In the PL/SQL
Editor, click on "New" and in the Trigger
window, type the letter "O" and "C," select
the ON-COUNT trigger. Then click "OK."
In the PL/SQL Editor, write a stored
procedure to update a record. Use the "COUNT_QUERY_"
procedure from your created package.
(PL/SQL
Editor)
DECLARE
r NUMBER;
BEGIN
r :=
managers_pkg.count_query_;
SET_BLOCK_PROPERTY (�managers�, query_hits,
r);
END;
Compile the
trigger and close the window.
Open
Layout Wizard
Go to the Main
menu, choose "Tools" sub-menu and select the
"Layout Wizard" option.
In the Welcome window, click on "next" to
continue.
Select canvas
In the �Layout
Wizard� window, select the "new canvas"
option. Select "content," then click �Next.�
Select Columns
for the Layout Wizard
In the �Layout
Wizard� window, select all the columns.
These are the columns that you want to
display them on the canvas. Then click
�Next.�
Change your
objects appearances
Change size or
prompt if needed. In this window, you can
enter a prompt, width, and height for each
item on the canvas. You can change the
measurement units. As a default the default
units for item width and height are points.
When you change size, click �Next.�
Select a layout style
Select a
layout style for your frame by clicking a
radio button. Select "Form," if you want one
record at a time to be displayed. Select
�Tabular,� if you want more than one record
at a time to be displayed. Select "Forms,"
and then click �next.�
Record layout
Type the Frame
Title (Managers Name), Records Displayed,
Distance Between Records values and
checkmark the �Display Scrollbar� box, when
you use multiple records or the �Tabular�
option. Then click �Next.�
Congratulation
Screen
In the
�Congratulations� window, click "Finish."
Now, run the program to test it. Insert
records into the Managers table. Delete
and/or update a record. Then save the
transactions.
Close the application and save the FORM.
Questions:
Q: What is a
transactional trigger?
Q: How do you
use a stored procedure in stead of tables to
create a form?
Q: When do you
use a transactional trigger?
|
