Clever Geek Handbook

Oracle call to enable DML view

I have a view in Oracle DB:

  CREATE VIEW view1
AS SELECT
  id, c1, c2, c3, c4, c5, c6
FROM
  table1
WHERE
  c1>1100 AND c1<2000
WITH CHECK OPTION;

And a table table1

with columns id, c1, ... c9. Now I want to make a trigger on this view to allow DML operations on data that is not in this view. But I don't know if whitch columns will be updated, whitch rows deleted or inserted. for example

UPDATE view1 SET c1=3000 WHERE c1=1500;

or

INSERT INTO view1 VALUES(3500, .......);

Anyone have an idea?

Edit: I know this doesn't make sense, but this is part of my project and this part will show you how to get around the view limitation.

+3


source to share


2 answers


So here's the solution for delete:

CREATE OR REPLACE TRIGGER DML_VIEW_DELETE
    INSTEAD OF DELETE ON myview
BEGIN
    DELETE table
    WHERE id = :old.id;
END;


And similarly for updating and inserting only with :new

instead of :old

and null

in columns that are not in the view

0


source


It seems that you are trying to insert / update through the view and at the same time work around the limitation that the view imposes - it doesn't seem to make much sense. Anyway, I don't believe that you will need to use another workaround, like a trigger.

In general, since your view only uses one table, you can update it through the view without triggers, etc., provided that you stay within the range CHECK OPTION

for c1 (i.e. between 1100 and 2000)

:

INSERT INTO view1(c1, ...) 
VALUES (1101, );

UPDATE view1 
SET c1 = 1102 
WHERE id = 1;

But not this:



UPDATE view1 
SET c1 = 23 
WHERE id = 1;

-- ORA-01402: view WITH CHECK OPTION where-clause violation

However, if you want to work around the limitation, you can:

  • Insert / Update directly into the table (i.e. update table1 set c1 = 23 where ...

    )
  • Create another view without filter WHERE

    and CHECK CONSTRAINT

    then update it (but again, why?)

I have added a SqlFiddle here demonstrating the above approaches

0


source






More articles:


All Articles