0 Replies - 5187 Views - Last Post: 10 November 2012 - 03:35 AM

#1 pmcmahon  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 26
  • Joined: 14-January 11

Trigger insert value before exception

Posted 10 November 2012 - 03:35 AM

Hi

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.

Is This A Good Question/Topic? 0
  • +

Page 1 of 1