حل أسئلة مقرر تقانات المعلومات (2) أو أوراكل 2 دورة 2007-2008

[BlackHat]

السؤال الاول

===========


create user user_test identified by user_test;

grant create any trigger to user_test;

grant "connect" to user_test;

grant create any object to user_test;

grant insert on prdcts to user_test;

grant update(prodname,quantity) on prdcts to user_test;

==============================================

السؤال الثاني



CREATE OR REPLACE TRIGGER QUNT_TRIGGER
BEFORE INSERT OR UPDATE
OF QNTITY ON INVDTLS
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
V_STATE VARCHAR2(10);
V_SAFELVL NUMBER ;
V_QUT NUMBER;
BEGIN IF INSERTING
THEN
SELECT I.STATE
INTO V_STATE FROM INVOICES I
WHERE I.INVID = :NEW.INVID ;

IF V_STATE ='IN'
THEN
UPDATE PRDCTS
SET QUANTITY =PRDCTS.QUANTITY+:NEW.QNTITY
WHERE PRDID = :NEW.PRDID;

ELSIF V_STATE ='OUT'
THEN
SELECT P.SAFELVL ,P.QUANTITY
INTO V_SAFELVL ,V_QUT
FROM PRDCTS P
WHERE P.PRDID=:NEW.PRDID;

IF V_SAFELVL<=(V_QUT-:NEW.QNTITY)
THEN

BEGIN UPDATE PRDCTS
SET PRDCTS.QUANTITY =(PRDCTS.QUANTITY-:NEW.QNTITY)
WHERE PRDCTS.PRDID = :NEW.PRDID;
END;

ELSE
RAISE_APPLICATION_ERROR(-20987,'THERE CAN BE INSERT RECORD ,THE SAVE LEVEL OF THIS PRODUCT = '||V_SAFELVL );

END IF ;
END IF ;

ELSIF UPDATING
THEN
UPDATE PRDCTS
SET PRDCTS.QUANTITY =PRDCTS.QUANTITY+(:OLD.QNTITY-:NEW.QNTITY)
WHERE PRDCTS.PRDID = :NEW.PRDID;
END IF ;



END QUNT_TRIGGER;


بالنسبة لباقي الأسئلة بها اليومين ان شاء الله أنا وضعت الاسئلة
بمنتدى المجموعة العربية للأوراكل وفي مهندس قدير اسمو عبد الله عم يحل الأسئلة