Notes

--Ramesh Kunwar
--18413696
--Term II
--Week 17
--***********************************************************************************************
--displaying existing triggers
SELECT * FROM user_triggers;
--no rows selected.

DESC students;

--creating the trigger named trig_dob_ck

CREATE OR REPLACE TRIGGER trig_dob_ck
BEFORE INSERT OR UPDATE OF date_of_birth ON students
FOR EACH ROW
WHEN(NEW.date_of_birth>SYSDATE)

BEGIN
RAISE_APPLICATION_ERROR(-20000, 'ERROR - THIS STUDENT WAS BORN AFTER TODAY, AMEND STUDENT DATE OF BIRTH!');

END trig_dob_ck;
/
SHOW ERRORS;
--------------------------------------------------------------------------------------------------
--**TEST**

--checking the number of rows present in students table;
SELECT * FROM students;
--21 rows selected.

--inserting the correct value in students table.

INSERT INTO students
(student_id, student_firstname, student_surname, phone_number, fax_number, gender
,date_of_birth, preferred_language, company_id)
VALUES (5001, 'RAMESH', 'KUNWAR', '9848000051', 099692365, 'M', '31-DEC-1999', 'NE', 30);

--1 row created.
--22 rows are select after creating the new row.

--Now trying to input incorrect date in students table.

INSERT INTO students
(student_id, student_firstname, student_surname, phone_number, fax_number, gender
,date_of_birth, preferred_language, company_id)
VALUES (5002, 'PEMBA', 'CERPA', '9848099051', 099697865, 'F', '31-DEC-2999', 'NE', 30);
--The data was not inserted in the table and the trigger works properly.
--ORA-20000: ERROR - THIS STUDENT WAS BORN AFTER TODAY, AMEND STUDENT DATE OF BIRTH!

--22 rows remained selected nd new row was not added.

--------------------------------------------------------------------------------------------------

--creating new trigger for students table so that it only fires if the date_of_birth column IS NOT NULL.

CREATE OR REPLACE TRIGGER trig_check_null
AFTER INSERT OR UPDATE OF date_of_birth ON students
FOR EACH ROW
WHEN(NEW.date_of_birth IS NOT NULL )

DECLARE
vd_today DATE;

BEGIN
SELECT SYSDATE
INTO vd_today
FROM DUAL;

IF (vd_today-:NEW.date_of_birth)<(365*18)
THEN DBMS_OUTPUT.PUT_LINE ('The student is a child ');
ELSE
DBMS_OUTPUT.PUT_LINE ('The student is an adult ');
END IF;

END trig_check_null;
/
SHOW ERRORS;


---------------------------------------------------------------------------------------
--TEST
SELECT * FROM students;

--22 rows selected.
--Inserting the data into table without giving date of birth
INSERT INTO students
(student_id, student_firstname, student_surname, phone_number, fax_number, gender
, preferred_language, company_id)
VALUES (5003, 'RAM', 'CERPA', '9841119051', 099697865, 'F', 'NE', 30);

--23 rows selected.

--now inserting with the date
SET SERVEROUTPUT ON;

INSERT INTO students
(student_id, student_firstname, student_surname, phone_number, fax_number, gender
,date_of_birth, preferred_language, company_id)
VALUES (5012, 'ss', 'Bss', '9848099222', 099697444, 'M', '31-DEC-1999', 'NE', 30);

--the student is adult

INSERT INTO students
(student_id, student_firstname, student_surname, phone_number, fax_number, gender
,date_of_birth, preferred_language, company_id)
VALUES (5013, 'sRR', 'Bssaa', '9848099222', 099697444, 'M', '31-DEC-2002', 'NE', 30);
--THE student is child