I'm doing a project involving creating a simplified banking database. One of the tasks is to write a trigger that prevents transactions that will cause an account to become overdrawn beyond the authorised limit. Should this be the case then a message needs to be sent - this is done by inserting a value into an 'alert' table.
So the first tasks is pretty simple - before trigger that checks the resulting account balance and raises an exception if there is an error. This is working fine. However I also need to insert the value into the alert table. Clearly if an exception is raised then the whole transaction will be rolled back including the insert into alert. Is there any way round this?
CREATE OR REPLACE TRIGGER tooOverdrawn BEFORE INSERT ON transactions FOR EACH ROW DECLARE clID NUMBER; ifCredit NUMBER; curBalance NUMBER; maxOverdraft NUMBER; balanceAfter NUMBER; accountSupervisor NUMBER; tooOverdrawn EXCEPTION; BEGIN SELECT COUNT (*) INTO siCredit FROM transaction_type WHERE typeDesc = 'Credit' AND typeID = :new.typeID; IF (siCredit = 0) THEN SELECT balanceNow, authorisedOverdraft INTO curBalance, maxOverdraft FROM compte WHERE compte.iban = :new.iban; SELECT clientID INTO clID FROM client_compte WHERE client_compte.iban = :new.iban; balanceAfter := curBalance-:new.montant; IF (balanceAfter < (maxOverdraft * -1)) THEN SELECT chargeClient INTO accountSupervisor FROM client WHERE clientID = clID; INSERT INTO alerte VALUES(7, accountSupervisor, 6, clID, 0, 'Transaction failed:', 0 ); --this is the transaction I need to remain after the exception RAISE tooOverdrawn; END IF; END IF; EXCEPTION WHEN tooOverdrawn THEN RAISE_APPLICATION_ERROR(-20000, 'Le transaction fera le compte decouvert plus que le decouvert autorise'); END;/
Thanks in advance for any help.