Notes

--@D:Sumandatabasewekk17.txt
/*
Name: SUMAN CHAULAGAIN
ID: 18406485
Activity 17

*/

--display info on the screen
SET SERVEROUTPUT ON;

DROP TRIGGER trig_dob_ck;

--The trigger for checking the date of birth of the student
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(-20010,'Date of birth cannot be more than current date');
END trig_dob_ck;
/

SHOW ERRORS;

-- TEST --
INSERT INTO students(student_id,student_firstname,student_surname,date_of_birth)
VALUES (000010,'SUMAN','CHAULAGAIN','21-MAR-1999');
-- 1 row created

INSERT INTO students(student_id,student_firstname,student_surname,date_of_birth)
VALUES (000011,'SUJAN','POUDEL','26-FEB-2017');
-- 1 row created

INSERT INTO students(student_id,student_firstname,student_surname,date_of_birth)
VALUES (000012,'SIDDHANT','SHRESTHA','27-FEB-2017');
-- ERROR at line 1:
--ORA-20010: Date of birth cannot be more than current date
--ORA-06512: at "CSY2038.TRIG_DOB_CK", line 2
--ORA-04088: error during execution of trigger 'CSY2038.TRIG_DOB_CK'



--
CREATE OR REPLACE TRIGGER trig_dob_ck2
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 :NEW.date_of_birth > (vd_today-(365*18))
then
DBMS_OUTPUT.PUT_LINE('You are child.');
else
DBMS_OUTPUT.PUT_LINE('You are adult.');
END IF;
END trig_dob_ck2;
/
SHOW ERRORS;

-- TEST --
INSERT INTO students(student_id,student_firstname,student_surname,date_of_birth)
VALUES (000013,'JOHN','CHADWICK','01-FEB-2000');
--You are child.
--1 row created

INSERT INTO students(student_id,student_firstname,student_surname,date_of_birth)
VALUES (000014,'SANDIP','CHETTRI','25-APR-1990');
--You are adult.
--1 row created.

INSERT INTO students(student_id,student_firstname,student_surname,date_of_birth)
VALUES (000015,'NIKHIL','JOSHI','29-APR-2017');
--You are child.
--1 row created.

INSERT INTO students(student_id,student_firstname,student_surname,date_of_birth)
VALUES (000016,'BINDU','MAHARA','12-NOV-2020');
--You are child.
--1 row created.

INSERT INTO students(student_id,student_firstname,student_surname,date_of_birth)
VALUES (000017,'SANDESH','BAM','');
--1 row created.

DROP TRIGGER trig_dob_ck2;


----- BOUNS ACTIVITY ------
CREATE OR REPLACE TRIGGER trig_dob_ck3
BEFORE 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 :NEW.date_of_birth > (vd_today-(365*18))
then
RAISE_APPLICATION_ERROR(-20010,'You have entered young student.');
else
DBMS_OUTPUT.PUT_LINE('Student Added.');
END IF;
END trig_dob_ck3;
/
SHOW ERRORS;


INSERT INTO students(student_id,student_firstname,student_surname,date_of_birth)
VALUES (000020,'HARI','MAINALI','12-NOV-2000');
--ERROR at line 1:
--ORA-20010: You have entered young student.
--ORA-06512: at "CSY2038.TRIG_DOB_CK3", line 10
--ORA-04088: error during execution of trigger 'CSY2038.TRIG_DOB_CK3'

INSERT INTO students(student_id,student_firstname,student_surname,date_of_birth)
VALUES (000019,'KUNAL','VAIDHYA','02-JAN-1990');
--Student Added.
--1 row created.

DELETE FROM students WHERE student_id = 000020;

DROP TRIGGER trig_dob_ck3;

SELECT student_id,student_firstname,student_surname,date_of_birth
FROM students;