sql 7

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