Apply UNQ Constraint through Trigger and View

CREATE OR REPLACE FORCE VIEW MY_VIEW_UNQ_CALL_TARGET(ID,ENTERPRISE_ID,ADDR_STRING)
AS
SELECT S.ID,
S.ENTERPRISE_ID,
C.ADDR_STRING
FROM MYSUBSCRIBER S,MYCALL_TARGET C
WHERE S.ID = C.SUBSCRIBER_ID;

CREATE OR REPLACE TRIGGER MY_UNQ_CALL_TARGET BEFORE
INSERT OR UPDATE ON MYCALL_TARGET FOR EACH ROW

DECLARE
v_cnt number:=0;
v_enterprise_id MYSUBSCRIBER.ENTERPRISE_ID%TYPE;
FAIL_CALL_TARGET_CREATE_UPDATE EXCEPTION;
FAIL_PROCESS_CALL_TARGET EXCEPTION;
BEGIN

SELECT s.ENTERPRISE_ID INTO v_enterprise_id FROM mysubscriber s where s.id = :NEW.subscriber_id;

IF SQL%ROWCOUNT = 0 THEN
RAISE FAIL_PROCESS_CALL_TARGET;
END IF;

SELECT count(1) INTO v_cnt FROM MY_VIEW_UNQ_CALL_TARGET v_call_target
WHERE NVL(v_call_target.enterprise_id,’0′)||v_call_target.addr_string = NVL(v_enterprise_id,’0′)||:NEW.addr_string;

IF v_cnt > 0 THEN — Problem with insert
RAISE FAIL_CALL_TARGET_CREATE_UPDATE;
END IF;

EXCEPTION
WHEN FAIL_CALL_TARGET_CREATE_UPDATE THEN
RAISE_APPLICATION_ERROR(-20030,’Failed to process subscriber create/update operation Reason,combination of Enterpriseid and Phone Number/IncomingDid Number must be unique ‘);
WHEN FAIL_PROCESS_CALL_TARGET THEN
RAISE_APPLICATION_ERROR(-20030,’Failed to process subscriber create/update operation’);
END;
/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s