title: trigger to delete a record before insert
body: <p>i have a table with 4 columns</p>
  
  <pre><code>1.msisdn
  2.accountnumber
  3.cardnumber
  4.subscriptiondate
  </code></pre>
  
  <p>I want to add a trigger to this table. If the data i am inserting is</p>
  
  <pre><code>1.99999999
  2.2
  3.3298572857239
  4.(this can be blank)
  </code></pre>
  
  <p>and the data that is currently in the table is</p>
  
  <pre><code>1.99999999
  2.1
  3.3298572857239
  4.(this can be blank)
  </code></pre>
  
  <p>Trigger should check if there is this msisdn <code>99999999</code> is already having a record with this cardnumber <code>3298572857239</code>. If there is a record already existing in the table, the trigger should delete the existing entry and insert the new one. The final result should look like this</p>
  
  <pre><code>1.99999999
  2.1
  3.3298572857239
  4.(this can be blank)
  </code></pre>
  
  <p>I want to keep the value of accountnumber same before and after the trigger. This is what i have tried so far but for this trigger, i am not getting any data in accountnumber column. Please someone help</p>
  
  <pre><code>DROP TRIGGER TRIG_TABLEA;
  
  CREATE OR REPLACE TRIGGER TRIG_TABLEA
  BEFORE INSERT ON TABLEA 
  REFERENCING OLD AS Old NEW AS New 
  FOR EACH ROW
  BEGIN
   :new.accountnumber := :old.accountnumber;
   DELETE FROM TABLEA WHERE MSISDN = :new.MSISDN AND CARDNUMBER = :new.CARDNUMBER;
   :new.MSISDN := :new.MSISDN;
   :new.CARDNUMBER := :new.CARDNUMBER;
   :new.accountnumber := :old.accountnumber;
  END;
  /
  </code></pre>