View the Exhibit and examine the structure of the customer table.
Examine the following trigger code:
What is the outcome when the above trigger is compiled?
A.
It compiles successfully.
B.
It gives an error because the when condition is not valid.
C.
It gives an error because when cannot be used for row-level triggers.
D.
It gives an error because the statements under updating are not valid.
E.
It gives an error because the new qualifier in the when clause requires a colon prefix.
E
Errors: check compiler log
Error(3,6): PLS-00049: bad bind variable ‘NEW.CUS_CATEGORY’
EDIT: B
A
a
It’s A
—
CREATE OR REPLACE TRIGGER max_credit_limit
BEFORE INSERT OR UPDATE OF MANAGER_ID ON EMPLOYEES
FOR EACH ROW WHEN (NEW.MANAGER_ID IS NULL)
BEGIN
IF INSERTING THEN :NEW.MANAGER_ID := 100;
:NEW.SALARY := 8000;
ELSIF UPDATING THEN
:NEW.MANAGER_ID := :OLD.MANAGER_ID;
:NEW.SALARY := :OLD.SALARY;
END IF;
END;
TRIGGER MAX_CREDIT_LIMIT compiled
Its A.
TRIGGER max_credit_limit compiled.
CREATE TABLE customer
(
cust_id number not null,
cust_last_name varchar2 (30) not null,
cust_city varchar2 (30) not null,
cust_credit_limit number,
cust_category varchar2 (30)
);
/
Create table EMPLOYEES
(MANAGER_ID number,
SALARY number);
/
CREATE OR REPLACE TRIGGER max_credit_limit
BEFORE INSERT OR UPDATE OF MANAGER_ID ON EMPLOYEES
FOR EACH ROW WHEN (NEW.MANAGER_ID IS NULL)
BEGIN
IF INSERTING THEN :NEW.MANAGER_ID := 100;
:NEW.SALARY := 8000;
ELSIF UPDATING THEN
:NEW.MANAGER_ID := :OLD.MANAGER_ID;
:NEW.SALARY := :OLD.SALARY;
END IF;
END;
Trigger MAX_CREDIT_LIMIT compiled
B and D are incorrect.
If
FOR EACH ROW WHEN (:NEW.MANAGER_ID IS NULL)
then
ORA-25000: invalid use of bind variable in trigger WHEN clause
option C is wrong, when can not be sed in Stmt triggers
A is the right answer,
A
questions on website are correct, answers are wrong, make sure to go through the code and run it, just passed with a 95
A
Hi there, I came upon aiotestking.com while researching a a similar topic in the search engines. It seems to be a good read; I’ve bookmarked it in my Yahoo favourites.
https://tonsilstonesauthority.wordpress.com/
??
I’m gratified with the way that aiotestking.com deals with this kind of subject. Usually to the point, sometimes contentious, always thoughtful and also stimulating.
http://www.youtube.com/watch?v=F0fhaRZjfQA
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 ::=”). 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).
https://docs.oracle.com/cd/E11882_01/appdev.112/e25519/create_trigger.htm#LNPLS01374