Wiring up Application Logic - Oracle Application Express 4 Recipes - page 71

Solution
Before you can test the solution in this recipe, you will need to set up some sample database and form
objects. After that, you will explore how you can add server-side validation to these forms. Let's start by
creating the sample objects.

Setting up the Sample Objects
To create the sample objects, please follow these steps:

1. Create the PaymentHistory table shown in Listing 3-1 using the SQL Workshop

or PL/SQL.

Listing 3-1. The PaymentHistory Table

CREATE table "PAYMENTHISTORY" (
"SOCIALSECURITYNO" NVARCHAR2(9),
"AMOUNTPAID" NUMBER(6,2)
)

2. Create the patient discharge table shown in Listing 3-2 (with the

DISCHARGEID field set as the primary key), then create a database application
and set up a data entry form on top of this table.

Listing 3-2. The PatientDischarge Table

CREATE table "PATIENTDISCHARGE" (
"DISCHARGEID" NVARCHAR2(10),
"PATIENTNAME" NVARCHAR2(255),
"PATIENTSOCIALSECURITYNO" NVARCHAR2(9),
"DATEOFDISCHARGE" DATE,
"DISCHARGEREMARKS" NVARCHAR2(2000),
constraint "PATIENTDISCHARGE_PK" primary key ("DISCHARGEID")
)
/

CREATE sequence "PATIENTDISCHARGE_SEQ"
/

CREATE trigger "BI_PATIENTDISCHARGE"
before insert on "PATIENTDISCHARGE"
for each row
begin
if :NEW."DISCHARGEID" is null then
select "PATIENTDISCHARGE_SEQ".nextval into :NEW."DISCHARGEID" from dual;
end if;
end;
/