كيف أقرأ من جدول في row trigger

[aroma_kt]

السلام عليكم ورحمة الله وبركاته:
لو سمحتم أحتاج إلى خبرة الأعضاء المحترفين

أعمل على إنشاء قاعدة بيانات لتخزين إجازات الموظفين
لدي جدول الإجازات تعريفه كالتالي:

CREATE TABLE leave_taken (
emp_id NUMBER(4) NOT NULL
CONSTRAINT leavetaken_empid_fk REFERENCES emp_info (emp_id),
leavetaken_type NOT NULL
CONSTRAINT leavetaken_type_fk REFERENCES leave_types (leavetype_id),
leavetaken_date DATE NOT NULL,
leavetaken_duration NUMBER(4) NOT NULL,
leavetaken_durationsort NUMBER(1) NOT NULL
CONSTRAINT leavetaken_durationsort_fk REFERENCES duration_sorts (durationsort_id),
leavetaken_note VARCHAR2(50),
user_reccreator VARCHAR2(20)
);

ما أحاول عمله هو منع المستخدم من أن يقوم بإدخال إجازة تتداخل مع إجازة موجودة لنفس الموظف
مثلاً:
الموظف ذو الرقم 1 أخذ إجازة بتاريخ الأول من أيلول 2006 مدتها 3 أيام هذا يعني (1و 2 و3 أيلول إجازة)
إذا حاولنا إدخال إجازة لنفس الموظف بتاريخ 2 أيلول 2006 مدتها 1 يوم

يجب عدم السماح بإدخال هذه الإجازة لأنها تتداخل (تتقاطع) مع الإجازة الأولى

لقد أنشأت function أمرر له تاريخي (بداية ونهاية) الإجازة الحالية وتاريخي (بداية ونهاية) الإجازة الجديدة
فيعطيني إن كان هناك تداخل بينهما أم لا كالتالي:

CREATE OR REPLACE FUNCTION LEAVES_CONFLICT (
ODATE1 IN DATE,
ODATE2 IN DATE,
NDATE1 IN DATE,
NDATE2 IN DATE
) RETURN NUMBER 
IS
BEGIN
IF NDATE1 >= ODATE1 AND NDATE2 <= ODATE2 THEN RETURN (1);
ELSIF NDATE1 >= ODATE1 AND NDATE2 >= ODATE2 AND ODATE2 >= NDATE1 AND ODATE2 <= NDATE2 THEN RETURN (1);
ELSIF NDATE1 <= ODATE1 AND NDATE2 <= ODATE2 AND ODATE1 >= NDATE1 AND ODATE1 <= NDATE2 THEN RETURN (1);
ELSIF NDATE1 <= ODATE1 AND NDATE2 >= ODATE2 THEN RETURN (1);
ELSE RETURN (0);
END IF;
END LEAVES_CONFLICT;
/

كما أنشأت function أعطيه تاريخ بدء الإجازة ومدتها: (1 أو 2 أو 365 ..إلخ) ونوع المدة: (دقيقة - ساعة - يوم - شهر - عام)
فيعطيني تاريخ نهايتها كالتالي:

CREATE OR REPLACE FUNCTION CalcEndOfLeave (  
START_DATE IN DATE,  
DURATION IN LEAVE_TAKEN.LEAVETAKEN_DURATION%TYPE,  
DURATION_SORT IN LEAVE_TAKEN.LEAVETAKEN_DURATIONSORT%TYPE)  
RETURN DATE  
IS  
END_DATE DATE;  
BEGIN  
END_DATE :=  
CASE DURATION_SORT  
WHEN 1 THEN (START_DATE + DURATION/60/24)--Minute  
WHEN 2 THEN (START_DATE + DURATION/24)--Hour  
WHEN 3 THEN (START_DATE + DURATION - 1)--Day  
WHEN 4 THEN (START_DATE + DURATION*30 - 1)--Month  
WHEN 5 THEN (START_DATE + DURATION*365 - 1)--Year  
END;  
RETURN (END_DATE);  
END CalcEndOfLeave;  
/

وأنشأت function يعمل query على جدول الإجازات ليرى إن كانت الإجازة (التي أمررها له كباراميتر) تتقاطع مع إجازات سابقة (لنفس الموظف بالطبع)
فإن كان هناك تقاطع يعيد إلي شريط محرفي يحوي (تاريخي بداية ونهاية الإجازة التي تتقاطع بينهما إشارة أكبر)
لأنني لا أكتفي بإظهار رسالة للمستخدم بأن الإجازة التي يحاول إدخالها تتقاطع بل وأقوم بإظهار هذا الشريط المحرفي للمستخدم

