Examine the trigger:
CREATE OR REPLACE TRIGGER Emp_count
AFTER DELETE ON Emp_tab
FOR EACH ROW
DELCARE
n INTEGER;
BEGIN
SELECT COUNT(*)
INTO n
FROM Emp_tab;
DBMS_OUTPUT.PUT_LINE(‘ There are now ‘ || a ||
‘ employees,’);
END;
This trigger results in an error after this SQL statement is entered:
DELETE FROM Emp_tab WHERE Empno = 7499;
How do you correct the error?
A.
Change the trigger type to a BEFORE DELETE.
B.
Take out the COUNT function because it is not allowed in a trigger.
C.
Remove the DBMS_OUTPUT statement because it is not allowed in a trigger.
D.
Change the trigger to a statement-level trigger by removing FOR EACH ROW.
Explanation:
A mutating table is a table against which a data manipulation statement has been issued and the
corresponding trigger on the DML statement is reading from the same table, at the same time.
Mutating tables are not valid for statement triggers because statement triggers fire only once for
each
event and allow the process to complete before the trigger is actually fired. Row triggers can
cause a table to mutate because row triggers fire for each row. To correct this problem you
change the trigger to a Statement-Level Trigger by removing FOR EACH ROW or by specifying
FOR EACH STATEMENT.