بسم الله الرحمن الرحيم
السلام عليكم ورحمة الله وبركاته
أخواني الكرام عندي مشكلة ممكن تساعدوني فيها أنا عامل شاشة تعرض معلومات view
وهذه الview هي complex أي أنها تربط بين جدولين المشكلة في حالتها أنا تربط بين جدولين لا أستطيع التعديل على البيانات من view ولكن في حال جعل الview تكون simple فالتعديل ممكن في هذه الحالة علما أنني لم أضع في الview أمر with read only وأنا مطر على الربط بين جدولين في الview فما هو الحل ياشباب

السلام عليكم
الحل في استخدام التريجرز من نوع instead of
و هذا شرح لها ارجو ان يفيدك

INSTEAD OF triggers control insert, update, and delete operations on views, not tables. They can be used to make non-updateable views updateable and to override the default behavior of views that are updateable.

To create (or replace) an INSTEAD OF trigger, use the syntax shown here:

1 CREATE [OR REPLACE TRIGGER] trigger_name
2 INTEAD OF operation
3 ON view name
4 FOR EACH ROW
5 BEGIN
6 ... code goes here ...
7 END;

The table contains an explanation of this code:

Line(s)	Description

1	States that a trigger is to be created with the unique name supplied. Specifying OR REPLACE is optional. If the trigger exists and REPLACE is not specified, then your attempt to create the trigger a new will result in an ORA-4081 error.

2	This is where you see differences between INSTEAD OF triggers and other types of triggers. Because INSTEAD OF triggers aren’t really triggered by an event, youe don’t need to specify AFTER or BEFORE or provide an event name. What is specified is the operation that the trigger is to fire in place of (or instead of). Stating INSTEAD OF followed by one of INSERT, UPDATE, or DELETE accomplishes this.

3	This line is somewhat like the corresponding line for DDL and database event triggers in that the keyword ON is specified. The similarities end there: instead of specifying DATABASE or SCHEMA, you provide the name of the view to which the trigger is to apply.

4–7	Contains standard PL/SQL code.

INSTEAD OF triggers are best explained with an example. Let’s use pizza delivery! Before you can start pounding the dough, you have to put a system in place to monitor the deliveries. You will need three tables: one to track actual deliveries, one to track delivery areas, and one to track the massive fleet of drivers (remember the first rule of business— always think big!).

CREATE TABLE delivery
(delivery_id NUMBER,
delivery_start DATE,
delivery_end DATE,
area_id NUMBER,
driver_id NUMBER);

CREATE TABLE area
(area_id NUMBER, area_desc VARCHAR2(30));

CREATE TABLE driver
(driver_id NUMBER, driver_name VARCHAR2(30));

For the sake of brevity no primary or foreign keys will be created here. 

You will also need three sequences to provide unique identifiers for the tables.

CREATE SEQUENCE delivery_id_seq;
CREATE SEQUENCE area_id_seq;
CREATE SEQUENCE driver_id_seq;

To avoid having to explain relational database design and normalization to the employees, simplify deliveries into a single view displaying delivery, area, and driver information.

CREATE OR REPLACE VIEW delivery_info AS
SELECT d.delivery_id,
d.delivery_start,
d.delivery_end,
a.area_desc,
dr.driver_name
 FROM delivery d,
area a,
driver dr
WHERE a.area_id = d.area_id
  AND dr.driver_id = d.driver_id;

Because the system relies heavily on this view for query functionality, why not make it available for insert, update, and delete as well? This is where INSTEAD OF triggers come into play. You need to tell the database what to do when an insert, update or delete operation occurs against the delivery_info view; in other words, it needs to be told what to do instead of trying to insert, update, or delete. Let’s start with the INSERT trigger.

The INSTEAD OF INSERT Trigger

The INSERT trigger will perform four basic operations:

1.	Ensure that the delivery_end value is NULL. All delivery completions must be done via an update.

2.	Try to find the driver ID based on the name provided. If the name cannot be found, then assign a new ID and create a driver entry using the name and the new ID.

3.	Try to find the area ID based on the name provided. If the name cannot be found, then assign a new ID and create an area entry using the name and the new ID.

4.	Create an entry in the delivery table.

Bear in mind that this example is intended to demonstrate triggers—not how to effectively build a business system! After a while you will probably wind up with a multitude of duplicate driver and area entries. However, using this view speeds things up by not requiring drivers and areas to be predefined, and in the fast-paced world of pizza delivery, time is money!

CREATE OR REPLACE TRIGGER delivery_info_insert
INSTEAD OF INSERT ON delivery_info
DECLARE

-- cursor to get the driver ID by name
CURSOR curs_get_driver_id ( cp_driver_name VARCHAR2 ) IS
SELECT driver_id
 FROM driver
WHERE driver_name = cp_driver_name;
v_driver_id NUMBER;

-- cursor to get the area ID by name
CURSOR curs_get_area_id ( cp_area_desc VARCHAR2 ) IS
SELECT area_id
 FROM area
