THANKS FACULTIES,
THANKS FOR YOUR GREAT SUPPORT, AT LAST I MEET MY REQUIREMENT THROUGH
YOUR
GREAT HELPS .I AM WRITING DOWN THE TOTAL THING.
CREATE TABLE STATEMENTS
CREATE TABLE TAB1
(
ID NUMBER,
NAME VARCHAR2(10),
AMOUNT NUMBER
);
CREATE TABLE TAB1COPY
(
ID NUMBER,
NAME VARCHAR2(10),
AMOUNT NUMBER,
SUMAMOUNT NUMBER
);
CREATING PACKAGE
CREATE OR REPLACE PACKAGE PKGTAB1
IS
BOOLVAL BOOLEAN := FALSE;
END;
/
BEFORE TRIGGER ON TAB1 CHANGING BOOLEAN VALUE
CREATE OR REPLACE TRIGGER MYTAB1TRGBEF1
BEFORE INSERT OR UPDATE OR DELETE ON TAB1
BEGIN
PKGTAB1.BOOLVAL := TRUE;
END;
/
AFTER TRIGGER ON TAB1 INSERTING IN TAB1COPY
CREATE OR REPLACE TRIGGER TRG_TAB1
AFTER INSERT OR UPDATE OR DELETE
ON TAB1 FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO TAB1COPY(ID,NAME,AMOUNT,SUMAMOUNT) VALUES (:NEW.ID
,:NEW.NAME ,:NEW.AMOUNT ,0);
UPDATE TAB1COPY SET SUMAMOUNT=0 WHERE NAME =:NEW.NAME ;
PROC_TAB1(:NEW.NAME);
PKGTAB1.BOOLVAL := FALSE;
END IF;
END;
PROCEDURE DOING NECESSARY CALCULATION IN TAB1COPY
CREATE OR REPLACE PROCEDURE PROC_TAB1(INAME VARCHAR2)
IS
BEGIN
DECLARE
SUMAMT NUMBER := 0;
CURSOR CUR IS SELECT * FROM TAB1COPY WHERE NAME = INAME ORDER BY ID;
BEGIN
FOR REC IN CUR LOOP
SUMAMT := SUMAMT+REC.AMOUNT;
UPDATE TAB1COPY SET SUMAMOUNT=SUMAMT WHERE NAME = INAME AND
ID=REC.ID;
END LOOP;
SUMAMT := 0;
END;
END;
AFTER TRIGGER ON TAB1 CHANGING BOOLEAN VALUE
CREATE OR REPLACE TRIGGER MYTAB1TRGAFT
AFTER INSERT OR UPDATE OR DELETE ON TAB1
BEGIN
PKGTAB1.BOOLVAL := FALSE;
END;
/
BEFORE TRIGGER ON TAB1COPY CHECKING THE PACKAGE VARIABLES VALUE
CREATE OR REPLACE TRIGGER MYCOPYTRGAFT3
BEFORE INSERT OR UPDATE OR DELETE ON TAB1COPY
BEGIN
IF (PKGTAB1.BOOLVAL=FALSE) THEN
RAISE_APPLICATION_ERROR(-20001,'ERROR FROM SQLPLUS');
END IF;
END;
/
REGARDS
ARIJIT CHATTERJEE
Received on Thu Dec 16 2004 - 02:32:46 CST
