Trigger to not allow a member to rent if unpaid balance exceeds $50

I want to create a trigger so that no rental is allowed if a members unpaid balance fine exceeds 50. At the bottom I have 2 attempts at the problem. But when I insert data for Mike Vick, it does not raise the application error. Mike Vicks unpaid balance fine is 55 dollars so it should trip the flag.

INSERT INTO Transaction
    VALUES ('6','Mem101','V104','C1', '14-Nov-14', '16-Nov-14', 'R101');

What can I do to fix this? Thanks!


--Trigger #3

--Attempt #1
 CREATE or Replace Trigger Unpaid_Balance_Rule
 BEFORE INSERT ON Member
 FOR EACH ROW
 BEGIN
 IF :new.Unpaid_Bal > 50 THEN
 RAISE_APPLICATION_ERROR(-20003, 'Balances exceeds 50 dollars, please pay dues before renting');
 END IF;
 END;
/
show errors;


--Attempt #2

Create or Replace Trigger UnpaidBal_50_Rule
 Before insert on Member
 For Each Row

Begin
 Select Mem_num into OverBalance from Member where unpaid_bal > 50;
  IF (:new.Mem_num = OverBalance )
  THEN  raise_application_error ( -20003, 'Balances exceeds 50 dollars, please pay dues before renting');
  END IF;
 end; 
 /
 show error;

Answer

Your attempt 2 will not work since you are trying to query the table Member before inserting the data

Your attempt 1 should work but it is always good to insert number in number column instead of relying on implicit conversion

hence try the below

--Insert for Member Table
INSERT INTO Member
VALUES ('Mem101','Mike Vick','Atlanta','320-274-3179',to_date('14-Oct-12','dd-mon-yyyy'),55);

Leave a Reply

Your email address will not be published. Required fields are marked *