How to create a PL/SQL row trigger that validates a column from another table

Question

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_UNIT   VARCHAR(20)         NULL,
UNIT_PRICE      NUMBER(10,2)    DEFAULT 0,
UNITS_IN_STOCK  NUMBER(9)   DEFAULT 0,
UNITS_ON_ORDER  NUMBER(9)   DEFAULT 0,
REORDER_LEVEL   NUMBER(9)   DEFAULT 0,
DISCONTINUED    CHAR(1)     DEFAULT 'N',
CONSTRAINT PK_PRODUCT PRIMARY KEY (PRODUCT_NAME),
CONSTRAINT FK_CATEGORY_NAME FOREIGN KEY (CATEGORY_NAME) REFERENCES CATEGORY(CATEGORY_NAME),
CONSTRAINT FK_SUPPLIER_NAME FOREIGN KEY (SUPPLIER_NAME) REFERENCES SUPPLIER(COMPANY_NAME),
CONSTRAINT CK_PRODUCT_UNIT_PRICE CHECK (UNIT_PRICE >= 0),
CONSTRAINT CK_PRODUCT_UNITS_IN_STOCK CHECK (UNITS_IN_STOCK >= 0),
CONSTRAINT CK_PRODUCT_UNITS_ON_ORDER CHECK (UNITS_ON_ORDER >= 0),
CONSTRAINT CK_PRODUCT_REORDER_LEVEL CHECK (REORDER_LEVEL >= 0),
CONSTRAINT CK_PRODUCT_DISCONTINUED CHECK (DISCONTINUED in ('Y','N'))
);

CREATE TABLE SUPPLIER
(
COMPANY_NAME    VARCHAR(40) NOT NULL,
CONTACT_NAME    VARCHAR(30)     NOT NULL,
CONTACT_TITLE   VARCHAR(30)     NOT NULL,
ADDRESS         VARCHAR(60)     NOT NULL,
CITY        VARCHAR(15)     NOT NULL,
REGION      VARCHAR(15)         NULL,
POSTAL_CODE     VARCHAR(10)     NOT NULL,
COUNTRY         VARCHAR(15)     NOT NULL,
PHONE       VARCHAR(24)     NOT NULL,
FAX         VARCHAR(24)         NULL,
HOME_PAGE       VARCHAR(500)        NULL,
TRUSTED_SUPPLIER    VARCHAR(3)    NULL,
CONSTRAINT PK_SUPPLIER PRIMARY KEY (COMPANY_NAME)  
);

I need to create a ROW TRIGGER that automatically verifies if information about a new product to be inserted into the database is supplied by a trusted supplier. If a supplier is not trusted, then the insertion must fail and an error message must be displayed.

Here is my row trigger so far:

CREATE OR REPLACE TRIGGER VERIFY_SUPPLIER_TRUST
BEFORE INSERT ON PRODUCT
FOR EACH ROW 
DECLARE TRUST VARCHAR(3);

    BEGIN
    SELECT SUPPLIER.TRUSTED_SUPPLIER
    INTO TRUST
    FROM SUPPLIER
    INNER JOIN PRODUCT ON PRODUCT.SUPPLIER_NAME = SUPPLIER.COMPANY_NAME
    WHERE SUPPLIER.TRUSTED_SUPPLIER = 'YES';

IF TRUST = 'NO' THEN 
RAISE_APPLICATION_ERROR(20001, 'SUPPLIER NOT TRUSTED');
END IF;
END;
/

However when I try to test the trigger with valid and non-valid insert statements:

//this should insert without errors
INSERT INTO PRODUCT
(PRODUCT_NAME, SUPPLIER_NAME, CATEGORY_NAME, QUANTITY_PER_UNIT, UNIT_PRICE, 
UNITS_IN_STOCK, UNITS_ON_ORDER, REORDER_LEVEL, DISCONTINUED)
 VALUES
   ('Backlestan','Exotic Liquids', 'Beverages', '10 boxes x 30 bags', 
    11, 20, 0, 27, 'N');

// this should display the trigger's error
INSERT INTO PRODUCT
(PRODUCT_NAME, SUPPLIER_NAME, CATEGORY_NAME, QUANTITY_PER_UNIT, UNIT_PRICE, 
UNITS_IN_STOCK, UNITS_ON_ORDER, REORDER_LEVEL, DISCONTINUED)
 VALUES
   ('Backlestan','Another Company', 'Beverages', '10 boxes x 30 bags', 
    11, 20, 0, 27, 'N');

I receive these errors:

Error report -
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "RK721.VERIFY_SUPPLIER_TRUST", line 4
ORA-04088: error during execution of trigger 'BH576.VERIFY_SUPPLIER_TRUST'

Error report -
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "RK721.VERIFY_SUPPLIER_TRUST", line 4
ORA-04088: error during execution of trigger 'BH576.VERIFY_SUPPLIER_TRUST'

Does anybody know how to fix this error? Any help would be greatly appreciated!

Answer 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:

create or replace trigger verify_supplier_trust
before insert or update on product
for each row 
declare 
    trust varchar2(3);

begin
    select 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.

Answer 2

Try this:

create or replace TRIGGER VERIFY_SUPPLIER_TRUST
BEFORE INSERT ON PRODUCT
REFERENCING NEW AS NEW_PRODUCT
FOR EACH ROW 
    DECLARE TRUST_COUNT NUMBER;

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

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

For this set of data:

INSERT INTO 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');

INSERT INTO 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:

INSERT INTO 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:

INSERT INTO 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*/

Answer 3

You must filter by the inserted row's id!

And not by SUPPLIER.TRUSTED_SUPPLIER = 'YES'

BEGIN
    SELECT SUPPLIER.TRUSTED_SUPPLIER
    INTO TRUST
    FROM SUPPLIER
    INNER JOIN PRODUCT ON PRODUCT.SUPPLIER_NAME = SUPPLIER.COMPANY_NAME
    WHERE PRODUCT.PRODUCT_NAME = :NEW.PRODUCT_NAME;