Notes

/*
Name: Rahul

week 17

*/

--@E:

SET SERVEROUTPUT ON

--creating the trigger
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 ERROR --SHOWING THE ERROR IN THE CODE


--Q: What's the result?
--A: Trigger created

--Q: Do you get any errors, if so what are they and how do you solve them?
--A: There were errors as I have missed to use the semi-colon at last before the END statement


--Q: What is the command used to fire your trigger?
--A:


--testing of the created trigger
--testing the inserting data with the invalid data
INSERT INTO students(students_id, students_fullname, student_email, location, date_of_birth)
VALUES(101, 'Chirayu Subedi', '[email protected]', 'Imadol', '22-03-2019')

--testing the inserting data with valid inpiut
INSERT INTO students(students_id, students_fullname, student_email, location)
VALUES(101, 'Chirayu Subedi', '[email protected]', 'Imadol', '22-05-1995')


--UPDATING USING THE INVALID DATA
UPDATE students SET
date_of_birth= '22-05-2019'
WHERE students_id= 101;



--UPDATING USING THE VALID DATA
UPDATE students SET
date_of_birth='15-05-1998'
WHERE students_id= 101;

--Q: What is the result? Is the data inserted into the table? Why?
--A:



CREATE OR REPLACE TRIGGER trig_dob_pk
AFTER INSERT OR UPDATE date_of_birth ON students
FOR EACH ROW
WHEN(NEW.date_of_birth>(today-(365*18)))

BEGIN
IF