Re: Another Trigger Query...

<arijitchatterjee123_at_yahoo.co.in> a écrit dans le message de news:1103185966.403911.83910_at_f14g2000cwb.googlegroups.com...
| 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
|

You modified some things from my post that turn the logic wrong.

In TRG_TAB1 you set the boolean value to false, this means if you execute a multi insert/update statement, then the boolean is false after the first row and the subsequent row will raise an error in MYCOPYTRGAFT3. So you can't never do this kind of DML statement. PKGTAB1.BOOLVAL must be clear only in the final trigger: MYTAB1TRGAFT

The logic and the error message in MYCOPYTRGAFT3 are not compatible. You remove the test on module i made then now each time you modify directly the copy table there is an error not only when this modification is made from SQL*Plus (but maybe this is what you want).

Regards
Michel Cadot Received on Thu Dec 16 2004 - 05:15:41 CST