CODEWITHSUNDEEP
oracle-databaseplsqltriggerssql-updatesql-insert
Tuhin
Tuhin

Reputation: 37

PL/SQL trigger to prevent the entry in one table based on the value the column in compare to value with other table

I have the following two tables:

TBL_EMPLOYEE

EMP_ID| FIRST_NAME| LAST_NAME|  PPS|        SALARY| JOB_ID| OFFICE_ID| MANAGER_ID| IND_ID
E001    JOHN        KENNEDY     12345ABCDE  90000   J001                            IN01
E002    BILL        CLINTON     12345ABCXE  80000   J002    OF01        E001        IN02
E003    BARAK       OBAMA       18745ABCXE  80000   J002    OF02        E001        IN03
E004    GORGE       BUSH        88745ABCXF  60000   J004    OF04        E002        IN04
E005    GORGE SR.   BUSH        45745ABCXS  70000   J003    OF03        E003        IN04
E006    LYNDON      JOHNSON     85345ABCYT  75000   J004    OF04        E002        IN06

TBL_CUST

CUST_ID| CUST_NAME|     CREATED_DATE| TRUSTED| IND_ID| EMP_ID| CUST_SCONT_ID| CUST_PCONT_ID
C100        ACCENTURE   23-DEC-19       N       IN04    E004                    PA02
C200        PWC         23-DEC-19       N       IN05    E005                    PA03
C300        ANDERSON    23-DEC-19       N       IN03    E003    SA01            PA04
C400        VODAFONE    23-DEC-19       N       IN02    E002    SA03            PA01
C500        GOODMAN     23-DEC-19       N       IN01    E001    SA05            PA05

And I have developed a PL/SQL trigger that will prevent an insert or update if the 'IND_ID' in the TBL_CUST is not same as TBL_EMPLOYEE. The EMP_ID of the TBL_CUST table is the foreign key.

CREATE OR REPLACE TRIGGER TRG_check_consistency_of_the_industry
    BEFORE INSERT OR UPDATE 
    ON tbl_cust
    FOR EACH ROW 
DECLARE
    v_industry_type1 varchar2(4);
    v_industry_type2 varchar2(4);
BEGIN
    IF :new.cust_id IS NOT NULL THEN
        v_industry_type1 := :NEW.ind_id;

        SELECT e.ind_id INTO v_industry_type2
        FROM tbl_cust c 
        INNER JOIN tbl_employee e ON c.emp_id = e.emp_id
        WHERE e.emp_id = :new.emp_id;

        IF v_industry_type1 <> v_industry_type2 THEN
            raise_application_error(-20025, 'The industry of the customer is not the same the account manager');
        END IF;
    END IF;
END;

But when I am trying to make the following entry:

INSERT INTO tbl_cust(cust_id, cust_name, ind_id, emp_id,cust_pcont_id) VALUES ('C600', 'DISNEY', 'IN03', 'E006', 'PA06');

I am getting this error

Error starting at line : 56 in command -
INSERT INTO tbl_cust(cust_id, cust_name, ind_id, emp_id,cust_pcont_id) VALUES ('C600', 'DISNEY', 'IN03', 'E006', 'PA06')
Error report -
ORA-01403: no data found
ORA-06512: at "TTAPADAR.TRG_CHECK_CONSISTENCY_OF_THE_INDUSTRY", line 8
ORA-04088: error during execution of trigger 'TTAPADAR.TRG_CHECK_CONSISTENCY_OF_THE_INDUSTRY'

Upvotes: 0

Views: 186

Answers (1)

Ergi Nushi
Ergi Nushi

Reputation: 863

Since you are saying:

SELECT e.ind_id INTO v_industry_type2
FROM tbl_cust c 
INNER JOIN tbl_employee e ON c.emp_id = e.emp_id
WHERE e.emp_id = :new.emp_id;

... when there isn't an employee with the new id that is being inserted, SELECT will raise NO_DATA_FOUND exception.

What you need to do is handle this exception:

CREATE OR REPLACE TRIGGER TRG_check_consistency_of_the_industry
    BEFORE INSERT OR UPDATE 
    ON tbl_cust
    FOR EACH ROW 
DECLARE
    v_industry_type1 varchar2(4);
    v_industry_type2 varchar2(4);
BEGIN

    IF :new.cust_id IS NOT NULL THEN
        v_industry_type1 := :NEW.ind_id;

        SELECT e.ind_id INTO v_industry_type2
        FROM tbl_cust c 
        INNER JOIN tbl_employee e ON c.emp_id = e.emp_id
        WHERE e.emp_id = :new.emp_id;

        IF v_industry_type1 <> v_industry_type2 THEN
        raise_application_error(-20025, 'The industry of the customer is not the same the account manager');
        END IF;
    END IF;


    -- handle the exception here
    EXCEPTION
        WHEN OTHERS THEN
            NULL; --> DO NOTHING
END;

Hope this helps.

Upvotes: 1

Related Questions