i have a table with 4 columns
1.msisdn
2.accountnumber
3.cardnumber
4.subscriptiondate
I want to add a trigger to this table. If the data i am inserting is
1.99999999
2.2
3.3298572857239
4.(this can be blank)
and the data that is currently in the table is
1.99999999
2.1
3.3298572857239
4.(this can be blank)
Trigger should check if there is this msisdn 99999999 is already having a record with this cardnumber 3298572857239. 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
1.99999999
2.1
3.3298572857239
4.(this can be blank)
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
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;
/