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.
B
http://docs.oracle.com/cd/B19306_01/appdev.102/b14251/adfns_triggers.htm
UPDATE and DELETE RESTRICT Trigger for Parent Table
b
b
B
B
WHEN clause cannot be used with table level triggers.
WHEN clause can only be used with row level triggers.
NEW or OLD references are not allowed in table level triggers
ORA-04082: NEW or OLD references not allowed in table level triggers
ORA-04077: WHEN clause cannot be used with table level triggers
B
I thought the answer should be D.
CREATE OR REPLACE TRIGGER dept_restrict
BEFORE DELETE OR UPDATE OF DEPARTMENT_ID ON dept
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_APPLICAITON_ERROR(-20001, ‘Employees Present in’ || ‘ Department ‘ || to_char(:OLD.DEPTNO));
WHEN employees_not_present THEN
CLOSE dummy_cursor;
END;
/
Trigger DEPT_RESTRICT compiled
Errors: check compiler log
PLS-00049: bad bind variable ‘OLD.DEPTNO’
Answer should be B. I forgot Referential integrity on the tables.
ORA-04082: NEW or OLD references not allowed in table level triggers
04082. 00000 – “NEW or OLD references not allowed in table level triggers”
*Cause: The trigger is accessing “new” or “old” values in a table trigger.
*Action: Remove any new or old references.