السلام عليكم ..

سؤالي هو ..

هل من الممكن إنشاء DATABASE TRIGGER يقوم بالتعديل على جدولين في نفس الوقت؟

توضيح: معي جدولين الأول أساسي والأخر مرتبطه به.. أريد إنشاء DATABASE TRIGGER  في حالة إذا تم التعديل على الجدول الرئيسي نفس الوقت يتعدل البيانات في الجدول الثانوي.. 

 

اكيد طبعا بتعمل trigger على الجدول الرئيسي وتحدد ال action يتم فين

 

DROP TRIGGER ISMAIL.T_QTY_STORE;

 

CREATE OR REPLACE TRIGGER ISMAIL."T_QTY_STORE" BEFORE INSERT OR DELETE OR UPDATE OF "QUANTITIES" ON "ISMAIL"."SAMPLING00" REFERENCING OLD AS "OLD" NEW AS "NEW" FOR EACH ROW

BEGIN

 

IF  INSERTING  THEN

    IF :NEW.QUANTITIES > 0    THEN 

       UPDATE PRODUCTS_T

       SET CURENT_COUNTITY = NVL(PRODUCTS_T.CURENT_COUNTITY,0) + :NEW.QUANTITIES 

       WHERE PRODUCTS_T.PRODUCT_ID      = :NEW.PRODUCT_ID;

    END IF;

 END IF;

 

IF  UPDATING THEN

      IF :NEW.QUANTITIES >:OLD.QUANTITIES   THEN

      UPDATE  PRODUCTS_T

      SET   CURENT_COUNTITY = NVL(PRODUCTS_T.CURENT_COUNTITY,0) + ( :NEW.QUANTITIES - :OLD.QUANTITIES )

      WHERE  PRODUCTS_T.PRODUCT_ID  = :OLD.PRODUCT_ID;

    END IF;

 

      IF :NEW.QUANTITIES <:OLD.QUANTITIES   THEN

      UPDATE  PRODUCTS_T

      SET   CURENT_COUNTITY = NVL(PRODUCTS_T.CURENT_COUNTITY,0) - ( :NEW.QUANTITIES - :OLD.QUANTITIES )

      WHERE  PRODUCTS_T.PRODUCT_ID  = :OLD.PRODUCT_ID;

    END IF;

END IF;

 

IF DELETING THEN

        IF :OLD.QUANTITIES > 0 THEN

      UPDATE  PRODUCTS_T

      SET   CURENT_COUNTITY = NVL(PRODUCTS_T.CURENT_COUNTITY,0) -  NVL(:OLD.QUANTITIES,0 )

      WHERE  PRODUCTS_T.PRODUCT_ID  = :OLD.PRODUCT_ID;

    END IF;

    END IF;

 

END ;

/

DROP TRIGGER ISMAIL.REF_AFTER_DEL;

 

CREATE OR REPLACE TRIGGER ISMAIL."REF_AFTER_DEL" AFTER DELETE ON "ISMAIL"."REFERENCE" REFERENCING OLD AS "OLD" NEW AS "NEW" FOR EACH ROW

DECLARE

  CURR_SESSION   VARCHAR2(100);

  MTRANS_DATE     DATE;

  MTRANS_HOUR      VARCHAR2(8);

  MTRANS_USER     VARCHAR2(8);

  TIME            VARCHAR2(20);

BEGIN

  TIME :=TO_CHAR(SYSDATE,'HH:MM:DD');

  MTRANS_DATE := SYSDATE;

  MTRANS_HOUR  := TO_CHAR(SYSDATE,'HH24:MM:SS');

 -- MTRANS_USER := :NEW.TRANS_USR;

SELECT S.MACHINE||'\'||S.OSUSER INTO CURR_SESSION

FROM   gv$session s

WHERE S.AUDSID IN( SELECT USERENV('SESSIONID') AS AUDSID FROM DUAL);

insert into ISMAIL.REF values(

:OLD.RESERVATION_ID,

:OLD.REFERENCE_ID,

:OLD.TEST_TYPE,

:OLD.TESTES,

:OLD.CONDITION,

:OLD.UNIT,

:OLD.RESULT,

:OLD.AGREE,

:OLD.AMOUNT,

:OLD.SPECIFICATIONS,

:OLD.SATUE,

:OLD.TEST_ID,

:OLD.BRANCH_ID,

:OLD.PERIOD,

:OLD.TESTER,

:OLD.D_START,

:OLD.D_FINISH,

:OLD.DEP_ID,

:OLD."REFERENCE",

:OLD.REG_NO,

:OLD.NR,

:OLD.CANCELL,

:OLD.NORMAL_RANGE,

:OLD.D_ID,

:OLD.V_L,

:OLD.RESULTB,

:OLD.RESULTC,

:OLD.RESULTD,

:OLD.RESULTE,

:OLD.TRANSA,

:OLD.TRANSB,

:OLD.TRANSC,

CURR_SESSION,

MTRANS_DATE);

end;

/

 

DROP TRIGGER ISMAIL.UPD_FINISH_D_TRG;

CREATE OR REPLACE TRIGGER ISMAIL."UPD_FINISH_D_TRG" BEFORE UPDATE OF "RESULT" ON "ISMAIL"."REFERENCE" REFERENCING OLD AS "OLD" NEW AS "NEW" FOR EACH ROW
DECLARE
   CURR_SESSION   VARCHAR2 (100);
   MTRANS_DATE    DATE;
   MTRANS_HOUR    VARCHAR2 (8);
   MTRANS_USER    VARCHAR2 (8);
   TIME           VARCHAR2 (20);
BEGIN
   TIME := TO_CHAR (SYSDATE, 'HH:MM:DD');
   MTRANS_DATE := SYSDATE;
   MTRANS_HOUR := TO_CHAR (SYSDATE, 'HH24:MM:SS');

   -- MTRANS_USER := :NEW.TRANS_USR;
   SELECT   S.MACHINE || '\' || S.OSUSER
     INTO   CURR_SESSION
     FROM   gv$session s
    WHERE   S.AUDSID IN (SELECT   USERENV ('SESSIONID') AS AUDSID FROM DUAL);

   IF UPDATING
   THEN
      IF :NEW.RESULT <> :OLD.RESULT
      THEN
         :NEW.D_FINISH := SYSDATE;

         INSERT INTO ISMAIL.REF
           VALUES   (:OLD.RESERVATION_ID,
                     :OLD.REFERENCE_ID,
                     :OLD.TEST_TYPE,
                     :OLD.TESTES,
                     :OLD.CONDITION,
                     :OLD.UNIT,
                     :OLD.RESULT,
                     :OLD.AGREE,
                     :OLD.AMOUNT,
                     :OLD.SPECIFICATIONS,
                     :OLD.SATUE,
                     :OLD.TEST_ID,
                     :OLD.BRANCH_ID,
                     :OLD.PERIOD,
                     :OLD.TESTER,
                     :OLD.D_START,
                     :OLD.D_FINISH,
                     :OLD.DEP_ID,
                     :OLD."REFERENCE",
                     :OLD.REG_NO,
                     :OLD.NR,
                     :OLD.CANCELL,
                     :OLD.NORMAL_RANGE,
                     :OLD.D_ID,
                     :OLD.V_L,
                     :OLD.RESULTB,
                     :OLD.RESULTC,
                     :OLD.RESULTD,
                     :OLD.RESULTE,
                     :OLD.TRANSA,
                     :OLD.TRANSB,
                     :OLD.TRANSC,
                     CURR_SESSION,
                      SYSDATE);

--         COMMIT;
      END IF;
   END IF;
END;
/