View the Exhibit and examine the structure of the customer table.
You create the following trigger to ensure that customers belonging to category "A" or "B" in the customer table can have a credit limit of more than 8000.
What is the outcome?
A. The trigger is fired, a message is displayed, and the update is successful
B. The trigger is fired and a message is displayed, but the update is rolled back.
C. The trigger is not fired because the when clause should be used to specify the condition, however, the update is successful.
D. The trigger is not fired because column names must be specified with the update event to identify which columns must be changed to cause the trigger to fire, however, the update is successful.
|
Download Printable PDF. VALID exam to help you PASS. |
 |
A is correct as long as cust_category is not ‘A’ or ‘B’.
A is correct.
This can be verified with following commands:
create table customer
(
cust_id number not null,
cust_last_name varchar2(40) not null,
cust_city varchar2(30) not null,
cust_credit_limit number,
cust_category varchar2(20)
);
insert into customer values
(101,’Ward’,’London’,5000,’A’);
commit;
CREATE OR REPLACE TRIGGER restrict_credit_limit
BEFORE INSERT OR UPDATE ON CUSTOMER
FOR EACH ROW
BEGIN
IF (:NEW.CUST_CATEGORY NOT IN (‘A’,’B’)
AND :NEW.CUST_CREDIT_LIMIT>8000) THEN
DBMS_OUTPUT.PUT_LINE(‘Credit limit cannot be greater than 8000 for this category’);
END IF;
END;
/
set linesize 200;
col cust_last_name format a5
col cust_city format a10;
select * from customer;
CUST_ID CUST_ CUST_CITY CUST_CREDIT_LIMIT CUST_CATEGORY
———- —– ———- —————– ——————–
101 Ward London 5000 A
set serveroutput on;
update customer set cust_category=’C’, cust_credit_limit=9000
where cust_id=101;
Credit limit cannot be greater than 8000 for this category
1 row updated.
SQL> select * from customer;
CUST_ID CUST_ CUST_CITY CUST_CREDIT_LIMIT CUST_CATEGORY
———- —– ———- —————– ——————–
101 Ward London 9000 C