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);