Notes

/* TRIGGER PRACTICE */
--@E:roshanDatabaseweek17TrigProcFuct.txt
/*
NAME: Anuj Magar
ID: 18406541
WEEK:17
Activity : 1
DATE: 21 Mar
USERNAME: C##CSY2038ANUJ
*/

--DROPPING EXISTING STAFF TABLE
DROP TABLE staff;

--CREATING TABLE STAFF
CREATE TABLE staff(
staff_id NUMBER(6),
firstname VARCHAR2(15),
surname VARCHAR2(15),
username VARCHAR2(10),
role VARCHAR2(10),
date_of_birth DATE);

--CREATING A FUNCTION
CREATE OR REPLACE FUNCTION func_staff_age (in_staff_dob DATE)
RETURN NUMBER IS
vn_staff_age NUMBER(3);
BEGIN
vn_staff_age := FLOOR(MONTHS_BETWEEN(sysdate, in_staff_dob)/12);
RETURN vn_staff_age;
END func_staff_age;
/
SHOW ERRORS;

--CREATING PROCEDURE TO TEST WHETHER FUNCTION WORKES OR NOT
CREATE OR REPLACE PROCEDURE proc_age(in_staff_id NUMBER) IS
vd_dob DATE;
vn_age NUMBER(3);

BEGIN
SELECT date_of_birth
INTO vd_dob
FROM staff
WHERE staff_id = in_staff_id;
vn_age := func_staff_age(vd_dob);
DBMS_OUTPUT.PUT_LINE('the age is ' || vn_age);
END;
/
SHOW ERRORS

--TESTING WHETHER FUNCTION WORKS OR NOT
EXEC proc_age(1000);

--CREATING TRIGGER TO DISPLAY AGE
CREATE OR REPLACE TRIGGER trig_staff_age
AFTER INSERT ON staff
FOR EACH ROW
DECLARE
vn_age NUMBER(3);
BEGIN
vn_age := func_staff_age(:NEW.date_of_birth);
DBMS_OUTPUT.PUT_LINE('THE AGE IS ' || vn_age);
END trig_staff_age;
/

--TESTING WHETHER TRIGGER WORKES OR NOT
SELECT * FROM STAFF;
--1 ROW RETURNED
INSERT INTO staff VALUES (1001, 'BERTY','BANANA', 'BBANA', 'WORKER BEE','02-MAR-1989');
--1 ROW ADDED DISPLAYING AGE
INSERT INTO staff VALUES (1002, 'CANDY','CANE', NULL,'CONTROL','12-JUN-1980');
--1 ROW ADDED DISPLAYING AGE

--CREATING PROCEDURE TO TEST WHETHER FUNCTION WORKES OR NOT
CREATE OR REPLACE PROCEDURE proc_rem_time(in_staff_id NUMBER) IS
vd_dob DATE;
vn_age NUMBER(3);
vn_rem_time NUMBER(3);

BEGIN
SELECT date_of_birth
INTO vd_dob
FROM staff
WHERE staff_id = in_staff_id;
vn_age := func_staff_age(vd_dob);
vn_rem_time := 67- vn_age;
DBMS_OUTPUT.PUT_LINE('the remining time at company is ' || vn_rem_time || ' years.');
END;
/
SHOW ERRORS;

--CREATING A TRIGGER TO SHOW REMAINING TIME AT COMPANY ON UPDATE
CREATE OR REPLACE TRIGGER trig_staff_rem_time
AFTER UPDATE ON staff
FOR EACH ROW
DECLARE
vn_rem_time NUMBER(3);
vn_age NUMBER(3);
BEGIN
vn_age := func_staff_age(:NEW.date_of_birth);
vn_rem_time := 67- vn_age;
DBMS_OUTPUT.PUT_LINE('the remining time at company is ' || vn_rem_time || ' years.');
END trig_staff_rem_time;
/
--TESTING WHETHER TRIGGER WORKS OR NOT
SELECT * FROM STAFF;
--2 ROWs RETURNED
UPDATE staff SET firstname = 'ROSHAN' WHERE staff_id = 1000;
--REMAINING TIME AT COMPANY DISPLAYED ON UPDATE

-- CREATING TRIGGER TO DISPLAY WHEN STAFF ARE BEING DELETED;
CREATE OR REPLACE TRIGGER trig_staff_deleting
BEFORE DELETE ON staff
FOR EACH ROW
BEGIN

DBMS_OUTPUT.PUT_LINE('staff deleting...');
END trig_staff_deleting;
/
--TESTING WHETHER TRIGGER WORKS OR NOT
SELECT * FROM staff;
--2 rows returned;
DELETE FROM staff WHERE staff_id = 1001;
--DELETING message received before deletion.