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.
Incorrect AnswersA: This will still result in an error and it will not achieve the intended result.
B: You may use the COUNT function in a Trigger.
C: The DBMS_OUTPUT statement is allowed in a Trigger .