CREATE OR REPLACE FUNCTION CONFLICT_ROW (
P_EMPID IN EMP_INFO.EMP_ID%TYPE,
NEW_LEAVE_START IN DATE,
NEW_LEAVE_END IN DATE,
EXCEPT_LEAVE_START IN DATE)
RETURN VARCHAR2
IS
INVALID_LEAVE VARCHAR2(25);
BEGIN
IF EXCEPT_LEAVE_START IS NULL THEN
SELECT LEAVETAKEN_DATE||' > '||CalcEndOfLeave(LEAVETAKEN_DATE,LEAVETAKEN_DURATION,LEAVETAKEN_DURATIONSORT)
INTO INVALID_LEAVE 
FROM LEAVE_TAKEN 
WHERE EMP_ID = P_EMPID AND LEAVETAKEN_TYPE <> 1
AND LEAVES_CONFLICT(LEAVETAKEN_DATE, CalcEndOfLeave(LEAVETAKEN_DATE,LEAVETAKEN_DURATION,LEAVETAKEN_DURATIONSORT), NEW_LEAVE_START, NEW_LEAVE_END)=1;
ELSE
SELECT LEAVETAKEN_DATE||'>'||CalcEndOfLeave(LEAVETAKEN_DATE,LEAVETAKEN_DURATION,LEAVETAKEN_DURATIONSORT)
INTO INVALID_LEAVE 
FROM LEAVE_TAKEN 
WHERE EMP_ID = P_EMPID AND LEAVETAKEN_TYPE <> 1 AND LEAVETAKEN_DATE <> EXCEPT_LEAVE_START
AND LEAVES_CONFLICT(LEAVETAKEN_DATE, CalcEndOfLeave(LEAVETAKEN_DATE,LEAVETAKEN_DURATION,LEAVETAKEN_DURATIONSORT), NEW_LEAVE_START, NEW_LEAVE_END)=1;
END IF;
RETURN (INVALID_LEAVE);
EXCEPTION
WHEN NO_DATA_FOUND THEN RETURN (NULL);
END CONFLICT_ROW;
/

ثم أنشأت ال trigger الذي يمنع المستخدم من إدخال إجازة تتقاطع مع إجازات سابقة (لنفس الموظف):

CREATE OR REPLACE TRIGGER CHECK_CONFLICT
BEFORE INSERT OR UPDATE ON LEAVE_TAKEN
FOR EACH ROW
WHEN (NEW.LEAVETAKEN_TYPE <> 1) --hour leave
DECLARE
V_INVALID VARCHAR2(25);
BEGIN
IF INSERTING THEN
V_INVALID := CONFLICT_ROW(:NEW.EMP_ID, :NEW.LEAVETAKEN_DATE, CalcEndOfLeave(:NEW.LEAVETAKEN_DATE, :NEW.LEAVETAKEN_DURATION, :NEW.LEAVETAKEN_DURATIONSORT), NULL);
ELSIF UPDATING THEN
V_INVALID := CONFLICT_ROW(:NEW.EMP_ID, :NEW.LEAVETAKEN_DATE, CalcEndOfLeave(:NEW.LEAVETAKEN_DATE, :NEW.LEAVETAKEN_DURATION, :NEW.LEAVETAKEN_DURATIONSORT), :OLD.LEAVETAKEN_DATE);
END IF;
IF V_INVALID IS NOT NULL THEN 
RAISE_APPLICATION_ERROR(-20011, 'This leave can''t be added because it will conflict with this one '||V_INVALID);
END IF;
END;
/

ليس هناك مشكلة عند عملية الinsert لإجازة وإنما عند عملية الupdate فإنه يعطيني رسالة خطأ

ERROR at line 1:
ORA-04091: table LMGR.LEAVE_TAKEN is mutating, trigger/function may not see it
ORA-06512: at "LMGR.CONFLICT_ROW", line 17
ORA-06512: at "LMGR.CHECK_CONFLICT", line 7
ORA-04088: error during execution of trigger 'LMGR.CHECK_CONFLICT'

أعلم سبب المشكلة وهي أنه لا يمكن ل Row Trigger الاستعلام من الجدول في حالة update
لكن كيف يمكنني الالتفاف حول هذه المعضلة ؟؟

[jamal_rrkk]

السلام عليكم ورحمة الله وبركاته

أخي الكريم

راجع الرابط التالي


http://www.araboug.org/ib/index.php?showtopic=16020

[aroma_kt]

السلام عليكم ورحمة الله وبركاته:
أخي الكريم jamal_rrkk

بداية أشكرك على تفاعلك مع موضوعي

أود أن أنبهك أن مثالي ومثالك يعملان بشكل ناجح في حالة insert

لكن عندما قمت بتعديل الtrigger الخاص بك (في المشاركة التي أرفقتها)
(لا تنسى بأن تعديل (update) تاريخ بدء ونهاية الحجز لغرفة ما في الفندق يحتاج أيضاً للتدقيق وليس insert فحسب)

يصبح شكل ال trigger كالآتي:

before insert or update on aaa for each row

في حالة ال update مثالك أيضاً لا يعمل فهو يسبّب نفس الرسالة التي تظهر لي وهي كالتالي:

SQL> update aaa set room=3 where room=2;
update aaa set room=3 where room=2
      *
ERROR at line 1:
ORA-04091: table HR.AAA is mutating, trigger/function may not see it
ORA-06512: at "HR.T_AAA", line 6
ORA-04088: error during execution of trigger 'HR.T_AAA'

لهذا أرجو منك ومن كافة الأعضاء التفكير معي لإيجاد حل لهذه المعضلة !

تم تعديل 19 نوفمبر 2006 بواسطة aroma_kt

[jamal_rrkk]

السلام عليكم ورحمة الله وبركاته

مرفق ملف قد يحتوي علي طريقة لتجنب هذا الخطأ

mutating.zip

mutating2.doc

تم تعديل 20 نوفمبر 2006 بواسطة jamal_rrkk

[aroma_kt]

أشكرك من أعماقي أخي jamal_rrkk ،جزاك الله عني كل خير

الملف المرفق يحوي 32 صفحة سوف أقرأه إن شاء الله وأعلمكم إن توصلت إلى حل

لكن ريثما أقرأه أرجو ممن لديه أيّ طريقة (بديلة عن الtrigger) مثلاً check constraint
لكي أحصل على مرادي وهو منع إدخال إجازة تتقاطع مع إجازة موجودة (في حالة update طبعاً)
أن يشاركنا بها وله الثواب والأجر

وشكراً للجميع