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 both the 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
|
Download Printable PDF. VALID exam to help you PASS. |
 |
I think correct answer is C:
Provided code contains many errors. But was able to reproduce it in SH schema, products table:
create or replace procedure check_price (p_prod_id number) is
v_price products.prod_list_price%type;
begin
select prod_list_price into v_price
from products
where prod_id=p_prod_id;
if v_price not between 20 and 30 then
raise_application_error(-20100,’Price not in range’);
end if;
end;
/
create or replace trigger check_price_trg
before insert or update of prod_id,prod_list_price
on products for each row
when(new.prod_idNVL(old.prod_id,0) or
new.prod_list_pricenvl(old.prod_list_price,0))
begin
check_price(:new.prod_id);
end;
/
UPDATE products SET prod_list_price = 10 WHERE prod_id=115;
*
ERROR at line 1:
ORA-04091: table SH.PRODUCTS is mutating, trigger/function may not see it
ORA-06512: at “SH.CHECK_PRICE”, line 4
ORA-06512: at “SH.CHECK_PRICE_TRG”, line 2
ORA-04088: error during execution of trigger ‘SH.CHECK_PRICE_TRG’
https://asktom.oracle.com/pls/apex/f?p=100:11:::NO::P11_QUESTION_ID:9536903800346619276
“You can’t query the table that caused a trigger to fire inside the trigger itself.”
?