Forum Stats

  • 3,875,385 Users
  • 2,266,909 Discussions
  • 7,912,189 Comments

Discussions

how to create a trigger on pl/sql when a function was used

User_7MMNG
User_7MMNG Member Posts: 2 Green Ribbon

hello guy i'm trying to learn PLSQL and i'm stack on how to create a trigger on pl/sql when a function was used.

i'm tring to create to create a bank database so i create some table like account(numaccount, name, balance, num_card) num_card is a froeigen key card( num_card, max_delay, datecard)

so i make some functions like

-a function that alow to withdaral money from bank branch

-a function that alow to withdaral money using card

-a trigger avoid withdrawaling money when sold < ammount

now i want to create a trigger that avoid withdrawaling money when datecard is < of today date! this is my code:


CREATE OR REPLACE TRIGGER expirationcarte

BEFORE update ON COMPTE 

for each row

DECLARE

v_date CARTE.DATEEXPIRATION%type;

BEGIN

select DATEEXPIRATION INTO v_date FROM CARTE C, COMPTE O WHERE C.NUMEROCOMPTE = O.NUMEROCOMPTE;

IF (v_date < to_char(SYSDATE,'MM-DD-YY') ) THEN

RAISE_APPLICATION_ERROR(-20012, 'la carte est expiré');

END IF;

END;

/



i want this trigger only work when the function that alow to withdaral money using card is runing!!

Tagged:

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,778 Red Diamond

    Hi, @User_7MMNG

    how to create a trigger on pl/sql when a function was used.

    I've never heard of a way to make a trigger fire when a function is used. If you want something to happen automatically whenever a particular function is used, then you can modify the function to do it. If you can't modify the function, then write a wrapper function that calls it, and have the wrapper do it before or after actually calling the function. Don't give anyone privileges to run the function directly; only the owner of the wrapper function needs to have privileges on the original function, and all other users only need privileges to call the wrapper function.

    Do you really want a trigger that fires when a particular function is called, or do you want a trigger that fires when a table is updated? Post a complete test case, for example:

    "I have a table like this: CREATE TABLE ... Let's say it contains this data: INSERT ... If someone then issues this statement: UPDATE ... it should raise an error and not allow the update because .... However, if someone issues this statement: UPDATE ... it should work, because ..."


    By the way:

    ... BEFORE update ON COMPTE 

    for each row

    DECLARE

    v_date CARTE.DATEEXPIRATION%type;

    BEGIN

    select DATEEXPIRATION INTO v_date FROM CARTE C, COMPTE O WHERE ...

    A FOR EACH ROW trigger on compte can't query compte itself.

  • User_7MMNG
    User_7MMNG Member Posts: 2 Green Ribbon

    those are my table

    CREATE TABLE COMPTE(

    NUMEROCOMPTE VARCHAR2(10),

    SOLDE NUMBER(38,3),

    SEUILDEBIT NUMBER(38,3),

    DATESOLDE DATE,

    ETAT VARCHAR2(10),

    CONSTRAINT pk_NUMEROCOMPTE PRIMARY KEY (NUMEROCOMPTE)

    );

    CREATE TABLE CARTE(

    NUMEROCARTE VARCHAR2(16),

    TYPECARTE VARCHAR2(10),

    PLAFOND NUMBER(38,3),

    DATEEXPIRATION DATE,

    NUMEROCOMPTE VARCHAR2(10),

    CONSTRAINT pk_NUMEROCARTE  PRIMARY KEY (NUMEROCARTE),

    CONSTRAINT fk_NUMEROCOMPTE FOREIGN KEY (NUMEROCOMPTE) REFERENCES COMPTE (NUMEROCOMPTE)

    );



    i have 2 functions the first is to withdrawal money (the traditional way i mean when someone withdrawal from agency) the fonction will not allow the withdrawal when client balance is less then amount asked.

    then i create an other function is to withdrawal money from DAP using visa card.

    the task is to write a trigger to throw an exception in the case of an expiry of the date of a card.

    so that's mean this trigger will fires only when the second function is activated (the function that make withdrawal using card) because the client can have an expiry card but he can still use the first function the withdrawal from agency.

  • Saubhik
    Saubhik Member Posts: 5,933 Gold Crown
    1. Trigger is a BAD idea, this has many dwarbacks. Think of putting the logic in an API and use that for your operations.
    2. Your "row level" trigger is on a table called COMPTE and you are selecting from the same table, so you will get ORA-04091 mutating table error.
    3. If you must do it through trigger then use the below article to overcome the mutating error.


    User_7MMNG
  • BluShadow
    BluShadow Member, Moderator Posts: 42,580 Red Diamond

    Agree with the others.

    This sort of logic is not something that you should be implementing in triggers.

    Business logic such as this belongs in the business/application logic, which is in the application or related procedures/functions.

    e.g.

    create or replace function transact_money(
      account_id number
     ,card_id    number
     ,amount     number -- negative amount = withdraw money
      ) return boolean as
      eAccountError   exception;
      eCardExpired    exception;
      eCardAccount    exception
      eNotEnoughFunds exception;
      check           number := 0;
      transact_ok     boolean := true;
    begin
      begin -- checks
        -- assuming appropriate locking on tables is implemented for the transaction to serialize it
        -- not doing that here in this example
        select count(*) into check
        from   account
        where  account_id = transact_money.account_id;
    
        if check = 0 then
          raise eAccountError;
        end if;
    
        select count(*) into check
        from   card
        where  card_id = transact_money.card_id
        and    expiry_date > sysdate;
    
        if check = 0 then
          raise eCardExpired;
        end if;
    
        select count(*) into check
        from   cardaccount
        where  account_id = transact_money.account_id
        and    card_id = transact_money.card_id;
    
        if check = 0 then
          raise eCardAccount;
        end if;
    
        select count(*) into check
        from   account
        where  account_id = transact_money.account_id
        and    balance + transact_money.amount > account_lower_limit;
    
        if check = 0 then
          raise eNotEnoughFunds;
        end if;
      exception
        when eAccountError then
          transact_ok := false;
          raise_application_error(-20001, 'Account Not Found');
        when eCardExpired then
          transact_ok := false;
          raise_application_error(-20002, 'Card Expired or Not Found');
        when eCardAccount then
          transact_ok := false;
          raise_application_error(-20003, 'Card Is Not For This Account');
        when eNotEnoughFundsthen
          transact_ok := false;
          raise_application_error(-20004, 'Insufficient Funds For Transaction');
      end;
      return transact_ok;
    exception
      when others then
        -- include necessary logging of errors etc. before re-raising whatever has occurred to calling code
        -- not included here in this example 
        raise;  
    end;
    /
    


    There are various ways you can handle exceptions, but ensuring you have a consistent approach and capture exceptions and log them and raise them appropriately is important, as well as ensuring there are no 'holes' that allow exceptions to fall out of sight and permit transactions to go ahead when they shouldn't.

    User_7MMNG