Notes

--Bigyan
--Un.Id:18413698
--week 17 Trigers

--DROPING TRIGGER
DROP TRIGGER 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 script
SELECT * FROM students;
-- 21 row selected..

INSERT INTO students
(student_id, student_firstname, student_surname, phone_number, fax_number, gender
,date_of_birth, preferred_language, company_id)
VALUES (1112, 'AMRIT', 'BROWN', '(617)342-2345', 6173422345, 'F', '01-FEB-1964', 'EN', 20);

SELECT * FROM students;
-- 22 row selected..


--1 row created.
INSERT INTO students
(student_id, student_firstname, student_surname, phone_number, fax_number, gender
,date_of_birth, preferred_language, company_id)
VALUES (1113, 'ANUJ', 'BROWN', '(617)342-2345', 6173422345, 'M', '01-FEB-2025', 'EN', 20);
--WHEN USER FORGET TO SUPPLY DOB AND UPDATE DOB TRIGGER AUTOMATICALLY CALLED -20001 ERROR WHICH IS DEFINED IN trig_dob_ck


SELECT * FROM students;
-- 22 row selected..



--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 (5005, 'AMRIT', 'THAPA', '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 (1114, 'ANUJ', 'BROWN', '(617)342-2345', 6173422345, 'M', '01-FEB-1995', 'EN', 20);
--The student is an adult

--1 row created.



INSERT INTO students
(student_id, student_firstname, student_surname, phone_number, fax_number, gender
,date_of_birth, preferred_language, company_id)
VALUES (1115, 'AMRIT', 'BROWN', '(617)342-2345', 6173422345, 'M', '01-FEB-2005', 'EN', 20);
--The student is a child

--1 row created.