Description
Q1. (25 points) statement level Trigger.
There will be typically four steps for completing a trigger related question. The first step, before working on the trigger, we will create a log table, here for Q1, it is called Empl_Log as below:
CREATE TABLE Empl_log(
Updated_Date DATE default SYSDATE,
Updated_By Varchar2 (15) default USER,
Action Varchar2 (30)
);
Normally, the default value of Updated_Date will be SYSDATE, Updated_by will be the current login name “USER”. You are free to define the string of action,
The second step requires you to create a statement level trigger named Empl_delete on table Employees (either before or after, up to you). Whenever a command of delete on the table Employees is executed, the trigger will be fired, and add one row into the table Empl_log.
After having successfully compiled the trigger, your program will run a DELETE commands to test the trigger as the third step.
DELETE employees where employee_id = 105;
In the forth step, your submission should include the output of Empl_log table, that displays the new record in the log table. It will be a good practice, remember to rollback after you get the display of the results:
— rollback;
** you may notice that the “rollback” will cancel the deletion command on table employees, also delete the correspondent record in the Empl_log table.
Q2. (25 points) Row level Trigger.
This question Q2 is similar to question #1 in this hw7, but it will be “for each row”.
It requires to create a row level trigger named Empl_Del_Row on table Employees.
You will create a table called Empl_Del_log to store the info from this trigger. Whenever there is a command of delete on the table employees, the trigger will be fired, and add one record into the table Empl_Del_log for each record deleted.
Below are the code for creating a new log table called Empl_Del_log
— DROP table Empl_Del_log;
CREATE TABLE Empl_Del_log (
Old_Empno number (6),
OLD_fname Varchar2 (20),
OLD_lname varchar2 (25),
OLD_sal number (8, 2),
OLD_mgrno number (6),
Updated_Date DATE,
Updated_By Varchar2 (15),
Action Varchar2 (30)
);
Similar as Q1, the second step requires you to create a row level trigger named Empl_Del_row on table Employees. Whenever there is a command of delete on the table employees, the trigger will be fired, and it will add one record into the table Empl_Del_log for each record deleted.
After having successfully compiled the trigger, your program will run a DELETE commands.
delete Employees where manager_id = 103;
In the forth step, your submission should include the output of Empl_Del_log table, that displays the new record in the log table. It will be a good practice, remember to rollback after you get the display of the results:
— rollback;
Q3. (25 points) Conditional Predicates Row level Trigger. This question is based on the departments table.
First step, please build a log table Dept_log as below:
CREATE TABLE Dept_log(
OLD_Deptno number (4),
NEW_Deptno number (4),
OLD_Deptname Varchar2 (30),
NEW_Deptname Varchar2 (30),
OLD_MgrID number (6),
NEW_MgrID number (6),
OLD_LocID number (4),
NEW_LocID number (4),
Updated_Date DATE,
Updated_By Varchar2 (15),
Action Varchar2 (25)
);
To monitor the table Departments, we will create a conditional predicates row-level trigger called Dept_Change. Whenever there is a command of insert, or delete, or update/change on column of manager_id or location ID, the trigger will be fired, and one row will be inserted into the table Dept_log.
Once you have successfully built the trigger, you are required to test your trigger using the following commands, display the correspondent output.
Column OLD_Deptname format A10
Column NEW_Deptname format A10
SELECT * from Dept_log ;
INSERT INTO departments VALUES (290, ‘Test’, null, 1700);
UPDATE departments set manager_id = 103 WHERE department_id = 290;
UPDATE departments set location_id = 1800 WHERE department_id = 290;
DELETE departments where department_id = 290;
SELECT * from Dept_log ;
rollback;
Q4. Package (25 point).
Please be patient when you read this question. This question requires you to edit, reorganize the codes from a PL/SQL block environment into package module, then invoke the functions defined in package.
Following codes are answers for Q3. hw 6. In a PL/SQL block, we have defined two (nested, or say local) functions with the same name as Dept_Head_FName with different parameter names and data types. Both functions have one IN parameter. The first function use deptno (department_id) as its IN parameter, its data type is number. The second function has dept_name (department_name) as its IN parameter, the data type is varchar2. Both functions will return the full name of the head/manager of that department.
Declare
Dpt_id employees.department_id%TYPE := 60;
dept_name varchar2 (30) := ‘it’ ;
Dpt_head_fname varchar2 (47);
Function Dept_Head_FName
( Deptno IN number)
RETURN varchar2
IS
Dept_Head_fullname varchar2 (47);
BEGIN
SELECT first_name ||’ ‘|| last_name INTO Dept_Head_fullname
FROM employees WHERE employee_id =
(select manager_id from departments where department_id = Deptno);
RETURN Dept_Head_fullname;
END Dept_Head_FName ;
Function Dept_Head_FName
( Dept_name IN varchar2)
RETURN varchar2
IS
Dept_Head_fullname varchar2 (47);
BEGIN
SELECT first_name ||’ ‘|| last_name INTO Dept_Head_fullname
FROM employees WHERE employee_id =
(select manager_id from departments
where upper (department_name) = upper (Dept_name));
RETURN Dept_Head_fullname;
END Dept_Head_FName ;
BEGIN
Dpt_head_fname := Dept_Head_FName ( Dpt_id);
DBMS_OUTPUT.PUT_LINE( ‘For the department with ID ‘|| dpt_id ||’:’||
CHR (10)|| ‘its dept head name is: ‘|| Dpt_head_fname || ‘.’);
Dpt_head_fname := Dept_Head_FName ( dept_name );
DBMS_OUTPUT.PUT_LINE( ‘For the department with name ‘|| Dept_name ||’:’||
CHR (10)|| ‘its dept head name is: ‘|| Dpt_head_fname || ‘.’);
END;
/
The above functions will exist only in the life of that block. To store then share these functions for the future use, a good practice will change the codes into package module.
In your program, define a package named Pkg_Dept_Head. In the specification section, define two functions with the same name as Dept_Head_Fname, similar as in the provided codes.
In the package body, you will provide the function programs to return the right value for these functions. In the executive section of the body, you do not need to write any code for initializing. It can be simply as
BEGIN
null;
END;
or skipped the “begin null; ”, only with “end; ”.
After having successfully compiled the package specification and its body, write a PL/SQL block. It will call the function twice. One uses 60 as department_id. The second time, uses ‘IT’ as actual parameter value. Your program will print out the results.
