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
It should be A. No semi colon is needed for calling procedure in trigger.
I think Semicolon is not valid when you call procedure in trigger using “CALL” statement.Please Correct me If I am wrong.
C is correct answer as this might result in mutating table error.
C is right answer
C is correct
why C is correct? I don’t understand that
Hi All
Understand the point everyone is making about the Mutating table error. The link below provides a great explanation of this
LINK: http://www.dba-oracle.com/t_avoiding_mutating_table_error.htm
I believe the answer is B though, because the function NVX does NOT exist in Oracle (or any other database), thus the code will fail.
I tested this on my own DB…
select NVX(111,0) from dual;
Error:
ORA-00904: “NVX”: invalid identifier
00904. 00000 – “%s: invalid identifier”
*Cause:
*Action:
Error at Line: 1 Column: 7
It’s just a typing error. It’s NVL.
Bye
Martin people are saying C because the Trigger and the procedure read the same Oracle Table at the same time.
In a trigger this is a no no.
Thanks!!! Now it’s clear
B is right but E also.
its wrong code – NVX(111,0) mean NVL(111,0)
C is the correct answer. We will get a mutating table error if we try to run this update statement.
try executing the trigger as an autonomous transaction. Update statement will execute. “C”
What do you mean?
What do you mean? I think you tested “PRAGAMA AUTONOMOUS_TRANSACTION”?
C
C is correct answer
mutating table
I don’t think C) is one correct answer: the reason to have one mutating table is that “the trigger event(update statement) and the trigger body access the same table”, but the c) is the procedure and trigger access the same table, procedure is portion of triggee. I am thinking if C) is refined as ” trigger event and trigger access the same table” then C) is correct
??