Oracle Database 12c, Windows 7.
In a restaurant management systems, there are table for creating purchase invoices and purchase returns. User shouldn't be able to delete a purchase invoice if that invoice is mentioned in a purchase return document. That's why I created a foreign key constraint between purchase invoice id (PI_ID) in purchase_return_hdr table and purchase_invoice_hdr.id column.
The purchase_invoice_dtl table has a before delete trigger which checks if the item being deleted has a record in purchase_return_dtl table with the same invoice mentioned in the purchase_return_hdr. If so, then it should prevent deletion.
Everything works fine except for that when user tries to delete a record of purchase_invoice_hdr - a whole invoice, the raise_application_error in trigger "PI_DTL_SET_ON_HAND" fires before the integrity constraint violation fires.
- An error of violating integrity constraint should fire first because the user tries to delete a master record not a detail one and the trigger is on the detail table not on the master.
- Is it normal that the trigger on the detail table fires before the integrity constraint violation error? Is there away to make the integrity constraint violation fires when user tries to delete a whole invoice - a master record instead of the trigger firing?
--------------------------------------------------------
-- DDL for Sequence PURCHASE_INVOICE_PK
--------------------------------------------------------
CREATE SEQUENCE "PURCHASE_INVOICE_PK" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 6169412 CACHE 20 NOORDER NOCYCLE ;
--------------------------------------------------------
-- DDL for Sequence PURCHASE_INVOICE_DTL_PK
--------------------------------------------------------
CREATE SEQUENCE "PURCHASE_INVOICE_DTL_PK" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 4222033 CACHE 20 NOORDER NOCYCLE ;
--------------------------------------------------------
-- DDL for Sequence PURCHASE_RETURN_HDR_PK
--------------------------------------------------------
CREATE SEQUENCE "PURCHASE_RETURN_HDR_PK" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 507 CACHE 20 NOORDER NOCYCLE ;
--------------------------------------------------------
-- DDL for Sequence PURCHASE_RETURN_DTL_PK
--------------------------------------------------------
CREATE SEQUENCE "PURCHASE_RETURN_DTL_PK" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 590 CACHE 20 NOORDER NOCYCLE ;
--------------------------------------------------------
-- DDL for Table PURCHASE_INVOICE_HDR
--------------------------------------------------------
CREATE TABLE "PURCHASE_INVOICE_HDR" ("ID" NUMBER DEFAULT ON NULL "PURCHASE_INVOICE_PK"."NEXTVAL", "BP_ID" NUMBER, "DOCUMENT_DATE" DATE DEFAULT SYSDATE, "PAYMENT_TERM_ID" NUMBER DEFAULT 0, "NOTES" VARCHAR2(200), "DISCOUNT" NUMBER, "DISCOUNT_TYPE" NUMBER, "CREATED_ON" DATE DEFAULT SYSDATE, "CREATED_BY" NUMBER) ;
COMMENT ON COLUMN "PURCHASE_INVOICE_HDR"."DISCOUNT_TYPE" IS 'Currency or Percentage';
--------------------------------------------------------
-- DDL for Table PURCHASE_INVOICE_DTL
--------------------------------------------------------
CREATE TABLE "PURCHASE_INVOICE_DTL" ("ID" NUMBER DEFAULT ON NULL "PURCHASE_INVOICE_DTL_PK"."NEXTVAL", "ITEM_ID" NUMBER, "QTY" NUMBER, "PURCHASE_INVOICE_ID" NUMBER, "PRICE" NUMBER, "EXPIRY_DATE" DATE) ;
--------------------------------------------------------
-- DDL for Table PURCHASE_RETURN_HDR
--------------------------------------------------------
CREATE TABLE "PURCHASE_RETURN_HDR" ("ID" NUMBER DEFAULT ON NULL "PURCHASE_RETURN_HDR_PK"."NEXTVAL", "DOCUMENT_DATE" DATE DEFAULT SYSDATE, "PI_ID" NUMBER, "RETURN_REASON_ID" NUMBER, "NOTES" VARCHAR2(200), "CREATED_ON" DATE DEFAULT SYSDATE, "CREATED_BY" NUMBER, "BP_ID" NUMBER) ;
--------------------------------------------------------
-- DDL for Table PURCHASE_RETURN_DTL
--------------------------------------------------------
CREATE TABLE "PURCHASE_RETURN_DTL" ("ID" NUMBER DEFAULT ON NULL "PURCHASE_RETURN_DTL_PK"."NEXTVAL", "ITEM_ID" NUMBER, "QTY" NUMBER, "RETURN_REASON_ID" NUMBER, "NOTES" VARCHAR2(200), "PR_HDR_ID" NUMBER, "PRICE" NUMBER, "EXPIRY_DATE" DATE) ;
REM INSERTING into PURCHASE_INVOICE_HDR
SET DEFINE OFF;
Insert into PURCHASE_INVOICE_HDR (ID,BP_ID,DOCUMENT_DATE,PAYMENT_TERM_ID,NOTES,DISCOUNT,DISCOUNT_TYPE,CREATED_ON,CREATED_BY) values (6169394,2,to_date('13/07/2020','DD/MM/YYYY'),0,null,null,null,to_date('13/07/2020','DD/MM/YYYY'),42);
REM INSERTING into PURCHASE_INVOICE_DTL
SET DEFINE OFF;
Insert into PURCHASE_INVOICE_DTL (ID,ITEM_ID,QTY,PURCHASE_INVOICE_ID,PRICE,EXPIRY_DATE) values (4222016,5,10,6169394,10,to_date('02/08/2020','DD/MM/YYYY'));
Insert into PURCHASE_INVOICE_DTL (ID,ITEM_ID,QTY,PURCHASE_INVOICE_ID,PRICE,EXPIRY_DATE) values (4222017,331,10,6169394,22,null);
REM INSERTING into PURCHASE_RETURN_HDR
SET DEFINE OFF;
Insert into PURCHASE_RETURN_HDR (ID,DOCUMENT_DATE,PI_ID,RETURN_REASON_ID,NOTES,CREATED_ON,CREATED_BY,BP_ID) values (491,to_date('13/07/2020','DD/MM/YYYY'),6169394,null,null,to_date('13/07/2020','DD/MM/YYYY'),42,2);
REM INSERTING into PURCHASE_RETURN_DTL
SET DEFINE OFF;
Insert into PURCHASE_RETURN_DTL (ID,ITEM_ID,QTY,RETURN_REASON_ID,NOTES,PR_HDR_ID,PRICE,EXPIRY_DATE) values (572,5,1,null,null,491,10,to_date('02/08/2020','DD/MM/YYYY'));
--------------------------------------------------------
-- DDL for Trigger PI_DTL_SET_ON_HAND
--------------------------------------------------------
--------------------------------------------------------
-- DDL for Package I
--------------------------------------------------------
CREATE OR REPLACE EDITIONABLE PACKAGE "I" AS
--"I" FOR "INVENTORY.
--"P_TRANS" INDICATES ADD_STOCK = 1, OR DISPATCH_STOCK = 2.
PROCEDURE SET_ON_HAND (
p_item_id NUMBER,
p_expiry_date DATE,
p_qty NUMBER,
p_trans NUMBER
);
END i;
/
--------------------------------------------------------
-- DDL for Package Body I
--------------------------------------------------------
CREATE OR REPLACE EDITIONABLE PACKAGE BODY "I"
AS
PROCEDURE SET_ON_HAND(
P_ITEM_ID NUMBER,
P_EXPIRY_DATE DATE,
P_QTY NUMBER,
P_TRANS NUMBER)
AS
BEGIN
MERGE INTO stock s USING
(
SELECT
P_ITEM_ID DUAL_P_ITEM_ID,
P_EXPIRY_DATE DUAL_P_EXPIRY_DATE
FROM
dual
)
pid ON
(
s.ingredient_id = P_ITEM_ID AND
(
TRUNC(s.expiry_date) = TRUNC(P_EXPIRY_DATE)
OR
(
P_EXPIRY_DATE IS NULL AND s.expiry_date IS NULL
)
))
WHEN MATCHED THEN
UPDATE
SET
s.on_hand = CASE P_TRANS WHEN 1 THEN ( s.on_hand + P_QTY ) WHEN 2 THEN ( s.on_hand - P_QTY )
WHEN 3 THEN P_QTY
END
WHEN NOT MATCHED THEN
INSERT
(
INGREDIENT_ID,
on_hand,
expiry_date
)
VALUES
(
P_ITEM_ID,
DECODE(P_TRANS, 1, (P_QTY ), 2, (0 - P_QTY ), 3, P_QTY),
P_EXPIRY_DATE
);
END SET_ON_HAND;
END I;
/
--------------------------------------------------------
-- Constraints for Table PURCHASE_INVOICE_HDR
--------------------------------------------------------
ALTER TABLE "PURCHASE_INVOICE_HDR" ADD CONSTRAINT "PIH_PAYMENT_TERM_ID_NOT_NULL" CHECK (PAYMENT_TERM_ID IS NOT NULL) ENABLE NOVALIDATE;
ALTER TABLE "PURCHASE_INVOICE_HDR" ADD CONSTRAINT "PURCHASE_INVOICE_PK" PRIMARY KEY ("ID") USING INDEX ENABLE;
ALTER TABLE "PURCHASE_INVOICE_HDR" MODIFY ("CREATED_BY" NOT NULL ENABLE);
ALTER TABLE "PURCHASE_INVOICE_HDR" MODIFY ("DOCUMENT_DATE" NOT NULL ENABLE);
ALTER TABLE "PURCHASE_INVOICE_HDR" MODIFY ("BP_ID" NOT NULL ENABLE);
ALTER TABLE "PURCHASE_INVOICE_HDR" MODIFY ("CREATED_ON" NOT NULL ENABLE);
--------------------------------------------------------
-- Constraints for Table PURCHASE_INVOICE_DTL
--------------------------------------------------------
ALTER TABLE "PURCHASE_INVOICE_DTL" ADD CONSTRAINT "PI_DTL_HDRID_ITEMID_EXPIRY_UK" UNIQUE ("PURCHASE_INVOICE_ID", "ITEM_ID", "EXPIRY_DATE") USING INDEX (CREATE UNIQUE INDEX "PI_DTL_HDRID_ITEMID_UK" ON "PURCHASE_INVOICE_DTL" ("PURCHASE_INVOICE_ID", "ITEM_ID", "EXPIRY_DATE") ) ENABLE;
ALTER TABLE "PURCHASE_INVOICE_DTL" ADD CONSTRAINT "PIDTL_PRICE_OVER_ZERO_CHK" CHECK (PRICE > 0) ENABLE;
ALTER TABLE "PURCHASE_INVOICE_DTL" MODIFY ("PRICE" NOT NULL ENABLE);
ALTER TABLE "PURCHASE_INVOICE_DTL" ADD CONSTRAINT "PID_QTY_OVER_ZERO" CHECK (QTY > 0) ENABLE;
ALTER TABLE "PURCHASE_INVOICE_DTL" MODIFY ("PURCHASE_INVOICE_ID" NOT NULL ENABLE);
ALTER TABLE "PURCHASE_INVOICE_DTL" ADD CONSTRAINT "PURCHASE_INVOICE_DTL_PK" PRIMARY KEY ("ID") USING INDEX (CREATE UNIQUE INDEX "TABLE1_PK" ON "PURCHASE_INVOICE_DTL" ("ID") ) ENABLE;
ALTER TABLE "PURCHASE_INVOICE_DTL" MODIFY ("QTY" NOT NULL ENABLE);
ALTER TABLE "PURCHASE_INVOICE_DTL" MODIFY ("ITEM_ID" NOT NULL ENABLE);
--------------------------------------------------------
-- Constraints for Table PURCHASE_RETURN_HDR
--------------------------------------------------------
ALTER TABLE "PURCHASE_RETURN_HDR" MODIFY ("CREATED_BY" NOT NULL ENABLE NOVALIDATE);
ALTER TABLE "PURCHASE_RETURN_HDR" MODIFY ("CREATED_ON" NOT NULL ENABLE NOVALIDATE);
ALTER TABLE "PURCHASE_RETURN_HDR" MODIFY ("PI_ID" NOT NULL ENABLE);
ALTER TABLE "PURCHASE_RETURN_HDR" MODIFY ("DOCUMENT_DATE" NOT NULL ENABLE);
ALTER TABLE "PURCHASE_RETURN_HDR" ADD CONSTRAINT "PURCHASE_RETURN_HDR_PK" PRIMARY KEY ("ID") USING INDEX ENABLE NOVALIDATE;
--------------------------------------------------------
-- Constraints for Table PURCHASE_RETURN_DTL
--------------------------------------------------------
ALTER TABLE "PURCHASE_RETURN_DTL" MODIFY ("PRICE" NOT NULL ENABLE);
ALTER TABLE "PURCHASE_RETURN_DTL" MODIFY ("PR_HDR_ID" NOT NULL ENABLE);
ALTER TABLE "PURCHASE_RETURN_DTL" MODIFY ("ITEM_ID" NOT NULL ENABLE);
ALTER TABLE "PURCHASE_RETURN_DTL" ADD CONSTRAINT "PRDTL_QTY_OVERZERO" CHECK ("QTY">0) ENABLE;
ALTER TABLE "PURCHASE_RETURN_DTL" MODIFY ("QTY" NOT NULL ENABLE);
ALTER TABLE "PURCHASE_RETURN_DTL" ADD CONSTRAINT "PRDTL_HDRID_ITEMID_EXPIRY_UK" UNIQUE ("PR_HDR_ID", "ITEM_ID", "EXPIRY_DATE") USING INDEX ENABLE;
ALTER TABLE "PURCHASE_RETURN_DTL" ADD CONSTRAINT "PURCHASE_RETURN_DTL_PK" PRIMARY KEY ("ID") USING INDEX ENABLE;
--------------------------------------------------------
-- Ref Constraints for Table PURCHASE_INVOICE_HDR
--------------------------------------------------------
--------------------------------------------------------
-- Ref Constraints for Table PURCHASE_INVOICE_DTL
--------------------------------------------------------
ALTER TABLE "PURCHASE_INVOICE_DTL" ADD CONSTRAINT "PI_ID_PID_PID_FK" FOREIGN KEY ("PURCHASE_INVOICE_ID") REFERENCES "PURCHASE_INVOICE_HDR" ("ID") ON DELETE CASCADE ENABLE;
--------------------------------------------------------
-- Ref Constraints for Table PURCHASE_RETURN_HDR
--------------------------------------------------------
ALTER TABLE "PURCHASE_RETURN_HDR" ADD CONSTRAINT "PRH_PIID_PIH_ID_FK" FOREIGN KEY ("PI_ID") REFERENCES "PURCHASE_INVOICE_HDR" ("ID") ENABLE NOVALIDATE;
--------------------------------------------------------
-- Ref Constraints for Table PURCHASE_RETURN_DTL
--------------------------------------------------------
ALTER TABLE "PURCHASE_RETURN_DTL" ADD CONSTRAINT "PRHDR_ID_PRDTL_PRHDRID_FK" FOREIGN KEY ("PR_HDR_ID") REFERENCES "PURCHASE_RETURN_HDR" ("ID") ON DELETE CASCADE ENABLE;
CREATE OR REPLACE EDITIONABLE TRIGGER "PI_DTL_SET_ON_HAND" BEFORE
INSERT OR
UPDATE OF ITEM_ID,EXPIRY_DATE, QTY OR
DELETE ON PURCHASE_INVOICE_DTL REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW
DECLARE
V_ITEM_EXIST NUMBER;
BEGIN
IF INSERTING OR UPDATING THEN
I.SET_ON_HAND(:NEW.ITEM_ID,:NEW.EXPIRY_DATE, :NEW.QTY, P_TRANS => 1);
END IF;
IF DELETING THEN
SELECT count(1) INTO V_ITEM_EXIST FROM PURCHASE_RETURN_HDR PRH JOIN PURCHASE_RETURN_DTL PRD
ON prh.id = prd.pr_hdr_id
WHERE prd.item_id = :OLD.ITEM_ID
AND
(
TRUNC(PRD.expiry_date) = TRUNC(:OLD.EXPIRY_DATE)
OR
(
PRD.EXPIRY_DATE IS NULL AND :OLD.expiry_date IS NULL
)
)
AND prh.pi_id = :OLD.PURCHASE_INVOICE_ID ;
IF V_ITEM_EXIST > 0
THEN
RAISE_APPLICATION_ERROR('-20123','لايمكن حذف المنتج حيث انه تم ادراجه فى مستند لمردود المشتريات');
END IF;
END IF;
IF DELETING OR UPDATING THEN
I.SET_ON_HAND(:OLD.ITEM_ID,:OLD.EXPIRY_DATE, :OLD.QTY, P_TRANS => 2);
END IF;
END;
/
ALTER TRIGGER "PI_DTL_SET_ON_HAND" ENABLE;