Description
Objectives: Learn
- To practice writing triggers to enforce integrity constraints between tables.
- Using PHP and Oracle SQL to build a Web application. PHP generates the HTML forms necessary to get the user input, accesses the database and generates the output.
Submit: A SQL file with the completed triggers from 2 & 4 & 6. Also submit a text file with the answers to the questions in 2-6.
Question 6 should be completed and submitted to the TA before you start Lab 7.
Demo: The webpage in part 2.
Part 1
In this part, you will practice writing a few triggers to enforce business rules among the table data.
Create the following tables for Bank Database.
Create table BANKCUST_6 (custno VARCHAR(5) Primary Key,custname VARCHAR(20),street VARCHAR(30), city VARCHAR(20));
Create table ACCOUNTS_6 (AccountNo VARCHAR(5) Primary Key,accountType VARCHAR(10), amount NUMBER(10,2), custno varchar(5),
CONSTRAINT accounts_fkey FOREIGN Key (custno)REFERENCES BANKCUST_6(custno));
Create table TOTALS_6 (custno VARCHAR(5), totalAmount Number(10,2), CONSTRAINT totals_fkey FOREIGN Key (custno)REFERENCES BANKCUST_6(custno));
Exercise 1 (10 pts)
In this exercise, you will write a trigger display the data that is inserted into Bankcust_6 table. This trigger is not really very useful, but just a warm up exercise to write a trigger and see if it fires correctly, as a preparation to write the triggers in the subsequent exercises.
- At SQL PROMPT type set serveroutput on;
- Create the following trigger (either run from a text file or copy and paste it at SQL prompt)
CREATE or REPLACE TRIGGER display_customer_trig
AFTER INSERT on BankCust_6
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE(‘From Trigger ‘||’Customer NO: ‘||:new.custno||’ Customer Name: ‘||:new.custname);
END;
/
show errors;
- Insert the following values into BANKCUST_6 table.
insert into BANKCUST_6 values(‘c1′,’Smith’,’32 Lincoln st’,’SJ’);
insert into BANKCUST_6 values(‘c2′,’Jones’,’44 Benton st’,’SJ’);
insert into BANKCUST_6 values(‘c3′,’Peters’,’12 palm st’,’SFO’);
insert into BANKCUST_6 values(‘c20′,’Chen’,’20 san felipo’,’LA’);
insert into BANKCUST_6 values(‘c33′,’Williams’,’ 11 cherry Ave’,’SFO’);
Did your trigger work?
Yes, the trigger executed successfully.
Modify the trigger so that it displays the city as well.
Added a statement to print :new.city
Exercise 2 (15 pts)
- We will disable the display_customer_trig using the alter trigger statement. Use the statement (use the trigger name).
Alter trigger trigger_name disable
- We will now write a trigger which fires after inserting a row in the Accounts_6 table. The trigger should enter the custno and the amount into the TOTALS_6 table as follows:
- If the custno is already in Totals_6 table, adds the new amount to the existing one.
- If the custno is not in Totals_6 table, adds a new row for this new customer.
The Totals_6 table should give us the total amount in all the accounts owned by each customer.
- Complete the code for the trigger given below, following the comments
Create Or Replace Trigger Acct_Cust_Trig
AFTER INSERT ON Accounts_6
FOR EACH ROW
BEGIN
/*If the custno is already in the Totals_6 table, the update will succeed */
update totals_6
set totalAmount = totalAmount + :new.amount
where custno = :new.custno;
/*If the custno is not in the Totals_6 table, we insert a row into
Totals_6 table. Complete the missing part in the subquery */
insert into totals_6 (select :new.custno, :new.amount from dual
where not exists (select * from TOTALS_6 where custno= ));
END;
/
Make sure that your trigger compiles without any errors.
- Delete if there is any data in the Accounts_6 and Totals_6 tables.
- Insert the following data into Accounts_6 table.
insert into ACCOUNTS_6 values(‘a1523′,’checking’,2000.00,’c1′);
insert into ACCOUNTS_6 values(‘a2134′,’saving’,5000.00,’c1′);
insert into ACCOUNTS_6 values(‘a4378′,’checking’,1000.00,’c2′);
insert into ACCOUNTS_6 values(‘a5363′,’saving’,8000.00,’c2′);
insert into ACCOUNTS_6 values(‘a7236′,’checking’,500.00,’c33′);
insert into ACCOUNTS_6 values(‘a8577′,’checking’,150.00,’c20′);
Did your trigger work? How did you check?
Yes, the triggered worked successfully. I checked this by showing the Totals_6 table and Accounts_6 table and ensuring that we were showing the correct total amount by comparing the two tables and adding the tuple if it was not previously in the Totals_6 table.
Show the data in Totals_6 table.
CUSTN TOTALAMOUNT
—– ———–
c1 7000
c2 9000
c33 500
c20 150
What is the amount for the customer, ‘c1’?
c1 = $7000
Does the total amount for ‘c1’ agree with the amounts for that customer in the Accounts_6 table?
Yes, it agrees since there are two tuples listed as c1 in Accounts_6 that are $5000 and $2000. Thus, the total of c1 = $7000
Exercise 3 (15 pts)
If your trigger is working correctly and updating the total amount for a customer every time a new Account for that customer is created, let us try the following query from SQL prompt.
update Accounts_6
set amount = 1000
where accountno = ‘a1523’;
If the above query successfully ran, check the Totals_6 table.
CUSTN TOTALAMOUNT
—– ———–
c1 7000
c2 9000
c33 500
c20 150
What is the amount for the customer, ‘c1’?
c1 = $7000
Does the amount in Totals_6 table for ‘c1’ agree with the total of amounts in all the accounts for ‘c1’ in Accounts_6 table?
The amount in Totals_6 for c1 does not agree with the total amount for c1 since we were expecting $6000, not $7000.
Exercise 4 (15 pts)
We will modify our trigger Acct_Cust_Trig to fire after inserting as well as updating data in the Accounts_6 table.
Create Or Replace Trigger Acct_Cust_Trig
AFTER INSERT OR UPDATE ON Accounts_6
FOR EACH ROW
BEGIN
If inserting then
update totals_6
set totalAmount = totalAmount + :new.amount
where custno = :new.custno;
insert into totals_6 (select :new.custno, :new.amount from dual
where not exists ( write your complete query from Question 2);
END IF;
if updating then
/* If we are updating we want to correctly set the totalAmount
to the new amount that may be >= or < old amount
Complete the query */
update totals_6
set totalAmount = totalAmount +
where custno = :new.custno;
end if;
END;
/
Show Errors;
- Complete and Compile your trigger.
- Now delete all rows from Accounts_6 table and Totals_6 table.
- Insert the following data into Accounts_6 table.
insert into ACCOUNTS_6 values(‘a1523′,’checking’,2000.00,’c1′);
insert into ACCOUNTS_6 values(‘a2134′,’saving’,5000.00,’c1′);
insert into ACCOUNTS_6 values(‘a4378′,’checking’,1000.00,’c2′);
insert into ACCOUNTS_6 values(‘a5363′,’saving’,8000.00,’c2′);
insert into ACCOUNTS_6 values(‘a7236′,’checking’,500.00,’c33′);
insert into ACCOUNTS_6 values(‘a8577′,’checking’,150.00,’c20′);
- Show the data in Totals_6 table.
CUSTN TOTALAMOUNT
—– ———–
c1 7000
c2 9000
c33 500
c20 150
What is the amount for the customer, ‘c1’?
c1 = $7000
- Run this query
update Accounts_6
set amount = 1000
where accountno = ‘a1523’;
- If the above query successfully ran, check the Totals_6 table.
CUSTN TOTALAMOUNT
—– ———–
c1 6000
c2 9000
c33 500
c20 150
- What is the amount for the customer, ‘c1’?
c1 = $6000
- Does the amount in Totals_6 table for ‘c1’ agree with the total of amounts in all the accounts for ‘c1’ in Accounts_6 table?
Yes, it does agree since $5000 + $1000 = $6000, and is the result we were looking for in exercise 3.
Exercise 5 (10 pts)
One way to check if a specific column is being updated in a table is to use, if updating (column name).
The following trigger prevents the primary key in the BANKCUST_6 table from being updated.
Create Or Replace Trigger NoUpdatePK_trig
After UPDATE ON BANKCUST_6
For each row
BEGIN
if updating (‘custno’) then
raise_application_error (-20999,’Cannot update a Primary Key’);
End if;
END;
/
show errors;
Now, type the following command from SQL prompt:
UPDATE BANKCUST_6
Set custno=’c99’
Where custno=’c1’;
What is the result?
UPDATE BANKCUST_6
*
ERROR at line 1:
ORA-20999: Cannot update a Primary Key
ORA-06512: at “EPAEK.NOUPDATEPK_TRIG”, line 3
ORA-04088: error during execution of trigger ‘EPAEK.NOUPDATEPK_TRIG’
Is the custno updated?
The custno does not update, as we were intending to do with the trigger.
——————————————————————————————————————————–
Question 6 (20 pts)
Question 6 should be completed and submitted to the TA before you start Lab 7.
In this question, you will create tables, M_Student, M_Course, M_CoursesTaken, M_PrereqCourse and M_CourseRegister. You will populate the tables with the given test data.
You will write a trigger that will check, when a student registers for a course, if the student has taken the prereqs for that course. If so, the student no and course no. are entered in M_CoursesTaken table. If not, the registration will fail.
- Create the following tables:
/* A table to hold student data */
Create table M_Student (stno varchar(3) primary key, name varchar(20));
/* A table to hold course info.*/
Create table M_Course (courseno varchar(5) primary key, units Integer);
/* A table to hold the info of courses taken by a student */
Create table M_CoursesTaken (stno varchar(3),courseno varchar(5));
/* A table to hold registration of a student into a course.*/
Create table M_CourseRegister(stno varchar(3),courseno varchar(5));
/* A table to hold course and its prerequisites.*/
Create table M_PreReqCourse (courseno varchar(5), prereq varchar(5));
- Complete the trigger below using the comments given
CREATE or Replace TRIGGER CheckPrereq_Trig
AFTER insert ON M_CourseRegister
FOR EACH ROW
DECLARE
/* local variables you will need */
l_cnt Integer;
l_no Integer;
BEGIN
/* Check if the course has a prereq.in M_PrereqCourse table
If it does not have a prerequisite, it will
Have a null for prereq. Complete this query
*/
Select Count(prereq) into l_no
from M_PrereqCourse
WHERE CourseNo = :NEW.courseno
And
/* prereq not null. */
/* If there are prereqs */
IF l_no > 0 THEN
Select Count(prereq) into l_cnt
from M_PrereqCourse
WHERE CourseNo=:NEW.courseno
AND
prereq NOT IN (
/* Select course no from
M_CoursesTaken where stNo = :NEW.stNo
Complete this query */
if l_cnt > 0 THEN
/* There are prereqs not taken by the student*/
DBMS_OUTPUT.PUT_LINE (‘No prereq’);
RAISE_APPLICATION_ERROR(-20010,’prereqs not done’);
END IF;
END IF;
/* If prereqs have been taken by the student, registration
Is successful and the new course is entered into M_CoursesTaken table.
*/
insert into M_CoursesTaken values (:NEW.stNo,:NEW.CourseNo);
END CheckPrereq_Trig;
/
Show Errors;
- C) Compile your Trigger.
- Enter the following data into the tables.
/* Student data */
insert into M_Student values (‘s1′,’smith’);
insert into M_Student values (‘s2′,’jones’);
/* Course data */
insert into M_course values (‘c1’,4);
insert into M_Course values (‘c2’,2);
insert into M_course values (‘c3’,4);
insert into M_Course values (‘c4’,2);
/* Prerequisite data */
insert into M_PrereqCourse values(‘c3′,’c1’);
insert into M_PrereqCourse values(‘c4′,’c1’);
insert into M_PrereqCourse values(‘c4′,’c2’);
insert into M_PrereqCourse values(‘c1’,NULL);
insert into M_PrereqCourse values(‘c2’,NULL);
- Now is the time to check if your trigger is firing correctly.
Enter the following data into the tables.
/* This should succeed, since c1 does not have any prereqs */
insert into M_CourseRegister values (‘s1′,’c1’);
/* This should succeed, since c3 has c1 as prereq, which s1 has taken. */
insert into M_courseRegister values(‘s1′,’c3’);
/* Do a Select * from M_CoursesTaken */
/* This should fail and fire the trigger, since c4 has c1 and c2 as prereqs
And s1 has taken only one. */
insert into M_courseRegister values(‘s1′,’c4’);
/* Do a Select * from M_CoursesTaken. This
Should show only two courses(c1 and c3) for s1 so far. */
/* This should succeed, since c2 does not have any prereqs */
insert into M_courseRegister values(‘s1′,’c2’);
/* This should succeed now, since s1 has completed the two prereqs */
insert into M_courseRegister values(‘s1′,’c4’);
/* Do a Select * from M_CoursesTaken */
/* Will this fail ? */
insert into M_courseRegister values(‘s2′,’c4’);
This fails since s2 has not taken c1 and c2, which are the prereqs for c4.
———————————————————————————————————————————-
Part 2 (15 pts)
In this, you will run a PHP program that will create an HTML form to get user’s input, connect to your Oracle database tables, fetches and shows the data as given in the query.
Use the file, showSalary_form.php, place the file in the folder where you are required to put the .php files.
You must edit the code below to put your login and password. At the end of the lab session, please feel free to change your password.
- Run the program by typing the URL of the program in the browser window. Give an employee name that you have stored in your AlphaCoEmp table. Check if the program works.
- Now, change the code in the showSalary_form.php file, to display the name, salary and title. Try not to display this information from the function (getSalaryFromDB) but from the main program that calls this function.