Implement and comprehensively test a statement trigger that verifies the followi

Question

Implement and comprehensively test a statement trigger that verifies the
following consistency constraint.

“An applicant cannot apply more than three positions in one day”.

(2) Implement and comprehensively test a row trigger that verifies the following
consistency constraint.
“The value of position number must be continuous”.

"Contiguous" in a relationship to the position number means, that the first position must
have a number 1, the second position must have a number 2, and each next position must
have a number greater by one than a number of the previous position.

Hint:
To avoid "mutating table" error, insert a line
PRAGMA AUTONOMOUS_TRANSACTION
At the beginning of DECLARE block.

When ready save your CREATE TRIGGER statement and all SQL statements that comprehensively test a trigger in a script solution1.sql. Comprehensive testing means that the trigger must reject SQL statements that violate the consistency constraint and accept SQL statements that do not violate the consistency constraint. It is a part of your task to find what SQL statements should be tested. Whenever SQL statement violates the consistency constraint a trigger must return ORA-… error message. Use a procedure RAISE_APPLICATION_ERROR to return ORA-… error message. If SQL statement does not violate the consistency constraint then a trigger must return no
messages.

Explanation / Answer

Answer 1)

“An applicant cannot apply more than three positions in one day”.

I make a table like jid, aid, dt i.e jobid,applicantID and the date. So when a user going to apply more than 3 jobs on the same date , he will get error.

CREATE OR REPLACE TRIGGER val1 before insert on appl
for each row

declare

c number;

begin

select count(jid) into c from appl where aid=:new.aid and dt=:new.dt;

if (c>2) then
RAISE_APPLICATION_ERROR(-20999,'More than 3 are not allowed');
end if;
end;

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.