21/23 FORMS - 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.

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?