CODEWITHSUNDEEP
oracle-databaseplsql
jaouadi
jaouadi

Reputation: 5

plsql how to fire trigger only when a function X is activated?

I have two functions. The first is to withdraw money (the traditional way, I mean when someone withdraw from agency). The function will not allow the withdrawal when client balance is less than amount asked.

Then I create another function to withdraw 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.

That means this trigger will fire only when the second function is activated (the function that make withdrawal using card) because the client can have an expired card but he can still use the first function, i.e. the withdrawal from agency.

These are my tables:

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

Upvotes: 0

Views: 24

Answers (1)

Littlefoot
Littlefoot

Reputation: 142768

If you're trying to update the compte table (i.e. withdraw some money), you have to know how is someone doing it - by using a card, or at the agency. I presume that's what the etat column says (see line #4). If not, how do you know it, then? Include that condition into the trigger.

Therefore: if you're using a card, check its expiration date and raise an error if it is lower than right now:

SQL> create or replace trigger trg_bu_compte
  2    before update on compte
  3    for each row
  4    when (new.etat = 'CARD')    --> I have no idea whether that's it, but - you have
  5  declare                       --> to have "something" what says wheteher you're
  6    l_dateexpiration date;      --> using a card or not
  7  begin
  8    select ca.dateexpiration
  9      into l_dateexpiration
 10      from carte ca
 11      where ca.numerocompte = :new.numerocompte;
 12
 13    if l_dateexpiration < trunc(sysdate) then
 14       raise_application_error(-20000, 'Card has expired');
 15    end if;
 16  end trg_bu_compte;
 17  /

Trigger created.

SQL>

Upvotes: 1

Related Questions