=======================TRIGGER ===========================
CREATE OR REPLACE TRIGGER qtydecrement
AFTER INSERT ON PORDER
FOR EACH ROW
begin
DECLARE
amount int;
SELECT Quantity INTO QUANTITY FROM MATERIALHOUSE WHERE new.r_materialid=:Materialno;
IF amount<=:NEW.R_QTY
THEN
RAISE(-20001,'OUT OF STACK');
ELSE
UPDATE MATERIALHOUSE SET Quantity=Quantity-:NEW.R_QTY WHERE new.materialid=materialno;
END IF;
END;
/
======================================final=========================
CREATE OR REPLACE TRIGGER BILLING
AFTER INSERT ON PORDER
FOR EACH ROW
DECLARE
PRICE NUMBER(10);
SALARY NUMBER(10);
begin
SELECT RATE INTO PRICE FROM MATERIALHOUSE WHERE MNUM=:NEW.RM_NUM;
SELECT WAGES INTO SALARY FROM EMPLOYEE WHERE EMPID=:NEW.R_EMPID;
INSERT INTO BILL(BILLNO,B_MNUM,B_QTY,DOP,TOTPRICE,B_CMPNUM,B_VEHINO,B_EMPID)VALUES(:NEW.ORDERNO,:NEW.RM_NUM,:NEW.R_QTY,SYSDATE,0,:NEW.R_CMPNUM,:NEW.R_VEHINO,:NEW.R_EMPID);
UPDATE BILL SET TOTPRICE=:NEW.R_QTY*PRICE+SALARY WHERE BILLNO=:NEW.ORDERNO;
END;
===========================================================