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
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;
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.
source to share
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
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
then update it (but again, why?)
I have added a SqlFiddle here demonstrating the above approaches
source to share