WHERE area_desc = cp_area_desc;
v_area_id NUMBER;

BEGIN

/*
|| Make sure the delivery_end value is NULL
*/
IF :NEW.delivery_end IS NOT NULL
THEN
RAISE_APPLICATION_ERROR (
 -20000,'Delivery end date value must be NULL when delivery created');
END IF;

/*
|| Try to get the driver ID using the name. If not found
|| then create a brand new driver ID from the sequence
*/
OPEN curs_get_driver_id(UPPER(:NEW.driver_name));
FETCH curs_get_driver_id INTO v_driver_id;
IF curs_get_driver_id%NOTFOUND THEN
  SELECT driver_id_seq.nextval
    INTO v_driver_id
    FROM DUAL;
  INSERT INTO driver(driver_id,driver_name)
  VALUES(v_driver_id,UPPER(:NEW.driver_name));
END IF;
CLOSE curs_get_driver_id;

/*
|| Try to get the area ID using the name. If not found
|| then create a brand new area ID from the sequence
*/
OPEN curs_get_area_id(UPPER(:NEW.area_desc));
FETCH curs_get_area_id INTO v_area_id;
IF curs_get_area_id%NOTFOUND THEN
SELECT area_id_seq.nextval
    INTO v_area_id
    FROM DUAL;
  INSERT INTO area(area_id,area_desc)
  VALUES(v_area_id,UPPER(:NEW.area_desc));
END IF;
CLOSE curs_get_area_id;

/*
|| Create the delivery entry
*/
INSERT INTO delivery(delivery_id,
delivery_start,
delivery_end,
area_id,
driver_id)
VALUES(delivery_id_seq.nextval,
NVL(:NEW.delivery_start,SYSDATE),
NULL,
v_area_id,
v_driver_id);

END;

The INSTEAD OF UPDATE Trigger

Now let’s move on to the UPDATE trigger. For the sake of simplicity, only allow updating of the delivery_end field, and only if it were NULL to start with. You can’t have drivers resetting delivery times.

CREATE OR REPLACE TRIGGER delivery_info_update
INSTEAD OF UPDATE
ON delivery_info
DECLARE
-- cursor to get the delivery entry
CURSOR curs_get_delivery (cp_delivery_id NUMBER)
IS
SELECT delivery_end
   FROM delivery
  WHERE delivery_id = cp_delivery_id
 FOR UPDATE OF delivery_end;

v_delivery_end DATE;
BEGIN
OPEN curs_get_delivery (:NEW.delivery_id);
FETCH curs_get_delivery INTO v_delivery_end;

IF v_delivery_end IS NOT NULL
THEN
RAISE_APPLICATION_ERROR (
 -20000, 'The delivery end date has already been set');
ELSE
UPDATE delivery
    SET delivery_end = :NEW.delivery_end
  WHERE CURRENT OF curs_get_delivery;
END IF;

CLOSE curs_get_delivery;
END;

The INSTEAD OF DELETE Trigger

The DELETE trigger merely ensures that you are not deleting a completed entry, and then removes the delivery record. The driver and area records remain intact.

CREATE OR REPLACE TRIGGER delivery_info_delete
INSTEAD OF DELETE
ON delivery_info
BEGIN
IF :NEW.delivery_end IS NOT NULL
THEN
 RAISE_APPLICATION_ERROR (
 	-20000,'Completed deliveries cannot be deleted');
END IF;

DELETE delivery
 WHERE delivery_id = :NEW.delivery_id;
END;

Populating the Tables

Now, with a single INSERT focused on the delivery information you know (the driver and the area), all of the required tables are populated:

SQL> INSERT INTO delivery_info(delivery_id,
2	delivery_start,
3	delivery_end,
4	area_desc,
5	driver_name)
6  VALUES
7    (NULL, NULL, NULL, 'LOCAL COLLEGE', 'BIG TED');

1 row created.

SQL> SELECT * FROM delivery;

DELIVERY_ID	DELIVERY_	DELIVERY_      AREA_ID	DRIVER_ID
-----------	---------	---------	----------	----------
         1	13-JAN-02            1      1

SQL> SELECT * FROM area;

   AREA_ID	AREA_DESC
----------	------------------------------
        1	LOCAL COLLEGE

SQL> SELECT * FROM driver;

DRIVER_ID	DRIVER_NAME
----------	------------------------------
        1	BIG TED

بالتوفيق

بسم الله الرحمن الرحيم
السلام عليكم ورحمة الله وبركاته
أخي الكريم أنا بصراحة أمس سألت أحد أصدقائي عن حل للمشكلة فذكر لي instead of
وعندما دخل على الموقع ورأيت رد على الموضوع كانت فرحتي عندما رأيت ردك أخي الكريم لأن حلك دعم حل صديقي وبصراحة مثال رائع وسهلت علي البحث في الكتب وشكرا لك أخي الفاضل