Each trigger applies to UPDATES as well as INSERTS A trigger that insures that t
ID: 3546591 • Letter: E
Question
Each trigger applies to UPDATES as well as INSERTS
A trigger that insures that the ASSIGN_HOURS field from table "Assignment" contains a value that is at least .5 hours and less than 10.0 hours
A trigger that insures that the field HIRE_DATE from table EMPLOYEE is not after the current date. Use the getdate() function to determine the current date.
A trigger that insures that the job code in the EMPLOYEE table is a valid job code from the job table. Here is what an example job code looks like ex: "201"
Explanation / Answer
1) CREATE OR REPLACE TRIGGER TRIG_ASSIGN_HOURS
BEFORE
INSERT OR
UPDATE OF ASSIGN_HOURS
ON Assignment
for each row
BEGIN
if :new.ASSIGN_HOURS < 0.5 and ASSIGN_HOURS > 10 then
raise_application_error(-20001,'ASSIGN_HOURS value is less the 0.5 and greater then 10');
end if;
END;
2) A trigger that insures that the field HIRE_DATE from table EMPLOYEE is not after the current date.
Use the getdate() function to determine the current date.
CREATE OR REPLACE TRIGGER TRIG_EMP_HIREDATE
BEFORE
INSERT OR
UPDATE OF HIRE_DATE
ON EMPLOYEE
for each row
BEGIN
if :new.HIRE_DATE > getdate() then
raise_application_error(-20002,'HIRE_DATE is after the current date');
end if;
END;
/
3) A trigger that insures that the job code in the EMPLOYEE table is a valid job code from the job table.
Here is what an example job code looks like ex: "201"
CREATE or REPLACE TRIGGER TRIG_JOB_EMP
BEFORE UPDATE or INSERT of job_code
on EMPLOYEE
FOR EACH ROW
declare
LV_COUNT NUMBER;
BEGIN
select count(*) into LV_COUNT from job where job_code = :new.job_code;
if LV_COUNT = 0 then
raise_application_error(-20003,'Job code is not in job table');
--here job table is parent table for employee table
end if;
END;
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.