• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 852
  • Last Modified:

Oracle: Insert, Update and Delete on complex views

I am looking for an detailed oversight on when can I and when can I not  update, insert or delete from complex views in Oracle? I seem to find some discussions on the topic via Google, but those do not seem to be final and covering the whole topic.
0
itnifl
Asked:
itnifl
  • 2
  • 2
2 Solutions
 
sdstuberCommented:
It basically boils down to 3 rules

1 - do you have the requisite privilege on the view?
               e.g.  grant insert on your_view to _some_user

2 - does your action validate against the view's conditions when the "with check" option is present?
      e.g.  The view has a condition like   "AND some_date < sysdate"
               and you try to insert a value for tomorrow, your insert will fail

3 -  does the view maintain unique row identification?  This is the tricky one.
      This means when you update a row in the view, can that row be mapped back to a single row in a table?  if no, then you can't modify the view.  Note "instead of" triggers can get around this by intercepting the sql statement and, as the name suggests, do some other action "instead of" the insert,update or delete that was issued.
0
 
itniflAuthor Commented:
I guess the view also needs to cover all the not null columns in the table, and also the primary key.
0
 
sdstuberCommented:
if there are triggers or a default values for the not null columns then the view doesn't need to reference them.

the primary key isn't necessary if there is a trigger to populate it on insert
ee.txt
0
 
itniflAuthor Commented:
Yes, but then the triggers are filling the requirement
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now