: SQL TRIGGER EXAMPLES

/* IN SQL PLUS SET SCREEN BUFFER TO AT LEAST 142 */

SET PAGESIZE 60
SET LINESIZE 132
COLUMN P_PRICE FORMAT 9,999.99
COLUMN P_DISCOUNT FORMAT 9,999.99
COLUMN P_DESCRIPT FORMAT A30 TRUNC

: SET SERVER MESSAGES ON
SET SERVEROUTPUT ON

: CREATE FIRST VERSION OF TRG_PRODUCT_REORDER
@TRG_PRODUCT_REORDER_V1.SQL

: TEST-> UPDATE A PRODUCT QUANTITY ON HAND
SELECT * FROM PRODUCT WHERE P_CODE = '11QER/31';

UPDATE PRODUCT 
   SET P_QOH = 4
     WHERE P_CODE = '11QER/31';

SELECT * FROM PRODUCT WHERE P_CODE = '11QER/31';

: TEST-> INCREASE P_MIN
SELECT * FROM PRODUCT WHERE P_CODE = '2232/QWE';

UPDATE PRODUCT
   SET P_MIN = 7
      WHERE P_CODE = '2232/QWE';

SELECT * FROM PRODUCT WHERE P_CODE = '2232/QWE';

: SECOND VERSION OF TRG_PRODUCT_REORDER
@TRG_PRODCUT_REORDER_V2;

: TEST -> UPDATE OF P_MIN
SELECT * FROM PRODUCT WHERE P_CODE = '23114-AA';

UPDATE PRODUCT
   SET P_MIN = 10
      WHERE P_CODE = '23114-AA';

SELECT * FROM PRODUCT WHERE P_CODE = '23114-AA';

: ERROR AFTER INCREASING P_QOH VALUE!
SELECT * FROM PRODUCT WHERE P_CODE = '11QER/31';

UPDATE PRODUCT
   SET P_QOH = P_QOH + P_MIN_ORDER
      WHERE P_CODE = '11QER/31';

SELECT * FROM PRODUCT WHERE P_CODE = '11QER/31';

: THIRD VERSION OF TRG_PRODUCT_REORDER
@TRG_PRODUCT_REORDER_V3.SQL

: TESTING THE THIRD VERSION ON TRG_PRODUCT_REORDER

SELECT * FROM PRODUCT;

UPDATE PRODUCT SET P_QOH = P_QOH;

SELECT * FROM PRODUCT WHERE P_CODE = '11QER/31';

: TRG_LINE_PROD TO UPDATE P_QOH
@TRG_LINE_PROD

: TRG_LINE_CUS TO UPDATE CUS_BALANCE
@TRG_LINE_CUS


