trigger

---------------------- User e input dile SIM Table e jabe-----------------------------

CREATE OR REPLACE TRIGGER USER_DATA_AFTER_INSERT
AFTER INSERT
ON USER_DATA
FOR EACH ROW
DECLARE
V_PHONE_NO VARCHAR2(100);
V_NID_NO NUMBER ;
V_TYPE_ID NUMBER ;
V_ISSUE_DATE TIMESTAMP;
V_ISSUED_BY VARCHAR2(100);

BEGIN
V_NID_NO := :NEW.NID_NO ;
V_ISSUE_DATE := SYSDATE ;
V_ISSUED_BY := 101;

SELECT P.SIM_TYPE_ID INTO V_TYPE_ID
FROM PENDING_SIM_REQ P
WHERE P.NID_NO = V_NID_NO ;

SELECT P.ISSUED_BY INTO V_ISSUED_BY
FROM PENDING_SIM_REQ P
WHERE P.NID_NO = V_NID_NO ;



IF V_TYPE_ID = 1 THEN

SELECT APRE.SIM_NO INTO V_PHONE_NO
FROM AVAILABLE_PREPAID_SIM APRE
WHERE ROWNUM = 1 ;

DELETE FROM AVAILABLE_PREPAID_SIM
WHERE ROWNUM = 1 ;

ELSIF V_TYPE_ID = 2 THEN

SELECT  APOST.SIM_NO INTO V_PHONE_NO
FROM AVAILABLE_POSTPAID_SIM APOST
WHERE  ROWNUM = 1 ;

DELETE FROM AVAILABLE_POSTPAID_SIM
WHERE ROWNUM = 1 ;

END IF ;

INSERT INTO SIM (PHONE_NO,NID_NO,TYPE_ID,ISSUE_DATE,ISSUED_BY) VALUES (V_PHONE_NO,V_NID_NO,V_TYPE_ID,V_ISSUE_DATE,V_ISSUED_BY);

END;
/
--------------------User e input dile SIM Table e jabe end-------------------------------------


--------------------SIM e input dile prepaid postpaid table e jabe start-------------------------------------
CREATE OR REPLACE TRIGGER SIM_TO_PRE_POSTPAID
AFTER INSERT
ON SIM
FOR EACH ROW

DECLARE
V_SIM_NO VARCHAR2(100) ;
V_CALL_DURATION NUMBER ;
V_MSG NUMBER ;
V_NET NUMBER ;
V_BALANCE NUMBER ;
V_TYPE NUMBER ;

BEGIN
V_SIM_NO := :NEW.PHONE_NO;
V_CALL_DURATION := 0 ;
V_MSG := 0 ;
V_NET := 0 ;
V_BALANCE := 100 ;


V_TYPE := :NEW.TYPE_ID ;



IF V_TYPE = 1 THEN

      INSERT INTO PREPAID_SIM (SIM_NO,TOTAL_CALL_DURATION ,TOTAL_MESSAGE_SENT,TOTAL_INTERNET_USAGE,BALANCE) VALUES (V_SIM_NO,V_CALL_DURATION,V_MSG,V_NET,V_BALANCE);

ELSIF V_TYPE = 2 THEN

      INSERT INTO POSTPAID_SIM(SIM_NO,TOTAL_CALL_DURATION ,TOTAL_MESSAGE_SENT,TOTAL_INTERNET_USAGE) VALUES (V_SIM_NO,V_CALL_DURATION,V_MSG,V_NET);

END IF;

END;
/

--------------------SIM e input dile prepaid postpaid table e jabe end-------------------------------------