Question

1a) Develop a trigger that will set CustBalance in Customer table to 100 if the balance (either inserted or updated) is less than 100

b) Create a function called Balance_Format. This function should convert a number to a string formatted as Canadian currency. For example, 33.2 will become CAD $33.2

ExternalCustomer table

Column Name	Data type	Sample data
CustomerID	NUMBER	1
CustomerName	VARCHAR2(24)	Roger Waters
PhoneNumber	VARCHAR2(14)	9058822222
Balance	NUMBER(8,2)	3200.12

Customer Table

Column Name	Data type	Sample data
CustID	NUMBER	1
CustFirstName	VARCHAR2(12)	Roger
CustLastName	VARCHAR2(12)	Waters
CustPhoneNum	VARCHAR2(18)	(905) 882-2222
CustBalance	NUMBER(8,2)	3200.00

Answer 1

1a)

create or replace trigger custBalanceTrigger
After insert or update
on ExternalCustomer
for each row
begin
   if :NEW.Balance < 100 then
       update Customer set CustBalance = 100 where CustID = :new.CustomerID;
   end if;
end;
  

1b)

create or replace function Balance_Format(inArg number)
   return varchar2 is inStr varchar2(100)
  
   begin
       inStr = concat('CAD $', to_char(inArg));
       return inStr;
   end;