"A friendship
founded on business is better than a business founded on
friendship." - John D. Rockefeller (1874-1960) |
Read
first then play the video:
FRM-VIDEO -What
is a REF CURSOR
FRM-VIDEO -FORM
BLOCK and TRANSACTIONAL TRIGGER S
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 ISELF 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.
"Be nice to
people on your way up because you meet them on your way
down." - Jimmy Durante |
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?
|