Notes

--Sonam Sherpa
--18406508
--Triggers - 2

--create table staff
CREATE TABLE staff(
staff_id NUMBER(6),
firstname VARCHAR2(150),
surname VARCHAR2(150),
username VARCHAR2(100),
role VARCHAR2(100),
date_of_birth DATE);


SET SERVEROUTPUT ON;
--drop triggers

DROP TRIGGER trigger_staff;

--qn - 1
--creating procedures
CREATE OR REPLACE PROCEDURE proc_display(in_id staff.staff_id%TYPE,in_firstname staff.firstname%TYPE, in_surname staff.surname%type, in_username staff.username%type, in_role staff.role%type, in_date_of_birth staff.date_of_birth%type) IS
vn_today DATE;

BEGIN
SELECT SYSDATE
INTO vn_today
FROM DUAL;

INSERT INTO staff
VALUES (in_id,in_firstname,in_surname,in_username,in_role,in_date_of_birth);

DBMS_OUTPUT.PUT_LINE('Hello '||in_firstname||' ' ||in_surname||' you are '||(FLOOR((vn_today-in_date_of_birth)/18))||' years old.');
END proc_display;
/
SHOW ERRORS;

--test sctripts
--before execution:
select * from tab;
--No rows selected.


EXEC proc_display(1001, 'BERTY','BANANA', 'BBANA', 'WORKER BEE','02-MAR-1989');

--after execution
--Hello BERTY BANANA you are 609 years old.
--PL/SQL procedure successfully completed.

select * from staff;
-- 1 row displayed.



--qn - 2
--creating triggers

CREATE OR REPLACE TRIGGER trigger_staff
AFTER UPDATE OF staff_id ON staff
FOR EACH ROW
WHEN (NEW.staff_id IS NOT NULL)
DECLARE
vc_firstname VARCHAR(15);
BEGIN
IF INSERTING THEN
SELECT firstname INTO vc_firstname FROM staff;
DBMS_OUTPUT.PUT_LINE('Staff_firstname : ' || vc_firstname);
ELSE NULL;
END IF;

END trigger_staff;
/
SHOW ERRORS;

-- creating procedures
CREATE OR REPLACE PROCEDURE proc_add_staff(staff_id,firstname) IS