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.
A is correct answer.
BUT iF customer with cust_id = 101 EXISTs
But there is no column name for a row trigger. Won’t it throw an error?
no.
correct ans A(100%)
A
A
result A is correct
create or replace trigger ver_update after insert or update on customers for each row
begin
if :new.GENDER in (‘F’, ‘M’) and :new.CREDIT_LIMIT > 8000 then
dbms_output.put_line(‘abc’);
end if;
end;
update customers set CREDIT_LIMIT = 9000, GENDER = ‘F’ where customer_id = 240;
select * from customers where customer_id = 240;
Just tried
Right reply is D for real.
my bed I tried again.
Answer is A.
Correct Answer : A
It is tested & verified
A.
The trigger is fired, a message is displayed, and the update or insert is successful
================================
Example:
create or replace trigger emp_biu
BEFORE INSERT OR UPDATE
of salary
on employee
for each row
declare
v_error VARCHAR2(2000);
begin
if :new.salary > 10000
then
dbms_output.put_line (‘THERE IS NO WAY’);
dbms_output.put_line (‘never never never!!’);
end if;
end;
/
Just tested, A is the correct answer
A is correct
A is correct
A
Answer: A
set serveroutput on
delete from hr.customer;
commit;
insert into hr.customer(cust_id, cust_last_name, cust_city, cust_credit_limit, cust_category)
values(101, ‘BagelRadio’, ‘Foxhol’, 7500, ‘A’);
commit;
select * from hr.customer;
create or replace trigger restrict_credit_limit
before insert or update on hr.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;
/
show errors
update hr.customer
set cust_category = ‘C’, cust_credit_limit = 9000
where CUST_ID = 101;
commit;
select * from hr.customer;
/*OUTPUT
1 row deleted.
Commit complete.
Credit Limit cannot be greater than 8000 for this category
1 row inserted.
Commit complete.
CUST_ID CUST_LAST_NAME CUST_CITY CUST_CREDIT_LIMIT CUST_CATEGORY
———- —————————————- —————————— —————– ——————–
101 BagelRadio Foxhol 7500 A
Trigger RESTRICT_CREDIT_LIMIT compiled
No errors.
1 row updated.
Commit complete.
Credit Limit cannot be greater than 8000 for this category
CUST_ID CUST_LAST_NAME CUST_CITY CUST_CREDIT_LIMIT CUST_CATEGORY
———- —————————————- —————————— —————– ——————–
101 BagelRadio Foxhol 9000 C
*/