View Exhibit1 and examine the structure of the product table.
View Exhiblt2 and examine the procedure you created. The procedure uses the prod id to
determine whether the list price is within a given range.
You then create the following trigger on the product table.
CREATE OR REPLACE TRIGGER check_price__trg
BEF0RE INSERT OR UPDATE OF prod_id, prod_list_price
ON product FOR EACH ROW
WHEN (nev.prod_id <> NVX(old.prod_id,0) OR
New.prod__list_price <> NVL(old.prod_list_price, 0) )
BEGIN
check_price (: new.prod_id) ;
END
/
Examine the following update command for an existing row in the product table.
SQL> UPDATE produce SET prod_list_price = 10 WHERE prod_id=115;
Why does it generate an error?
A.
Because the procedure call in the trigger is not valid
B.
Because the condition specified in the when clause is not valid
C.
Because boththe procedure and trigger access the same table
D.
Because the WHEN clause cannot be used with a row-level trigger
E.
Because the column list specified with UPDATE in the trigger is not valid
ะก
Mutating tabe error
C
c
UPDATE product SET prod_list_price = 10 WHERE prod_id=115;
Result:
—-
Error starting at line : 1 in command –
UPDATE product SET prod_list_price = 10 WHERE prod_id=115
Error report –
SQL Error: ORA-04091: table HR.PRODUCT is mutating, trigger/function may not see it
ORA-06512: at “HR.CHECK_PRICE”, line 4
ORA-06512: at “HR.CHECK_PRICE_TRG”, line 2
ORA-04088: error during execution of trigger ‘HR.CHECK_PRICE_TRG’
04091. 00000 – “table %s.%s is mutating, trigger/function may not see it”
*Cause: A trigger (or a user defined plsql function that is referenced in
this statement) attempted to look at (or modify) a table that was
in the middle of being modified by the statement which fired it.
*Action: Rewrite the trigger (or function) so it does not read that table.
Correct answer: C
C
D: Because the WHEN clause cannot be used with a row-level trigger <== this is incorrect.
Trigger Syntax
CREATE [OR REPLACE ] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF }
{INSERT [OR] | UPDATE [OR] | DELETE}
[OF col_name]
ON table_name
[REFERENCING OLD AS o NEW AS n]
[FOR EACH ROW]
WHEN (condition)
BEGIN
— sql statements
END;
Yes it can.
WHEN (condition):
————
Specifies a SQL condition that the database evaluates for each row that the triggering
statement affects. If the value of condition is TRUE for an affected row, then trigger_
body runs for that row; otherwise, trigger_body does not run for that row. The
triggering statement runs regardless of the value of condition.
The condition can contain correlation names (see “referencing_clause ::=” on
page 14-58). In condition, do not put a colon (:) before the correlation name NEW, OLD,
or PARENT (in this context, it is not a placeholder for a bind variable).
The Trigger tries to select from the table while it is being updated, so it is Mutaing table error. Also it is row level trigger. Only row level trigger can get mutating table error.
C
C
C