How To Create A Pl/sql Row Trigger That Validates A Column From Another Table

I have two tables: CREATE TABLE PRODUCT ( PRODUCT_NAME VARCHAR(40) NOT NULL, SUPPLIER_NAME VARCHAR(40) NOT NULL, CATEGORY_NAME VARCHAR(30) NOT NULL, QUANTITY_PER_UNI

Solution 1:

There are several issues with your trigger. Let's begin with the 'relationship' between a select statement and remaining code. In this particular case the select.. and the if...end_if (for the moment assume your select actually works, it does not but just assume). Now concentrate on the WHERE clause.

SELECT SUPPLIER.TRUSTED_SUPPLIER
    INTO TRUST
    ...
    WHERE SUPPLIER.TRUSTED_SUPPLIER ='YES';

IF TRUST ='NO'THEN ...

Since your select returns ONLY YES the if statement will never be True. Therefore the application exception can never be raised. Now, what are the issues with the select. Well first you are accessing the table that the trigger is fired upon. While in some cases you can get away with it but usually it results in an ORA-04091: table <table_name> is mutating, trigger/function may not see it. It is bust to always avoid referencing the triggering table altogether. You reference the table data with the :NEW and/or :OLD pseudo records. Secondly, your query is not doing what you think it is. It says

Select the trusted_supplier column for every row in the Supplier table that has at least 1 row in the Product table and the trusted_supplier column is 'YES'.

However the INTO clause requires the statement to return exactly 1 row. More that 1 row results in the exception, and 0 rows results in a no data found exception. Finally there is an issue with the raise_application_error statement. If it were executed it would raise an number argument...is out of range exception. The first parameter must be between -20999 to -20000 (Negative number). So what does the result look like:

createor replace trigger verify_supplier_trust
before insertorupdateon product
foreachrowdeclare 
    trust varchar2(3);

beginselect supplier.trusted_supplier
      into trust
      from supplier 
     where supplier.company_name = :new.supplier_name
       and supplier.trusted_supplier ='YES';
exception
   when no_data_found then 
        raise_application_error(-20001, 'supplier not trusted');
end;
/

NOTES: DO not use data type VARCHAR. It is allowed but Oracle recommends against it. Means they are reserving the right to change what it does at any time. Use the recommended VARCHAR2 instead. I change the trigger to fire on either Insert or Update. If fired on Insert only someone CAN change the supplier_name to reference a non-trusted supplier and all would be fine.

Solution 2:

You must filter by the inserted row's id!

And not by SUPPLIER.TRUSTED_SUPPLIER = 'YES'

BEGINSELECT SUPPLIER.TRUSTED_SUPPLIER
    INTO TRUST
    FROM SUPPLIER
    INNERJOIN PRODUCT ON PRODUCT.SUPPLIER_NAME = SUPPLIER.COMPANY_NAME
    WHERE PRODUCT.PRODUCT_NAME = :NEW.PRODUCT_NAME;

Solution 3:

Try this:

createor replace TRIGGER VERIFY_SUPPLIER_TRUST
BEFORE INSERTON PRODUCT
REFERENCINGNEWAS NEW_PRODUCT
FOREACHROWDECLARE TRUST_COUNT NUMBER;

BEGINSELECTCOUNT(*) 
    INTO TRUST_COUNT
    FROM SUPPLIER    
    WHERE SUPPLIER.TRUSTED_SUPPLIER ='YES'and SUPPLIER.COMPANY_NAME = :NEW_PRODUCT.SUPPLIER_NAME;

    IF TRUST_COUNT =0THEN 
      RAISE_APPLICATION_ERROR(20001, 'SUPPLIER NOT TRUSTED');
    END IF;
END;
/

For this set of data:

INSERTINTO SUPPLIER(COMPANY_NAME,CONTACT_NAME,CONTACT_TITLE,ADDRESS,CITY,REGION,POSTAL_CODE,COUNTRY,PHONE,FAX,HOME_PAGE,TRUSTED_SUPPLIER) 
VALUES('C1','C1','CT1','A1','R1','PC1','C1','P1','F1','H1','H1','YES');

INSERTINTO SUPPLIER(COMPANY_NAME,CONTACT_NAME,CONTACT_TITLE,ADDRESS,CITY,REGION,POSTAL_CODE,COUNTRY,PHONE,FAX,HOME_PAGE,TRUSTED_SUPPLIER) 
VALUES('C2','C1','CT1','A1','R1','PC1','C1','P1','F1','H1','H1','NO');
COMMIT;

This insert works:

INSERTINTO PRODUCT(PRODUCT_NAME, SUPPLIER_NAME, CATEGORY_NAME, QUANTITY_PER_UNIT, UNIT_PRICE, UNITS_IN_STOCK, UNITS_ON_ORDER, REORDER_LEVEL, DISCONTINUED)
VALUES('Backlestan111','C1', 'Beverages', '10 boxes x 30 bags',  11, 20, 0, 27, 'N'); /* it works*/

But this one, doesn't:

INSERTINTO PRODUCT(PRODUCT_NAME, SUPPLIER_NAME, CATEGORY_NAME, QUANTITY_PER_UNIT, UNIT_PRICE, UNITS_IN_STOCK, UNITS_ON_ORDER, REORDER_LEVEL, DISCONTINUED)
VALUES('Backlestan222','C2', 'Beverages', '10 boxes x 30 bags',  11, 20, 0, 27, 'N'); /* it doesn't work*/