0

I want to create a trigger PL/SQL for the table: ap_supplier_sites_all (oracle EBS R12), in this table i have below information: vendor_site_id (primary key), vendor_id, attribute4 (varchar250), my trigger will be developp for avoid the insert or update the attribute4 if this attribute4 exists in the table for each vendor_id,

I have two triggers:

1first trigger:

CREATE OR REPLACE TRIGGER TRIG
BEFORE INSERT OR UPDATE of attribute4 ON ap_supplier_sites_all
referencing new as new old as old
for each row
BEGIN
  IF :new.attribute4 in (123,789,78965,12345) -- (select distinct attribute4 from ap_supplier_sites_all) --(123,789,78965,12345) trigger and vendor_id not in
  THEN
    RAISE_APPLICATION_ERROR(-20101, 'This RIB exists with another Suppliers.Please try with a new RIB');
    ROLLBACK;
  END IF;
END;

I have two issues for this trigger:

  1. I can't compare the :new.attribute4 for the selection in the table (select distinct attribute4 from ap_supplier_sites_all)

  2. this trigger check the attribute4 for each vendor_site_id not for the vendor_id, because the vendor_site_id is the primary key.

2 second trigger:

CREATE OR REPLACE TRIGGER TRIGg
BEFORE insert or update ON ap_supplier_sites_all
referencing new as new old as old
for each row
DECLARE
  v_vendor_id ap_supplier_sites_all.vendor_id%TYPE; 
  v_attribute4 ap_supplier_sites_all.attribute4%TYPE; 
  CURSOR cur IS
    SELECT distinct ap_supplier_sites_all.attribute4,ap_supplier_sites_all.vendor_id
    from ap_supplier_sites_all; 
BEGIN
  open cur;
  --for attribute4 in (SELECT distinct ap_supplier_sites_all.attribute4 from ap_supplier_sites_all)
  LOOP
    FETCH cur INTO v_attribute4,v_vendor_id;
    EXIT WHEN cur%NOTFOUND;
    IF :new.attribute4=v_attribute4
    THEN
      RAISE_APPLICATION_ERROR(-20101, 'This RIB exists with another Suppliers.Please try with a new RIB'); 
    END IF;
  END LOOP;
  CLOSE cur;
END;

when I do this trigger, I have the issue from oracle application:

Error while saving Saving :Vendor Site Name azertyyyiii: Vendor site id :844562. Error :ORA-20001: APP-SQLAP-10000: ORA-04091: table AP.AP_SUPPLIER_SITES_ALL is mutating, trigger/function may not see it ORA-06512: at "APPS.TRIGG", line 6 ORA-06512: at "APPS.TRIGG", line 9 ORA-04088: error during execution of trigger 'APPS.TRIG in Package AP_VENDOR_PUB_PKG Procedure Update_Vendor_Site

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.