View Exhibit 1 and examine the structure of the EMP and dept tables.
View Exhibit2 and examine the trigger code that is defined on the dept table to enforce the update and delete restrict referential actions on the primary key of the dept table.
What is the outcome on compilation?
A. It compiles and executes successfully.
B. It gives an error on compilation because it is not a row-level trigger.
C. It gives an error on compilation because the exception section Is used in the trigger.
D. It compiles successfully but gives an error on execution because it is not a row-level trigger.
|
Download Printable PDF. VALID exam to help you PASS. |
 |
Also think that correct answer is B
Reference:
https://docs.oracle.com/cd/B25329_01/doc/appdev.102/b25108/xedev_triggers.htm
“The FOR EACH ROW option determines whether the trigger is a row trigger or a statement trigger. If you specify FOR EACH ROW, then the trigger fires once for each row of the table that is affected by the triggering statement. These triggers are referred to as row-level triggers. See the use of FOR EACH ROW in Example 6-1 and Example 6-2.
The absence of the FOR EACH ROW option indicates that the trigger fires only once for each applicable statement, but not separately for each row affected by the statement. ”
Test code:
create or replace trigger dept_restrict
before delete or update of deptno on dept1
declare
dummy integer;
employees_present exception;
employees_not_present exception;
cursor dummy_cursor (dn number) is
select deptno from emp where deptno=dn;
begin
open dummy_cursor (:old.deptno);
fetch dummy_cursor into dummy;
if dummy_cursor%found then
raise employees_present;
else
raise employees_not_present;
end if;
close dummy_cursor;
exception
when employees_present then
close dummy_cursor;
raise_application_error(-20001,’Employees Present in’
|| ‘ Department ‘|| to_char(:old.deptno));
when employees_not_present then
close dummy_cursor;
end;
/
ERROR at line 1:
ORA-04082: NEW or OLD references not allowed in table level triggers
create or replace trigger dept_restrict
before delete or update of deptno on dept1 FOR EACH ROW
declare
dummy integer;
employees_present exception;
employees_not_present exception;
cursor dummy_cursor (dn number) is
select deptno from emp where deptno=dn;
begin
open dummy_cursor (:old.deptno);
fetch dummy_cursor into dummy;
if dummy_cursor%found then
raise employees_present;
else
raise employees_not_present;
end if;
close dummy_cursor;
exception
when employees_present then
close dummy_cursor;
raise_application_error(-20001,’Employees Present in’
|| ‘ Department ‘|| to_char(:old.deptno));
when employees_not_present then
close dummy_cursor;
end;
/
Trigger created.